Ryan R Ryan R - 1 month ago 9
SQL Question

How do you count number tasks in schedule per day in SQL Server?

I'm trying to create a query in SQL Server that will return a count of tasks per day. If the start and finish date for a task are on the same day, then it should only add one to the count.

Here is a small example data set:

+----+------------+-------------+
| ID | Start_Date | Finish_Date |
+----+------------+-------------+
| 1 | 24-Oct-16 | 24-Oct-16 |
| 2 | 24-Oct-16 | 26-Oct-16 |
| 3 | 25-Oct-16 | 26-Oct-16 |
| 4 | 26-Oct-16 | 27-Oct-16 |
| 5 | 26-Oct-16 | 28-Oct-16 |
+----+------------+-------------+


Here would be the expected result:

+-----------+----------------+
| Date | Count_Of_Tasks |
+-----------+----------------+
| 24-Oct-16 | 2 |
| 25-Oct-16 | 2 |
| 26-Oct-16 | 4 |
| 27-Oct-16 | 2 |
| 28-Oct-16 | 1 |
+-----------+----------------+


Can anyone create an example query that can count number of tasks per day?

Thanks for your help!

Answer

You need a calendar table to do this

I have used Recursive CTE to generate the dates between the start and finish date range on the fly. But it is always better to have a Calendar table created in database. It will be very helpful in such queries

;with data as
(
select * from (
VALUES
    (1, '2016-10-24 00:00:00', '2016-10-24 00:00:00'),
    (2, '2016-10-24 00:00:00', '2016-10-26 00:00:00'),
    (3, '2016-10-25 00:00:00', '2016-10-26 00:00:00'),
    (4, '2016-10-26 00:00:00', '2016-10-27 00:00:00'),
    (5, '2016-10-26 00:00:00', '2016-10-28 00:00:00')) tc ([ID], [Start_Date], [Finish_Date])
),calendar
     AS (SELECT dates = CONVERT(DATETIME, '24-Oct-16') -- Min Start_Date 
         UNION ALL
         SELECT dates = Dateadd(DAY, 1, dates)
         FROM   calendar
         WHERE  dates < '28-Oct-16') -- Max Finish_Date 
SELECT c.dates,
       Count(s.Start_Date) AS Count_Of_Tasks
FROM   calendar c
       LEFT JOIN data s
              ON c.dates between s.Start_Date and s.Finish_Date
Group by c.dates

Result :

╔═════════════════════════╦════════════════╗
║          dates          ║ Count_Of_Tasks ║
╠═════════════════════════╬════════════════╣
║ 2016-10-24 00:00:00.000 ║              2 ║
║ 2016-10-25 00:00:00.000 ║              2 ║
║ 2016-10-26 00:00:00.000 ║              4 ║
║ 2016-10-27 00:00:00.000 ║              2 ║
║ 2016-10-28 00:00:00.000 ║              1 ║
╚═════════════════════════╩════════════════╝
Comments