Forum Discussion
Help with SQL Code
Hi,
I have a table called FACT_DELAYS which contains the following columns:
ID, START_DATE, END_DATE, FILTERED_MINUTES
If an event is still active then END_DATE IS NULL.
Example Data:
1, 20/10/24, null, 25
2, 07/11/24, null, 67
3, 02/08/23, 10/11/24, 43
4, 01/01/22, null, 20
The problem I have got is that it gives me an as of now value when I run the sql.
I want to be able to dynamically show the previous 7 days and if a delay covers multiple days then it shows up in each day.
My desired output would be:
DATE, COUNT(FILTERED_MINUTES)
11/11/24, 3
10/11/24, 4
09/11/24, 4
08/11/24, 4
07/11/24, 4
06/11/24, 3
05/11/24, 3
Any Help will be much appreciated!
Thanks
- Use table value constructor OR UNION to build a date list of past 7 days.
- Use OUTER APPLY pass the date into inner query as parameter.
- COUNT rows that fit conditions and returns the result to main query.
CREATE TABLE #FACT_DELAYS ( [ID] int, [START_DATE] date, [END_DATE] date, [FILTERED_MINUTES] int ) INSERT INTO #FACT_DELAYS VALUES (1, '2024-10-20', null, 25) ,(2, '2024-11-07', null, 67) ,(3, '2023-08-02', '2024-11-10', 43) ,(4, '2022-01-01', null, 20) --,(5, '2024-11-09', null, 21) SELECT DS.D AS [DATE], ISNULL(FN.N, 0) AS [COUNT(FILTERED_MINUTES)] FROM (VALUES ( CAST( GETDATE() AS DATE) ) ,( CAST( DATEADD( DAY, -1, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -2, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -3, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -4, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -5, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -6, GETDATE() ) AS DATE) ) --,( CAST( DATEADD( DAY, -7, GETDATE() ) AS DATE) ) ) AS DS(D) OUTER APPLY ( SELECT COUNT([FILTERED_MINUTES]) AS N FROM #FACT_DELAYS F WHERE F.START_DATE <= DS.D AND (F.END_DATE IS NULL OR F.END_DATE >= DS.D) )FN ORDER BY DS.D DESC DROP TABLE IF EXISTS #FACT_DELAYS
- Use table value constructor OR UNION to build a date list of past 7 days.
- Use OUTER APPLY pass the date into inner query as parameter.
- COUNT rows that fit conditions and returns the result to main query.
CREATE TABLE #FACT_DELAYS ( [ID] int, [START_DATE] date, [END_DATE] date, [FILTERED_MINUTES] int ) INSERT INTO #FACT_DELAYS VALUES (1, '2024-10-20', null, 25) ,(2, '2024-11-07', null, 67) ,(3, '2023-08-02', '2024-11-10', 43) ,(4, '2022-01-01', null, 20) --,(5, '2024-11-09', null, 21) SELECT DS.D AS [DATE], ISNULL(FN.N, 0) AS [COUNT(FILTERED_MINUTES)] FROM (VALUES ( CAST( GETDATE() AS DATE) ) ,( CAST( DATEADD( DAY, -1, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -2, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -3, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -4, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -5, GETDATE() ) AS DATE) ) ,( CAST( DATEADD( DAY, -6, GETDATE() ) AS DATE) ) --,( CAST( DATEADD( DAY, -7, GETDATE() ) AS DATE) ) ) AS DS(D) OUTER APPLY ( SELECT COUNT([FILTERED_MINUTES]) AS N FROM #FACT_DELAYS F WHERE F.START_DATE <= DS.D AND (F.END_DATE IS NULL OR F.END_DATE >= DS.D) )FN ORDER BY DS.D DESC DROP TABLE IF EXISTS #FACT_DELAYSMuch Appreciated!
What's the meaning of "delay"? Could you supply the result that related on sample data?
Hi,
Right now I can only to give me a result of how many delays are open as of when i run the query. I am not sure how to tell me how many delays are open on each of the last 7 days.
Thanks
