CODEWITHSUNDEEP
sqlsql-servert-sql
sylanter
sylanter

Reputation: 49

Find total date in ms sql 2008

I need help to find a total day in ms sql 2008 for example I have a course table like following

+----------+------------+------------+
|  Course  |  DateFrom  |   DateTo   |
+----------+------------+------------+
| Course1a | 12/22/2015 | 12/22/2015 |
| Course1b | 12/22/2015 | 12/22/2015 |
| Course1c | 12/24/2015 | 12/28/2015 |
+----------+------------+------------+

and a Holiday table that store holiday which mean no course during that day

+-----------+------------+
|   name    |  DateFrom  |
+-----------+------------+
| Christmas | 12/25/2015 |
+-----------+------------+

In here I want to have total days for course1 to be 5 days (12/22, 12/24, 12/25(do not count christmas holiday), 12/26, 12/27, 12/28)

Upvotes: 2

Views: 30

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176284

One way to achieve it is to use:

;WITH tally AS
(
  SELECT TOP 1000 r = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1
  FROM master..spt_values
), cte AS
(
  SELECT Course, DATEADD(d, t.r, c.DateFrom) AS dat
  FROM #courses c
  JOIN tally t
    ON DATEADD(d, t.r, c.DateFrom) <= c.DateTo
)
SELECT LEFT(Course, 7) AS Course_Name,
       COUNT(DISTINCT dat) AS Total_Days
FROM cte c
LEFT JOIN #holidays h
  ON c.dat = h.DateFrom
WHERE h.DateFrom IS NULL
GROUP BY LEFT(Course, 7);

LiveDemo

Output:

╔═════════════╦════════════╗
║ Course_Name ║ Total_days ║
╠═════════════╬════════════╣
║ Course1     ║          5 ║
╚═════════════╩════════════╝

How it works:

  • tally generates number table (any method)
  • cte transforms date_from and date_to to multiple rows
  • join with holidays table to exclude holiday dates
  • GROUP BY LEFT(Course, 7) is workaround (your course name should be distinct without suffixes (a,b,c) or you need another column that indicates that 3 courses combined create one course)
  • COUNT only DISTINCT dates to get total days count

Upvotes: 1

Related Questions