Query to report Dates spreading over Mon to Fri

[moodhi]

I have a table to record sickness i.e. DateStart and DateEnd.

I need to report total sickness recorded for an employee for each of the weekday in the year.

Mon Tue Wed Thu Fri Sat Sun
2 1 3 0 4 0 5

How can I generate SQL for the above?

 

[MajP]

you can use the weekday function on the date
https://www.excelfunctions.net/vba-weekday-function.html
Then do a crosstab with weekday value as column

 

[plog]

Sorry to be nitpick guy, but to generate your expected results you need to add something to Maj's advice. If Thu=0 it will not show up in your final results without a little trickery.

To get a day that has no data in your final results you will need another table of all the days you want to report on. So, you will need a Day table that has all the days of the week you want to report on. Then come the sub-queries.

The first sub query will take your existing table and be a simple aggregate query that gets the results like so:

Weekday, SickDays
Monday, 2
Tuesday, 1
Wednesday, 3
Friday, 4
...

Next you make a another sub query using the Days of the week table mentioned above and the above query I outlined. You LEFT JOIN the query to the DAys of the week table, showing all from Days of the week. You would make a calculated field for SickDays to convert Nulls to 0's. Save that sub-query and then do the cross-tab.

 

[moodhi]

OK but what about the difference between the Start Date and the End Date?There is obviously range of dates between

 

[plog]

This is in the same vein as making Thur=0 appear in your report, but a lot worse. You can't create data where there is none. You could have 1 record that represents 5 days of sickness, but in a query you can't explode that 1 record from the source into 5 records in the result---without major trickery.

I am sure someone will come along with a VBA recordset solution. But to accomplish this with just SQL you will need a table of all the dates you want to report on. Not days (Mon, Tues, Wed, etc.) but dates (1/1/2018, 1/2/2018, 1/3/2018, etc.). So to pursue this with SQL you need such a table.

I would search this forum for similar VBA solutions (or like I said, wait a bit, someone will post a solution).

 

[MajP]

You can't create data where there is none. You could have 1 record that represents 5 days of sickness, but in a query you can't explode that 1 record from the source into 5 records in the result---without major trickery.

I missed the point where you just have start and stop and not a record for each date. If it was me I would have another table with EmployeeID, DateOfSickness. I would loop your table and create a record for every day in the new table. You can also do this in pure sql, but I would think it would be a little ugly. I would think it requires a cartesian join to your dates table.

 

[moodhi]

Apologies guys. Many thanks of all useful responses.
Basically I was trying to see if there was a quick MS Access Query solution. But since there isn't I have used VBA to first loop round for all employees and within that loop round for dates between DateStart and DateEnd for each of the record and simply increment a count in variables Mon, Tue.....Sun and that's it really.
Thanks for the help.

 

[theDBguy]

Hi moodhi,

See if this example on how to use a Cartesian Query might give you some ideas.