Sql statement help

Question

i am working on a Gridview on ASP .Net. I have a table in SQL Server which has columns named "surname" and "Date" (DateTime) and duration. The table is for vacation requests. How can I construct a SQL statement to see how many people will be missing for each day?. The point is that the query SELECT [Date], COUNT(DISTINCT surname) GROUP BY [Date] will not show me that actually 8 people will be missing at 2 of september. For example, given the following data:

surname Date       Duration
------- ---------- ---------
Bertram 2011-09-01     3
Coulois 2011-09-01     5
LeBlanc 2011-09-01     6
Fosters 2011-09-01     3
Blanche 2011-09-01     2
Bertram 2011-09-02     6
Gillian 2011-09-02     4
Pikklar 2011-09-02     7
Thierry 2011-09-03     6
Selanne 2011-09-03     6

I want the following results:

Date  Count
----- -----
1 Sep     5
2 Sep     8    
3 Sep     10

Any ideas how to approach it and produce a gridview with those data?. Thx for your time

Answer 1

You can do this using a numbers table. Here I use master..spt_values.

declare @T table
(
  surname varchar(20),
  [Date] datetime,
  Duration int
)

insert into @T values
('Bertram', '2011-09-01',     3),
('Coulois', '2011-09-01',     5),
('LeBlanc', '2011-09-01',     6),
('Fosters', '2011-09-01',     3),
('Blanche', '2011-09-01',     2),
('Bertram', '2011-09-02',     6),
('Gillian', '2011-09-02',     4),
('Pikklar', '2011-09-02',     7),
('Thierry', '2011-09-03',     6),
('Selanne', '2011-09-03',     6)


select dateadd(day, N.number, [Date]) as [Date],
       count(*) as [Count]
from @T as T
  inner join master..spt_values as N
    on N.number between 0 and T.Duration
where N.type = 'P'    
group by dateadd(day, N.number, [Date])
order by dateadd(day, N.number, [Date])

Result:

Date                    Count
----------------------- -----------
2011-09-01 00:00:00.000 5
2011-09-02 00:00:00.000 8
2011-09-03 00:00:00.000 10
2011-09-04 00:00:00.000 9
2011-09-05 00:00:00.000 7
2011-09-06 00:00:00.000 7
2011-09-07 00:00:00.000 5
2011-09-08 00:00:00.000 4
2011-09-09 00:00:00.000 3

Answer 2

The following should give you a breakdown of all holiday starting with the first day booked and ending with the last day booked off (not just the start date). It should also report dates within the range with zero bookings (if any exist);

2011-09-01  5
2011-09-02  8
2011-09-03  10
2011-09-04  9
2011-09-05  7
2011-09-06  7
2011-09-07  5
2011-09-08  4
2011-09-09  3

The code works out the last booked date and then calculates all bookings for each day in the dynamic date range

DECLARE @MaxDate date
SELECT  @MaxDate = max(dateAdd(day, duration, date)) 
FROM holiday;

WITH HolidayDates (holidayDate)
as
(
    SELECT  MIN(date) holidayDate 
    FROM    holiday 
    UNION ALL 
    SELECT  DateAdd(day, 1, holidayDate)
    FROM    holidayDates 
    WHERE   holidayDate <@MaxDate
)

SELECT      cast(hd.holidayDate as date) holidayDate
            , count(h.surname) PeopleOnHoliday
FROM        HolidayDates hd
LEFT JOIN   holiday h on hd.holidayDate between h.date AND dateAdd(day, duration, date) 
GROUP BY    hd.holidayDate
ORDER BY    hd.holidayDate

hope this helps...