Set date column value from another column based on indicator (window function)-CodePudding

EDIT: I am using SQL Server (Managed Instance)

In my table I am attempting to: Set PrevMktOpenDate = CalendarDate where MarketOpen was last 1. I hope this is better illustrated in my example excerpts below.

Table:
marketCalendar

Columns:
Day(int)
CalendarDate(date)
MarketOpen(bit)
PrevMktOpenDate(date)

Current:

364 2022-12-30  1   2022-12-29
365 2022-12-31  0   2022-12-30
1   2023-01-01  0   2022-12-31
2   2023-01-02  0   2023-01-01
3   2023-01-03  1   2022-12-02
4   2023-01-04  1   2023-01-03

Requested:

364 2022-12-30  1   2022-12-29
365 2022-12-31  0   2022-12-30
1   2023-01-01  0   2022-12-30
2   2023-01-02  0   2023-01-30
3   2023-01-03  1   2022-12-30
4   2023-01-04  1   2023-01-03

I have tried to write up a rank function with sorting by date but could not get it to work as intended.

CodePudding user response：

Something like this perhaps (gave 3 alts):

if object_id('tempdb..#t_open') is not null
drop table #t_open
create table #t_open(
    Day int
,   CalendarDate date
,   MarketOpen bit
,   PrevMktOpenDate date
)

insert into #t_open (
    Day, CalendarDate, MarketOpen, PrevMktOpenDate
)
select  *
from
(
    VALUES  (363,'2022-12-29',1,'2022-12-28')
    ,   (364,'2022-12-30',1,'2022-12-29')
    ,   (365,'2022-12-31',0,'2022-12-30')
    ,   (1  ,'2023-01-01',0,'2022-12-31')
    ,   (2  ,'2023-01-02',0,'2023-01-01')
    ,   (3  ,'2023-01-03',1,'2022-12-02')
    ,   (4  ,'2023-01-04',1,'2023-01-03')
) t (day, cd, mo, pm)

-- Alternative one
update  t
set PrevMktOpenDate = prev
from (
    SELECT  *
    ,   MAX(CASE WHEN MarketOpen = 1 THEN CalendarDate END) OVER(ORDER BY CalendarDate ROWS BETWEEN unbounded preceding and 1 preceding) AS prev
    FROM    #t_open o
) t

-- Alternative two
update  t
set PrevMktOpenDate = prev
from #t_open t
CROSS APPLY (
    SELECT  TOP 1 o.Calendardate AS prev
    FROM    #t_open o
    WHERE o.CalendarDate < t.CalendarDate
    AND o.MarketOpen = 1
    oRDER BY o.CalendarDate DESC
) prev

-- ALternative three
update  t
set PrevMktOpenDate = (SELECT TOP 1 o.Calendardate AS prev
    FROM    #t_open o
    WHERE o.CalendarDate < t.CalendarDate
    AND o.MarketOpen = 1
    oRDER BY o.CalendarDate DESC)
from #t_open t


select  *
from    #t_open

CodePudding user response：

Assuming you are on Azure SQL Database, you can use a the window function LAST_VALUE with a CASE expression and IGNORE NULLS:

SELECT Col1,
       Col2,
       Col3,
       ISNULL(LAST_VALUE(CASE Col3 WHEN 1 THEN Col2 END) IGNORE NULLS OVER (ORDER BY Col2 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),Col4) AS Col4
FROM (VALUES(364,CONVERT(date,'2022-12-30'),1,CONVERT(date,'2022-12-29')),
            (365,CONVERT(date,'2022-12-31'),0,CONVERT(date,'2022-12-30')),
            (1  ,CONVERT(date,'2023-01-01'),0,CONVERT(date,'2022-12-31')),
            (2  ,CONVERT(date,'2023-01-02'),0,CONVERT(date,'2023-01-01')),
            (3  ,CONVERT(date,'2023-01-03'),1,CONVERT(date,'2022-12-02')),
            (4  ,CONVERT(date,'2023-01-04'),1,CONVERT(date,'2023-01-03')))V(Col1,Col2,Col3,Col4);

db<>fiddle