Geek Answers Handbook

Sql Help

I am working on Gridview on ASP.Net. I have a table in SQL Server that has columns named "last name" and "Date" (DateTime) and duration. The table is for vacation requests. How can I build an SQL statement to find out how many people will be absent for each day ?. The fact is that the query SELECT [Date], COUNT(DISTINCT surname) GROUP BY [Date] will not show me that in fact 8 people will be absent on September 2. 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 need the following results:

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

Any ideas how to approach it and create a gridview with this data ?. thanks for your time

+4
source share
4 answers

The following should give you a breakdown of the entire holiday, starting from the first day booked and ending with the last day booked (and not just the start date). It should also report dates within the range with zero orders (if they 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 processes the last registered date, and then calculates all orders for each day in a 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 ...

+2
source

You can do this using the 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
+3
source

You can 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 will be a table that has a list of dates that you want to view. This is the general table output (CTE) in this query.

It also suggests that the start date is the first day of the duration.

+2
source

I like bobs answer Sql statement help .

Here is the CTE that you need to create a date column with a start and 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 insert this into your answer and create a stored procedure that accepts the "start" and "end" parameters, you must have your answer.

+1
source

More articles:


All Articles