CODEWITHSUNDEEP
sqlms-accessms-access-2013
farmpapa
farmpapa

Reputation: 31

SQL join count of date ranges to table of calendar dates?

I have a tblAbsence

Name    Start   End
Joe    18-Mar-2018 0800 26-Mar-2018 1830
Mary    19-Mar-2018 0010    19-Mar-2018 2349
Adam    21-Mar-2018 0700    21-Mar-2018 1300

Is there a SQL query that would create a new table of dates with the count of people absent that day? 

Date    Absent
18-Mar-18   1
19-Mar-18   2
20-Mar-18   3

I know how to create a recordset from a calendar table and basically rs1.movenext through each date while I Update tblTemp with a count that meets the 

WHERE tblAbsence.start <= rs1.date AND tblAbsence.End >= rs1.date;

but that seems ridiculously repetitive when there is probably some type of JOIN that might work?

Upvotes: 0

Views: 140

Answers (2)

Emre
Emre

Reputation: 387

you may try something like below:

select rs1.date,count(ta.Name) from Calendar rs1
    join tblAbsence ta on (ta.start<DATEADD(day,1,rs1.date) and ta.end >=rs1.date)
group by rs1.date
order by rs1.date

Upvotes: 0

Erik A
Erik A

Reputation: 32632

If you have a list of dates, the problem is pretty simple. You can use a subquery to count from your absence list, where the date from your list is between the start and end of absence.:

SELECT TheDate, (SELECT Count(Name) FROM Absent WHERE Absent.End > DateList.TheDate AND Absent.Start < DateList.TheDate + #23:59:59#) As Absent
From DateList

Where DateList is a list of all dates you're interested in (either from a table, or from Gustav's query)

Upvotes: 1

Related Questions