Counting Dates between a Start and End, Loop through records, providepeg counts

[gumby]

I have a leave table with a StartDate and an EndDate field for leave
requests. I can count how many people are gone if they only are gone
for one day, but if it is a range I would like to be able to query
them and it show me how many people are gone on a given day.

Example:

RecordID, StartDate, EndDate
1, 01/01/2009,01/03/2009
2,01/01/2009,01/04/2009
3,01/01/2009,01/02/2009
4,01/01/2009,01/01/2009

I would like the query to tell me that

LeaveDate 1/1/2009, 1/2/2009, 1/3/2009, 1/4/2009
OnLeave 4 3 2 1



I was thinking about how I could break the days out in between the
dates that had ranges and then count them. Not sure how to do this. I
also do not what to have people put in individual dates for leave
requests that are greater than one day.

Thanks,

 

[pietlinden]

I have a leave table with a StartDate and an EndDate field for leave
requests. I can count how many people are gone if they only are gone
for one day, but if it is a range I would like to be able to query
them and it show me how many people are gone on a given day.

Example:

RecordID, StartDate, EndDate
1, 01/01/2009,01/03/2009
2,01/01/2009,01/04/2009
3,01/01/2009,01/02/2009
4,01/01/2009,01/01/2009

I would like the query to tell me that

LeaveDate      1/1/2009, 1/2/2009, 1/3/2009, 1/4/2009
OnLeave          4               3            2              1

I was thinking about how I could break the days out in between the
dates that had ranges and then count them. Not sure how to do this. I
also do not what to have people put in individual dates for leave
requests that are greater than one day.

Thanks,

Here's the only way I could get it to work... Maybe there's an easier
way, but I don't know it.

First, create a table of CalendarDates (I called mine
tCalendarDates). Then I used the following code to create a record
for each date in the sequence. This is necessary so you can get a
count of absences grouped by date.


Public Sub CreateDates(ByVal dtStart As Date, ByVal dtFinish As Date)

'CREATE TABLE tCalendarDates(CalendarDate DATE PRIMARY KEY);
<===CalendarDate is unique.

Dim dtThis As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tCalendarDates",
dbOpenTable, dbAppendOnly)

'Populate the table with all the dates between dtStart and dtFinish
For dtThis = dtStart To dtFinish Step 1
Debug.Print dtThis
rs.AddNew
rs.Fields("CalendarDate") = dtThis
rs.Update
Next dtThis

rs.Close
Set rs = Nothing

End Sub

Given that table
tCalendarDates(CalendarDate DATE);

and

the Leave table
tLeave (EmployeeID, LeaveStart, LeaveEnd)

then you can easily generate all the dates an employee is absent:
qDatesAway:
SELECT tCalendarDates.CalendarDate, tLeave.EmployeeID,
tLeave.LeaveStart, tLeave.LeaveEnd
FROM tLeave, tCalendarDates
WHERE (((tLeave.LeaveStart)<=[CalendarDate]) AND ((tLeave.LeaveEnd)>=
[CalendarDate]));

Then all you have to do is group that result by CalendarDate and Count
EmployeeID:

SELECT tCalendarDates.CalendarDate, Count(qDatesAway.EmployeeID) AS
AbsentEmployeeCount
FROM qDatesAway RIGHT JOIN tCalendarDates ON qDatesAway.CalendarDate =
tCalendarDates.CalendarDate
GROUP BY tCalendarDates.CalendarDate
HAVING (((tCalendarDates.CalendarDate) Between #1/1/2009# And
#1/15/2009#))
ORDER BY tCalendarDates.CalendarDate;

Hey, wait, I cheated and added tCalendarDates to the summary, so that
the query would return a zero count for any day that there were no
absences. (Otherwise, if you tried to group by
qDatesAway.CalendarDate you would just get the dates where absences
exist).

Hope this helps.

If you need clarification, ask away.

Pieter

 

[pietlinden]

I would like the query to tell me that

LeaveDate      1/1/2009, 1/2/2009, 1/3/2009, 1/4/2009
OnLeave          4               3            2              1

I don't think the above query structure is going to work, at least if
you have a lot of dates in your date range. The crosstab is going to
run out of columns, because there are more than 255 days in a year.

The only way I could get it to work and include all the dates was to
do it this way:

CalendarDate Absences
1/1/2009 1
1/2/2009 1
1/3/2009 1
1/4/2009 2
1/5/2009 2
1/6/2009 1
1/7/2009 1
1/8/2009 1
1/9/2009 1
1/10/2009 0
1/11/2009 0
1/12/2009 0
1/13/2009 0
1/14/2009 0
1/15/2009 0
....

besides, it would probably be easier to summarize in a report with the
data set up this way. You can do counts in the grouping section (by
date) and show/hide the summary section on the fly.

 

[gumby]

I have a leave table with a StartDate and an EndDate field for leave
requests. I can count how many people are gone if they only are gone
for one day, but if it is a range I would like to be able to query
them and it show me how many people are gone on a given day.

RecordID, StartDate, EndDate
1, 01/01/2009,01/03/2009
2,01/01/2009,01/04/2009
3,01/01/2009,01/02/2009
4,01/01/2009,01/01/2009

I would like the query to tell me that

LeaveDate      1/1/2009, 1/2/2009, 1/3/2009, 1/4/2009
OnLeave          4               3            2              1

I was thinking about how I could break the days out in between the
dates that had ranges and then count them. Not sure how to do this. I
also do not what to have people put in individual dates for leave
requests that are greater than one day.

Thanks,

Here's the only way I could get it to work...  Maybe there's an easier
way, but I don't know it.

First, create a table of CalendarDates (I called mine
tCalendarDates).  Then I used the following code to create a record
for each date in the sequence.  This is necessary so you can get a
count of absences grouped by date.

Public Sub CreateDates(ByVal dtStart As Date, ByVal dtFinish As Date)

'CREATE TABLE tCalendarDates(CalendarDate DATE PRIMARY KEY);
<===CalendarDate is unique.

    Dim dtThis As Date
    Dim rs As DAO.Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("tCalendarDates",
dbOpenTable, dbAppendOnly)

'Populate the table with all the dates between dtStart and dtFinish
    For dtThis = dtStart To dtFinish Step 1
        Debug.Print dtThis
        rs.AddNew
        rs.Fields("CalendarDate") = dtThis
        rs.Update
    Next dtThis

    rs.Close
    Set rs = Nothing

End Sub

Given that table
tCalendarDates(CalendarDate DATE);

and

the Leave table
tLeave (EmployeeID, LeaveStart, LeaveEnd)

then you can easily generate all the dates an employee is absent:
qDatesAway:
SELECT tCalendarDates.CalendarDate, tLeave.EmployeeID,
tLeave.LeaveStart, tLeave.LeaveEnd
FROM tLeave, tCalendarDates
WHERE (((tLeave.LeaveStart)<=[CalendarDate]) AND ((tLeave.LeaveEnd)>=
[CalendarDate]));

Then all you have to do is group that result by CalendarDate and Count
EmployeeID:

SELECT tCalendarDates.CalendarDate, Count(qDatesAway.EmployeeID) AS
AbsentEmployeeCount
FROM qDatesAway RIGHT JOIN tCalendarDates ON qDatesAway.CalendarDate =
tCalendarDates.CalendarDate
GROUP BY tCalendarDates.CalendarDate
HAVING (((tCalendarDates.CalendarDate) Between #1/1/2009# And
#1/15/2009#))
ORDER BY tCalendarDates.CalendarDate;

Hey, wait, I cheated and added tCalendarDates to the summary, so that
the query would return a zero count for any day that there were no
absences.  (Otherwise, if you tried to group by
qDatesAway.CalendarDate you would just get the dates where absences
exist).

Hope this helps.

If you need clarification, ask away.

Pieter- Hide quoted text -

- Show quoted text -

No join with the qDatesAway? Thanks for the idea. I when through your
instructions and that last query is a monster. It wont run.

David

 

[gumby]

I have a leave table with a StartDate and an EndDate field for leave
requests. I can count how many people are gone if they only are gone
for one day, but if it is a range I would like to be able to query
them and it show me how many people are gone on a given day.

RecordID, StartDate, EndDate
1, 01/01/2009,01/03/2009
2,01/01/2009,01/04/2009
3,01/01/2009,01/02/2009
4,01/01/2009,01/01/2009

I would like the query to tell me that

LeaveDate      1/1/2009, 1/2/2009, 1/3/2009, 1/4/2009
OnLeave          4               3            2              1

I was thinking about how I could break the days out in between the
dates that had ranges and then count them. Not sure how to do this. I
also do not what to have people put in individual dates for leave
requests that are greater than one day.

Thanks,

Here's the only way I could get it to work...  Maybe there's an easier
way, but I don't know it.

First, create a table of CalendarDates (I called mine
tCalendarDates).  Then I used the following code to create a record
for each date in the sequence.  This is necessary so you can get a
count of absences grouped by date.

Public Sub CreateDates(ByVal dtStart As Date, ByVal dtFinish As Date)

'CREATE TABLE tCalendarDates(CalendarDate DATE PRIMARY KEY);
<===CalendarDate is unique.

    Dim dtThis As Date
    Dim rs As DAO.Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("tCalendarDates",
dbOpenTable, dbAppendOnly)

'Populate the table with all the dates between dtStart and dtFinish
    For dtThis = dtStart To dtFinish Step 1
        Debug.Print dtThis
        rs.AddNew
        rs.Fields("CalendarDate") = dtThis
        rs.Update
    Next dtThis

    rs.Close
    Set rs = Nothing

End Sub

Given that table
tCalendarDates(CalendarDate DATE);

and

the Leave table
tLeave (EmployeeID, LeaveStart, LeaveEnd)

then you can easily generate all the dates an employee is absent:
qDatesAway:
SELECT tCalendarDates.CalendarDate, tLeave.EmployeeID,
tLeave.LeaveStart, tLeave.LeaveEnd
FROM tLeave, tCalendarDates
WHERE (((tLeave.LeaveStart)<=[CalendarDate]) AND ((tLeave.LeaveEnd)>=
[CalendarDate]));

Then all you have to do is group that result by CalendarDate and Count
EmployeeID:

SELECT tCalendarDates.CalendarDate, Count(qDatesAway.EmployeeID) AS
AbsentEmployeeCount
FROM qDatesAway RIGHT JOIN tCalendarDates ON qDatesAway.CalendarDate =
tCalendarDates.CalendarDate
GROUP BY tCalendarDates.CalendarDate
HAVING (((tCalendarDates.CalendarDate) Between #1/1/2009# And
#1/15/2009#))
ORDER BY tCalendarDates.CalendarDate;

Hey, wait, I cheated and added tCalendarDates to the summary, so that
the query would return a zero count for any day that there were no
absences.  (Otherwise, if you tried to group by
qDatesAway.CalendarDate you would just get the dates where absences
exist).

Hope this helps.

If you need clarification, ask away.

Pieter- Hide quoted text -

- Show quoted text -

Probably because the table of CalDate I build was from 2000 -
2025......

David