CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008
pikk
pikk

Reputation: 855

Sql statement help

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

Upvotes: 4

Views: 117

Answers (4)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

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.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 3

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3939

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

Upvotes: 2

bobs
bobs

Reputation: 22184

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.

Upvotes: 2

Related Questions