Add holidays to DateDiff query

[Autoeng]

I have a query that generates a report for records found between start and stop dates for workdays only (4 day week). I would like to add holidays to the query. As I work in an industrial manufacturing setting our holidays (at least not all of them) cannot be calculated via code as we are shutdown for 2 weeks in the summer and several non-holidays during Christmas. I thought that I would build a table with holidays to reference to.

Here is the query in SQL that I am using to calculate weekdays including holidays. (I apologize in advance for my poor naming conventions as this was one of my first databases.) If anyone can suggest a good method to remove the holidays I would be grateful.

Autoeng

SELECT ECNBCNVIPtbl.[ECNBCNVIP ID], ECNBCNVIPtbl.[Release Date], ECNDetailtbl.[Actual Implementation Date], DateDiff("d",[ECNBCNVIPtbl].[Release Date],[ECNDetailtbl].[Actual Implementation Date],2) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([ECNDetailtbl].[Actual Implementation Date],2)>5,5-Weekday([ECNDetailtbl].[Actual Implementation Date],2),0)+IIf(Weekday([ECNBCNVIPtbl].[Release Date],2)=6,1,0)+1 AS weekdays
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNDetailtbl.[Actual Implementation Date]) Between [Forms]![frmDates]![Start Date] And [Forms]![frmDates]![Stop Date]) AND ((ECNBCNVIPtbl.[Do Not Process])<>&quot;Do Not Process&quot);

 

[MichaelRed]

faq181-261

Although you will need to modify it a bit for you four day week thing. It is quite straight forward.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Autoeng]

Michael:

I had printed out your Faq prior to posting but I am a novice at queries and don't have a clue as to where to start to combine this into my existing query or to how to make use of the return that it would generate to use in my query. If you could be so kind as to give me a starting point I would be most grateful.

Autoeng

 

[MichaelRed]

something like unto:

strSQL = &quot;SELECT tblEcn.[EcnId], &quot; & _
&quot;tblEcn.[RelDt], &quot; & _
&quot;tblDtl.[DtActImpl], &quot; & _
&quot;DateDiff('d',[tblEcn].[RelDt],[tblDtl].[DtActImpl],2) AS DaysDiff, &quot; & _
&quot;Int([daysdiff]/7) AS Weeks, &quot; & _
&quot;[daysdiff]-[weeks]*3+ &quot; & _
&quot;IIf(Weekday([tblDtl].[DtActImpl],2)>5,&quot; & _
&quot;5-Weekday([tblDtl].[DtActImpl],2),0)+&quot; & _
&quot;IIf(Weekday([tblEcn].[RelDt],2)=6,1,0)+1 AS weekdays&quot; & _
&quot;FROM tblEcn &quot; & _
&quot;INNER JOIN tblDtl ON &quot; & _
&quot;tblEcn.[EcnId] = tblDtl.[EcnId]&quot; & _
&quot;WHERE (((tblDtl.[DtActImpl]) &quot; & _
&quot;Between [Forms]![frmDates]![Start Date] And &quot; & _
&quot;[Forms]![frmDates]![Stop Date]) AND &quot; & _
&quot;((tblEcn.[Do Not Process])<>'Do Not Process'));&quot;

' &quot;DateDiff('d',[tblEcn].[RelDt],[tblDtl].[DtActImpl],2) AS DaysDiff, &quot; & _
' DeltaDaysX(tblEcn.RelDt, tblDlt.DtActImpl) as DaysDiff
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CCLINT]

Or, if you have a table with holiday dates, or a string with the holiday dates, then add this to the WHERE criteria, (or a similar logic):

AND WeekDay(TheDate) NOT IN (6,7,1) AND TheDate NOT IN (SELECT HolidayDate FROM tblHolidays WHERE HolidayDate)

(For the String with the holiday dates use the same syntax as used for the WeekDays)

You may want to add the Date criteris to the Sub criteria as well:

AND WeekDay(TheDate) NOT IN (6,7,1) AND TheDate NOT IN (SELECT HolidayDate FROM tblHolidays WHERE HolidayDate BETWEEN [Forms]![frmDates]![Start Date] And [Forms]![frmDates]![Stop Date])
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[raskew]

For example purposes please follow this
sequence of queries in Northwind.
Whenever prompted to [Enter mm/yyyy],
respond with 12/95.

1. Qry101 returns all of the orders during
Dec 95 (12/95).
Records returned = 35

2. Now want to show only those orders
occurring on Monday, Tuesday, Thursday
and Friday-our cult doesn't work on
Saturday, Sunday or Wednesday.
Qry102 adds a mechanism to filter-out
specific days of the week. Note that there
are no records for weekdays Sunday(1),
Wednesday(4) and Saturday(7).
Records returned = 29.

3. Additionally, our cult holds Dec 12th, 14th
and 15th as High Holy Days. We don't
work on those days. Qry103 filters out
our Holy Days.
Records returned = 24

The above is just a rough example but will
hopefully provide you with some ideas.

**************************************************
Qry101

PARAMETERS [enter mm/yyyy] Text;
SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate) Between
DateValue([enter mm/yyyy]) And 
DateAdd("m",1,DateValue([enter mm/yyyy]))-1));

Qry102

PARAMETERS [enter mm/yyyy] Text;
SELECT Orders.OrderID, Orders.OrderDate, 
Weekday([orderdate]) AS WorkDays
FROM Orders
WHERE (((Orders.OrderDate) Between 
DateValue([enter mm/yyyy]) And 
DateAdd("m",1,DateValue([enter mm/yyyy]))-1) 
AND ((Weekday([orderdate])) Not In (1,4,7)));

Qry103

PARAMETERS [enter mm/yyyy] Text;
SELECT Orders.OrderID, Orders.OrderDate, 
Weekday([orderdate]) AS WorkDays
FROM Orders
WHERE (((Orders.OrderDate) Between 
DateValue([enter mm/yyyy]) And 
DateAdd("m",1,DateValue([enter mm/yyyy]))-1 
And (Orders.OrderDate) Not In 
(#12/12/1995#,#12/14/1995#,#12/15/1995#)) 
AND ((Weekday([orderdate])) Not In (1,4,7)));

 

[CCLINT]

Wasn't that exactly the way I said to do it in my post? [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!