I have a SQL Table as below:

Now I need to write a query to show the status of every day. My Output should be as below:

• Device 1 - 1/10/2022 - ACTIVE
• Device 1 - 2/10/2022 - ACTIVE
• Device 1 - 3/10/2022 - ACTIVE
• Device 1 - 4/10/2022 - ACTIVE
• Device 1 - 5/10/2022 - INACTIVE

I have tried few queries which does not giving me correct result. So, it would be appreciate if I can get some help on this. Thanks in Advance.

CodePudding user response：

1. Create a table with every day as a record.
2. Take the next Device Status date from YourStatusTable with a self join
3. Join your status table to the date seed

with YourStatusTable as ( 
    select 'Device 1' [Device ID],  'ACTIVE' [Status],  cast('01-Oct-2022' as date) [Created Date]
    union all select 'Device 1' [Device ID], 'INACTIVE' [Status], '05-Oct-2022' 
),

seed as ( 
    select null as n 
    union all select null
),

dates as (
    select dateadd(day,row_number() OVER ( ORDER BY a.n )-1, '-Sep-2022') date_value
    from seed a,
    seed b,
    seed c,
    seed d,
    seed e,
    seed f
),

status_boundaries as ( 
    select 
        a.[Device ID], 
        a.[Status],
        a.[Created Date],
        b.[Created Date] [next_status_date]
    from YourStatusTable a
    left join YourStatusTable b on a.[Device ID] = b.[Device ID] and a.[Created Date] < b.[Created Date]
)

select * 
from dates
inner join status_boundaries on  date_value >= [Created Date] 
where date_value < next_status_date or next_status_date is null

CodePudding user response：

You may try a recursive CTE as the following:

WITH CTE AS
  (
    SELECT DEVICE_ID, STATUS, Created_Date
    FROM table_name
    UNION ALL
    SELECT C.DEVICE_ID, C.STATUS, DATEADD(DAY, 1, C.Created_Date)
    FROM CTE C  
    WHERE  DATEADD(DAY, 1, C.Created_Date) NOT IN (SELECT Created_Date FROM table_name T WHERE T.DEVICE_ID=C.DEVICE_ID AND T.STATUS='INACTIVE')
           AND DATEADD(DAY, 1, C.Created_Date)<=GETDATE()
           AND STATUS='ACTIVE'
  )
SELECT DEVICE_ID, STATUS, Created_Date
FROM CTE
ORDER BY DEVICE_ID, Created_Date

See a demo with extended data sample.