Compare the Records in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare the Records in Query

Author

Topic

irfanshirur
Starting Member

21 Posts

Posted - 2010-01-04 : 07:53:53

Hello

Is it possible in SQL to compare the column values between two rows.

Example

I have Table(Table Name : Member) having the Values -

ID Name FromDate ToDate
1 A 7/1/2008 4/30/2009
2 B 5/1/2009 12/31/2100

Now i want a output like -

I want compare the first Record To Date with 2nd record from date if the Differance is >30 i want as some flag (Y or N).

output -

ID Name FromDate ToDate Flag
1 A 7/1/2008 3/30/2009 Y
2 B 5/1/2009 12/31/2100 N

is it possible to do at query level or i need to go for any other approch.

Please can any one hep me out to solve this problem.

Thanks
Regards
Mohammed Irfan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 08:01:16

how many rows do you have in the table ?

If you have more than 2 rows in the table, which 2 rows do you want to compare ? How to identify these 2 rows ?

KH
[spoiler]Time is always against us[/spoiler]

irfanshirur
Starting Member

21 Posts

Posted - 2010-01-04 : 08:28:27

[quote]Originally posted by khtan

how many rows do you have in the table ?

If you have more than 2 rows in the table, which 2 rows do you want to compare ? How to identify these 2 rows ?

KH
[spoiler]Time is always against us[/spoiler]

Hi ..

Thanks for your valuable time.

I may get more than two records, i will do order by fromdate and toDate so that i will get it in the order.

First Record with second one , seciond one with third like this so fourth....

Thanks
Regards
Mohammed Irfan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 08:32:16

which column determine the order of the records ? the ID ?

KH
[spoiler]Time is always against us[/spoiler]

irfanshirur
Starting Member

21 Posts

Posted - 2010-01-04 : 08:45:30

fromDate and Todate column

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-04 : 08:54:53

[code]
; with data (ID, Name, FromDate, ToDate, row_no)
as
(
select ID, Name, FromDate, ToDate, row_no = row_number() over (order by FromDate, ToDate)
from Member
)
select d1.ID, d1.Name, d1.FromDate, d1.ToDate, Flag = case when datediff(day, d1.ToDate, d2.FromDate) > 30
then 'Y'
else 'N'
end
from data d1
left join data d2 on d1.row_no = d2.row_no - 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

irfanshirur
Starting Member

21 Posts

Posted - 2010-01-04 : 09:20:46

Awesome :)

Thanks a lot...it worked fine i got the output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources