Holidays and Records count

Z

Zimme

Hello everyone:

I need to seek your wisdoms and advice again. I have two questions this time:

1) I want to sum volumes by days excluding the weekends and holidays. I
created a “Holidays†table containing only the holiday names and dates, eg
Easter; 2006/04/14, Xmas; 2006/12/25 etc and used it as a dated benchmark.
The ProductionDay is also a date/time format as yyyy/mm/dd.

SELECT qryOper.ProductionDay, Sum(qryOper.vol) AS SumOfvol,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]=[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

For testing, the query above gave me the past Good Friday and Easter Monday.
But I want the opposite. So, I changed the “=†to “<>†as HolDate <>
ProductionDay.

SELECT qryOper.ProductionDay, Sum(qryOper.cMailPieces) AS SumOfcMailPieces,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]<>[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

I got zero records with this “<>†change. Then, I tried to change the inner
and outer join type. Still, I was not able to get the result.

Sample before with 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/14 3 5
2006/04/17 700818 1
2006/04/18 1112158 2

Expected result with out 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/18 1112158 2


2) With DateAdd("d",-85,(select Max([ProDuctionDay]) from qryoper)), the –85
gave me a constant records of the last 60 weekdays. When I got the Holidays
issues resolved, how do I build in a formula to obtain a constant records of
60?

Thank you. Wish you all a nice day.

Zimme
 
K

KARL DEWEY

This will give you the last 60 workday excluding the weekends and holidays.
Create a table name CountNumber with number field named CountNUM containing
zero through 100. Use your Holidays table with dates. You can join this
query in your totals query.

SELECT TOP 60 Date()-[CountNUM] AS [My Dates]
FROM CountNumber, Holidays
WHERE (((Date()-[CountNUM])<[Holiday]) AND ((Weekday(Date()-[CountNUM]))
Between 2 And 6))
GROUP BY Date()-[CountNUM]
ORDER BY Date()-[CountNUM] DESC , Date()-[CountNUM] DESC;


Zimme said:
Hello everyone:

I need to seek your wisdoms and advice again. I have two questions this time:

1) I want to sum volumes by days excluding the weekends and holidays. I
created a “Holidays†table containing only the holiday names and dates, eg
Easter; 2006/04/14, Xmas; 2006/12/25 etc and used it as a dated benchmark.
The ProductionDay is also a date/time format as yyyy/mm/dd.

SELECT qryOper.ProductionDay, Sum(qryOper.vol) AS SumOfvol,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]=[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

For testing, the query above gave me the past Good Friday and Easter Monday.
But I want the opposite. So, I changed the “=†to “<>†as HolDate <>
ProductionDay.

SELECT qryOper.ProductionDay, Sum(qryOper.cMailPieces) AS SumOfcMailPieces,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]<>[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

I got zero records with this “<>†change. Then, I tried to change the inner
and outer join type. Still, I was not able to get the result.

Sample before with 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/14 3 5
2006/04/17 700818 1
2006/04/18 1112158 2

Expected result with out 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/18 1112158 2


2) With DateAdd("d",-85,(select Max([ProDuctionDay]) from qryoper)), the –85
gave me a constant records of the last 60 weekdays. When I got the Holidays
issues resolved, how do I build in a formula to obtain a constant records of
60?

Thank you. Wish you all a nice day.

Zimme
Click to expand...
 
K

KARL DEWEY

In double check it did not work correct. I used two queries and it works.
The first query named [Past workdays].

SELECT Date()-[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((Weekday(Date()-[CountNUM])) Between 2 And 6))
GROUP BY Date()-[CountNUM]
ORDER BY Date()-[CountNUM] DESC;

SELECT TOP 60 [Past workdays].[My Dates]
FROM [Past workdays] LEFT JOIN Holidays ON [Past workdays].[My Dates] =
Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Past workdays].[My Dates] DESC;


KARL DEWEY said:
This will give you the last 60 workday excluding the weekends and holidays.
Create a table name CountNumber with number field named CountNUM containing
zero through 100. Use your Holidays table with dates. You can join this
query in your totals query.

SELECT TOP 60 Date()-[CountNUM] AS [My Dates]
FROM CountNumber, Holidays
WHERE (((Date()-[CountNUM])<[Holiday]) AND ((Weekday(Date()-[CountNUM]))
Between 2 And 6))
GROUP BY Date()-[CountNUM]
ORDER BY Date()-[CountNUM] DESC , Date()-[CountNUM] DESC;


Zimme said:
Hello everyone:

I need to seek your wisdoms and advice again. I have two questions this time:

1) I want to sum volumes by days excluding the weekends and holidays. I
created a “Holidays†table containing only the holiday names and dates, eg
Easter; 2006/04/14, Xmas; 2006/12/25 etc and used it as a dated benchmark.
The ProductionDay is also a date/time format as yyyy/mm/dd.

SELECT qryOper.ProductionDay, Sum(qryOper.vol) AS SumOfvol,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]=[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

For testing, the query above gave me the past Good Friday and Easter Monday.
But I want the opposite. So, I changed the “=†to “<>†as HolDate <>
ProductionDay.

SELECT qryOper.ProductionDay, Sum(qryOper.cMailPieces) AS SumOfcMailPieces,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]<>[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

I got zero records with this “<>†change. Then, I tried to change the inner
and outer join type. Still, I was not able to get the result.

Sample before with 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/14 3 5
2006/04/17 700818 1
2006/04/18 1112158 2

Expected result with out 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/18 1112158 2


2) With DateAdd("d",-85,(select Max([ProDuctionDay]) from qryoper)), the –85
gave me a constant records of the last 60 weekdays. When I got the Holidays
issues resolved, how do I build in a formula to obtain a constant records of
60?

Thank you. Wish you all a nice day.

Zimme
Click to expand...
Click to expand...
 
Z

Zimme

Thank you. It works beautifully. Best regards.

zimme

KARL DEWEY said:
In double check it did not work correct. I used two queries and it works.
The first query named [Past workdays].

SELECT Date()-[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((Weekday(Date()-[CountNUM])) Between 2 And 6))
GROUP BY Date()-[CountNUM]
ORDER BY Date()-[CountNUM] DESC;

SELECT TOP 60 [Past workdays].[My Dates]
FROM [Past workdays] LEFT JOIN Holidays ON [Past workdays].[My Dates] =
Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Past workdays].[My Dates] DESC;


KARL DEWEY said:
This will give you the last 60 workday excluding the weekends and holidays.
Create a table name CountNumber with number field named CountNUM containing
zero through 100. Use your Holidays table with dates. You can join this
query in your totals query.

SELECT TOP 60 Date()-[CountNUM] AS [My Dates]
FROM CountNumber, Holidays
WHERE (((Date()-[CountNUM])<[Holiday]) AND ((Weekday(Date()-[CountNUM]))
Between 2 And 6))
GROUP BY Date()-[CountNUM]
ORDER BY Date()-[CountNUM] DESC , Date()-[CountNUM] DESC;


Zimme said:
Hello everyone:

I need to seek your wisdoms and advice again. I have two questions this time:

1) I want to sum volumes by days excluding the weekends and holidays. I
created a “Holidays†table containing only the holiday names and dates, eg
Easter; 2006/04/14, Xmas; 2006/12/25 etc and used it as a dated benchmark.
The ProductionDay is also a date/time format as yyyy/mm/dd.

SELECT qryOper.ProductionDay, Sum(qryOper.vol) AS SumOfvol,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]=[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

For testing, the query above gave me the past Good Friday and Easter Monday.
But I want the opposite. So, I changed the “=†to “<>†as HolDate <>
ProductionDay.

SELECT qryOper.ProductionDay, Sum(qryOper.cMailPieces) AS SumOfcMailPieces,
Weekday([productionday],2) AS ProdWkDayNum
FROM Holidays INNER JOIN qryOper ON Holidays.HolDate = qryOper.ProductionDay
WHERE (((qryOper.ProductionDay) Between DateAdd("d",-85,(select
Max([ProDuctionDay]) from qryoper)) And (select Max([ProDuctionDay]) -1 from
qryoper)) AND ([holDate]<>[ProductionDay]))
GROUP BY qryOper.ProductionDay, Weekday([productionday],2)
HAVING (((Weekday([productionday],2))<6));

I got zero records with this “<>†change. Then, I tried to change the inner
and outer join type. Still, I was not able to get the result.

Sample before with 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/14 3 5
2006/04/17 700818 1
2006/04/18 1112158 2

Expected result with out 2006 Apr 14 and 2006 Apr 17 as holidays

ProductionDay SumOfvol ProdWkDayNum
2006/04/11 929615 2
2006/04/12 875089 3
2006/04/13 915273 4
2006/04/18 1112158 2


2) With DateAdd("d",-85,(select Max([ProDuctionDay]) from qryoper)), the –85
gave me a constant records of the last 60 weekdays. When I got the Holidays
issues resolved, how do I build in a formula to obtain a constant records of
60?

Thank you. Wish you all a nice day.

Zimme
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

selecting records BACK from the maximum date 8

Top