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])<>"Do Not Process"));

 

[Autoeng]

Getting there

Been trying some stuff out to make the query work but can't seem to get it. Anyone have any pointers as to what I have done wrong. Created table tblHoliday with field Holiday.

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-(SELECT COUNT Holiday As Days FROM tblHoliday WHERE Holiday BETWEEN [Forms]![frmDates]![Start Date] And [Forms]![frmDates]![Stop Date])(Days) 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])<>"Do Not Process"));

 

[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)));

 

[Autoeng]

Raskew:

Copied your posted the code in query SQL view in the Northwind database to understand but there must be something wrong as I get 0 returns.

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));

Autoeng

 

[79ssecca]

Basically just add this to the where clause of your SQL.

"AND [Release Date] not in (select HDate From tbl_HolidayDates)"


What you are doing here is telling it to list all the dates unless it is in the table tbl_HolidayDates.

Good Luck
79ssecca

 

[raskew]

Strange. I copied the code back from the forum to new queries and they worked without problem. Are you entering month/year as 12/95 or something near? The Orders table has a very limited date range.

 

[Autoeng]

It is strange as I tried it again with same results (none). Oh hell, I'm tired of fooling with the thing.

Autoeng

 

[Autoeng]

I hate to post on the query again but I am not getting anywhere and I need to add this functionality so if anyone else cares to chip in I would be most grateful.

I guess the problems lies in that:

I need to subtract the count of Holiday found in tblHoliday between the start and stop dates from frmDates for a record that was implemented (stored in Actual Implementation Date in table ECNDetailtbl during that period. (I know that my naming convention sucks but as you can see as I went along I was learning).

This code is just too complex for my ability. I have tried numerous iterations of each of the suggestions offered with no luck.

Autoeng