Hi,

I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1

We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID

My tables look like

Declare  @tab1 table (ID Varchar(3), Dt Date, Compare Int)

Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)

Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)

select * from @tab1

1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only

2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column 

3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column 

My expected result set should be like

Regards,

Fazlu

Hi,

I would do something like this.

Declare  @tab1 table (ID Varchar(3), Dt Date, Compare Int)

Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)

Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)

select *
,case when exists(
	select *
	from @tab1 as b
	where a.ID = b.ID
	and b.Compare = 0
	and a.Dt <= b.Dt) then 0 else 1 end
from @tab1 as a

Hi Fazlu,

What if there're more than one row with Compare=0 for each ID? Are the 3 rules applied to the minimum Dt of Compare 0 as well? If so please see below sample.

Declare  @tab1 table (ID Varchar(3), Dt Date, Compare Int)

Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)

Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)



SELECT ID,DT,Compare,CASE WHEN Compare = 1 AND DT>MDT THEN 1 ELSE 0 END Result FROM @tab1 T 
CROSS APPLY
(
SELECT MIN(DT) MDT FROM @tab1 WHERE t.id=id and Compare=0
) C

If you have any question, feel free to let me know.