KInd of a tricky one this I'm not sure I'm overthinking it but here goes:
So I have one table with data like this:
ID StartDate EndDate Days
== ========== ======== =====
1 01/01/2019 03/01/2019 3
2 05/01/2019 07/01/2019 3
3 09/01/2019 12/01/2019 4
So each range is counted as one episode, however I have to show how many episodes per Month, per Day etc. so I need to map each one to each day in a range, say from 2017 to now.
I have concocted a query as per below however this now counts each row I've split up. I need to map to each day, but I also olny need to count only one row per range/id if that makes sense.
Not entirely clear to me what your end goal is. If you show the desired output that would help. If you are trying to get how many Id's occur on a range of dates, query shown below is one way to do it. Most of the code is creating a numbers table. If you already have a numbers table or calendar table in your database, you should use that.
-- CREATE A NUMBERS TABLE.
DROP TABLE IF EXISTS #Nums;
CREATE TABLE #Nums(N INT);
;WITH cte(n) AS
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)
INSERT INTO #Nums
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM
cte a
CROSS JOIN cte b
CROSS JOIN cte c
CROSS JOIN cte d
CROSS JOIN cte e;
-- QUERY
DECLARE
@StartDate DATE = '20190101';
SELECT
Date = DATEADD(DAY,NN.n,@StartDate),
DailyEposideCount = COUNT(*)
FROM
#Nums NN
INNER JOIN YourTable y ON
y.StartDate <= DATEADD(DAY,NN.n,@StartDate)
AND y.EndDate >= DATEADD(DAY,NN.n,@StartDate)
GROUP BY
NN.n
ORDER BY
Date;
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cteTally10 c1 CROSS JOIN cteTally10 c2 CROSS JOIN cteTally10 c3
)
SELECT YT.ID, YT.StartDate, YT.EndDate, 1 AS Days,
DATEADD(DAY, t.number - 1, YT.StartDate) AS DailyDate
FROM dbo.YourTable YT
INNER JOIN cteTally1000 T ON T.number BETWEEN 1 AND YT.Days
ORDER BY YT.ID, YT.DailyDate
I'll try and explain this a bit more, as it's difficult to actually understand what I need, I know kind of what I want but decoding the data is a bit confusing.
Basically with the below I can count the rows and that gives me the number of episodes. In my reporting tool (Tableau) I need to be able to count those episodes but by each day, but at the same time not duplicate the count if that makes sense. So for the first one if I split this out it would give me a count of 3, but there is only one episode. I need to be able to report when the episode started and also ended by Month.
If that makes any sense, for the 2nd row I'd have a count of two days in the episode but one episode. If I was to map that to our master date table it will show 2 episodes, masked as 2 days in the one episode, but I only want to count one episode, over the 2 day period.