Hello All,
I have a scenario wherein we have 2 tables with below format


Since there is no PK-FK relationship between the 2 tables, finding the state as of that period becomes tedious.
My requirement is as below:
Need to charge data from table A for those IDs whose Status=Z during that time period.
So 2nd row ( with Id=1 and Time=31st July ) should be filtered out as during that time, the status of that ID=A.
So what is the best SQL method to achieve this scenario?
Sample 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-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-16',0)
insert into #Status values(1,'A','2021-07-16',Null,1)
insert into #Status values(2,'Z','2021-01-01',Null,1)
My Query :
Select * from #Test T
Left join #Status s on t.id=s.id and (T.TRTime should match to the nearest time for that ID in RowStartDtColumn)
where Status='Z'
Needed output :

So we need to filter out records from Test table when for the corresponding time period the status of that ID is 'Z' in Status table.
