SQLTeam.com | Weblogs | Forums

How to count workload for each day - query


#1

Hi, I have a table of data (Jul-Feb) I would like to calculate for each day my workload:

Project No. Start Date End Date
1 01/07/15 04/07/15
2 01/07/15 03/07/15
3 01/07/15 06/07/15
4 02/07/15 06/07/15

what I would get to get as a result is
Date Projects
01/07/15 3
02/07/15 4
03/07/15 4
04/07/15 3
05/07/15 2
06/07/15 2
07/07/15 0

Any help would be appreciated!!


How to count workload each day
#2
; with
dates as
(
    select    [date] = convert(date, '2015-07-01')
    union all
    select    [date] = dateadd(day, 1, [date])
    from      dates
    where     [date]    < '2015-07-31'
)
select    d.[date], c.cnt
from      dates d
cross apply
(
        select  cnt = count(*)
        from    proj x
        where   x.[Start Date]  <= d.[date]
        and     x.[End Date]    >= d.[date]
) c

#3

Thank you!!!