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

The actual date is the start date of the vacation, correct? It appears to me that you don't have a date for every single day that person is gone, so we need to do some date calculations to get the right dates to show up. — Jennifer S, Aug 31 '11 at 22:10
And does duration include the weekend days, or exclude them? — Jennifer S, Aug 31 '11 at 22:10
To work out who is away on a particular day you will need a record for each day from startdate to startdate+duration. You could create another related child table to store these and create the records at the same time as the parent records which will make query the data easier or you could create them dynamically in the query using a CTE or a temp table which is more complex but does not require altering the DB. — Ben Robinson, Aug 31 '11 at 22:12
Exactly, i dont have a date for every single day. Yes but where do i have to make the calculations, inside the sql statement, or the gridview. do you have any ideas how to approach it? — pikk, Aug 31 '11 at 22:12
@Ben Robinson hm i dont have enough knowledge to do this. can you give me more details please?. — pikk, Aug 31 '11 at 22:15

score 3 · Answer 1 · edited May 23 '17 at 12:04

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

bobs · Answer 2 · 2011-08-31T22:19:45.690

2

You could try something like this

WITH Dates AS
    (SELECT CAST('9/1/2011' AS DATE) AS [DATE]
    UNION SELECT '9/2/2011'
    UNION SELECT '9/3/2011'
    )

SELECT [DATE], SUM(OnVacation) AS COUNT
FROM
    (
    SELECT [DATE], 
        CASE WHEN [DATE] BETWEEN StartDate AND DATEADD(dd, Duration, StartDate) 
             THEN 1 ELSE 0 END AS OnVacation
    FROM Vacations
    CROSS JOIN Dates
    ) x
GROUP BY [DATE]
ORDER BY [DATE]

The Dates table would be a table that has a list of dates you want to view. It's a Common Table Expression (CTE) in this query.

This also assumes that start date is the first day of the duration.

edited Aug 31 '11 at 22:19

answered Aug 31 '11 at 22:12

bobs

21,844
12
67
78

yes but in order to see all the days of a year then i have to cast all the available dates of the year? – pikk Aug 31 '11 at 22:24
1

I'd suggest that you create a table with dates, or you create a more dynamic CTE. – bobs Aug 31 '11 at 22:30

score 2 · Accepted Answer · answered Aug 31 '11 at 22:41

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...

score 1 · Answer 4 · edited May 23 '17 at 12:12

I like bobs answer Sql statement help.

Here is the CTE that you need to create a column of dates given a start & end date.

DECLARE @start datetime = '2011-01-01'
DECLARE @end datetime = '2011-01-31'

; WITH Dates as
(
    SELECT @start as d
    UNION ALL 
    SELECT DATEADD(DAY, 1, d)FROM dates WHERE d < @end
)
SELECT * FROM Dates

If you plug this into his answer, and create a stored procedure that accepts a 'start' and 'end parameter, you should have your answer.

Sql statement help

4 Answers4