TSQL - Matching data from row 1 to row 2 etc 2

[Cobby1812]

Hi there,
In my query results below I have returned three rows of data - what I want to do is say

If EndDate (Row 2) = SetDate (Row 3) then 1 else 0

I cant for the life of me work out how to do it. Any help would be gratefully received.




SetDate EffectiveDate End Date
2017-12-20 00:00:00.000 2017-12-20 00:00:00.000 2018-01-17 00:00:00.000
2017-12-20 00:00:00.000 2018-01-17 00:00:00.000 2018-02-01 00:00:00.000
2018-02-01 00:00:00.000 2018-02-01 00:00:00.000 NULL

 

[Olaf Doschke]

The question is where do you want this, as a fourth column? In which row?

You have LAG() and LEAD() to peek into values of the next or look back into values of the previous row.

declare @data as table (setdate datetime, effectivedate datetime, enddate datetime)

set dateformat ymd;
insert into @data values ('2017-12-20 00:00:00','2017-12-20 00:00:00','2018-01-17 00:00:00')
                        ,('2017-12-20 00:00:00','2018-01-17 00:00:00','2018-02-01 00:00:00')
                        ,('2018-02-01 00:00:00','2018-02-01 00:00:00',NULL);

select *, Case When Enddate=LEAD(SetDate) over (order by SetDate,EffectiveDate) Then 1 Else 0 End as "EndDate is next SetDate" from @data
select *, Case When SetDate=LAG(Enddate)  over (order by SetDate,EffectiveDate) Then 1 Else 0 End as "Previous EndDate is SetDate" from @data

results:

To be able to use LAG or LEAD an OVER clause has to specify an order (there is no sense in asking for previous or next without any sort order, as you can see I assumed main order by set date, secondary order by effective date, you might adjust that ordering. And don't assume the order in which records come out without any ORDER BY clause or OVER clause is natural, SQL is set operations, means analyizing records = set elements in any order unless a specific order is specified.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Cobby1812]

Hi Olaf - will give it a whirl - your a star many thanks

 

[Andrzejek]

Cobby1812,
If Olaf is "your [...] star", give him one (star)


---- Andy

There is a great need for a sarcasm font.