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.

Here's another option... All 3 options work based on your provided test data. Test all 3 and see which performs best in your particular environment.

WITH NonCompare AS (
	SELECT 
		t.ID,
		t.Dt
	FROM @tab1 t
	WHERE t.Compare = 0
)
	SELECT
		nc.ID,
		t.Dt,
		t.Compare,
		CASE WHEN nc.Dt < t.Dt THEN 1 ELSE 0 END AS Result
	FROM
		NonCompare nc
		LEFT JOIN @tab1 t 
			ON nc.ID = t.ID;

HTH,

Jason

>> I have a scenario to flag devious records [sic] based on each foo_id [sic] columns. For each foo_id [sic] there would be few, I have a column called "compare" [sic], We have to compare  = 1 records with compare  = 0 records [sic]. If foobar_date is lesser or equal to comparing foobar_date, then show 0, else 1 <<

Why do you post picture? Did you know that compare is a verb? 
Wow! You got so many fundamental concepts wrong! Rows are nothing like records. A column named stupid_non_relational_flg is a verb, not a noun. We do not use 1960's assembly language flags in SQL. Did you know that a table has to have key by definition? 

>> We always only one compare = 0 records [sic] in my table, so all compare = 1 columns will compare with only one row per ID [sic] 

>> My tables look like <<

Please read any whatsoever on RDBMS and data modeling. You did the wrong thing in the wrong way. Here is a repair job: 

CREATE TABLE Foobar 
(foo_id CHAR(3) NOT NULL
  CHECK (foo_id LIKE '[0-9][0-9][0-9]'))
 PRIMARY KEY,
 foobar_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, 
stupid_non_relational_flg CHAR(1) NOT NULL'
   CHECK(stupid_non_relational_flg IN ('0','1')));

INSERT INTO Foobar 
VALUES 
('101', '2015-07-01', '0'),
('101', '2015-07-02', '1'),
('101', '2015-07-03', '1'),
('101', '2015-07-01', '1'),
('101', '2015-06-30', '1'),

('102', '2015-07-01', '0'),
('102', '2015-07-02', '1'),
('102', '2015-07-01', '1'); 

1.) In the above scenario for foo_id = '101', we have 5 records [sic], first [sic: earlest] has stupid_non_relational_flg value = 0, which mean all other 4 records [sic] need to stupid_non_relational_flg with this oi only

2.) If stupid_non_relational_flg = 1 record's foobar_date is less or equal to stupid_non_relational_flg 0's foobar_date, then show 0 in next [sic: columns have no ordering] column. 

3.) If stupid_non_relational_flg 1 record's foobar_date is greater than stupid_non_relational_flg 0's foobar_date, then show 1 in next column

This is a job for LEAD() and LAG() functions. This ought to be in a VIEW and not materialized in a table. 

If you are using SS 2012 o greater then you could also try:

WITH R AS (
SELECT
    ID,
    Dt,
    Compare,
    MAX(CASE WHEN Compare = 1 THEN NULL ELSE Dt END) OVER(
    PARTITION BY ID 
    ORDER BY Compare, Dt 
    ROWS UNBOUNDED PRECEDING
    ) AS comp0_dt
FROM @tab1
)
SELECT
    R.ID,
    R.Dt,
    R.Compare,
    CASE WHEN R.Dt <= R.comp0_dt THEN 0 ELSE 1 END AS Result
FROM
    R
ORDER BY
    ID,
    Compare,
    Dt;
GO

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  Datediff(s,dt,Cast(SUBSTRING(
      MAX( CAST(id AS BINARY(4)) + CAST(Cast(case when Compare=0 then dt else null End as datetime) AS BINARY(16))) OVER( ORDER BY id,Compare  ROWS UNBOUNDED PRECEDING ),
     5,16) as datetime))>=0  then 0 else 1 End as Result
 from @tab1

 /*
 Id	Dt	Compare	Result
101	2015-07-01	0	0
101	2015-07-02	1	1
101	2015-07-03	1	1
101	2015-07-01	1	0
101	2015-06-30	1	0
102	2015-07-01	0	0
102	2015-07-02	1	1
102	2015-07-01	1	0

 */

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)

;WITH CTE AS (
	SELECT * FROM @tab1 WHERE Compare = 0
)

SELECT t1.*, CASE WHEN t1.Dt <= t2.Dt THEN 0 ELSE 1 END AS Result
FROM @tab1 AS t1
INNER JOIN CTE AS t2 ON t2.ID = t1.ID

Try this

SELECT

[@tab1].*, IIF( [@tab1].Dt > [Compare_0].Dt , 1,0) Result

from @tab1

LEFT OUTER JOIN @tab1 [Compare_0] ON [Compare_0].Compare =0 AND [Compare_0].ID = [@tab1].ID &#

Hi Fazlu,

See the following code below:

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);

with CTE ([Row],Id,DT,Compare)
as 
(
	select 
		row_number() over (partition by Id order by id) ,
		Id,
		Dt,
		Compare  
	from @tab1
)

Select
	ID,
	Dt,
	Compare,
	iif(dt<=(Select dt from CTE c where c.Id=c1.Id and c.[Row]=1),0,1) as Result 
from CTE c1Result

Result

Best Regards