Hi all,
I have a scenario wherein i have following 2 tables with the below provided data:
Create table #Test
(
TRTime datetime,
ID Int,
Charge int
)
insert into #Test values('2021-06-30',1,5)
insert into #Test values('2021-07-15',1,5)
insert into #Test values('2021-07-15',1,5)
insert into #Test values('2021-07-15',1,7)
insert into #Test values('2021-07-31',1,6)
insert into #Test values('2021-07-15',2,8)
insert into #Test values('2021-07-31',2,6)
create table #Status
(
Id int,
Status varchar(5),
RowStartDt datetime,
RowEndDt datetime,
RowIsCurrent int
)
insert into #Status values(1,'F','2021-01-01','2021-07-14',0)
insert into #Status values(1,'P','2021-07-14','2021-07-15',0)
insert into #Status values(1,'Z','2021-07-15','2021-07-15',0)
insert into #Status values(1,'Z','2021-07-15','2021-07-16',0)
insert into #Status values(1,'A','2021-07-16',Null,1)
insert into #Status values(2,'Z','2021-01-01',Null,1)
insert into #Status values(1,'Z','2021-07-15','2021-07-15',0)
insert into #Status values(1,'X','2021-07-15','2021-07-16',0)
And this is my expected output:
I followed this thread which i think had similar scenario
https://docs.microsoft.com/en-us/answers/questions/525262/handling-missing-pk-fk-relations-in-historical-dat.html
and tried reproducing the expected output with below query:
Select * from
(
Select *,ROW_NUMBER() OVER(PARTITION BY id,TRTime,Charge ORDER BY diff) rr from
(Select t.*,s.Status,s.RowStartDt,ABS(DATEDIFF(day,t.TRTime,s.RowStartDt)) diff
from #Test t
Left join #Status s on t.id=s.id and t.TRTime BETWEEN RowStartDt AND ISNULL(RowEndDt,'9999-12-31')) a) b
where rr=1 and status='z'
But with this query, i am only getting one row for exact duplicate TRTime,ID and Charge.
I want a query wherein even if TRTime,ID and Charge are exact duplicates they should be retained in the output.