Comparing two data rows - 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

General SQL Server Forums

New to SQL Server Programming

Comparing two data rows

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2013-09-25 : 06:02:38

Hi,

create table #prints(id int IDENTITY(1,1) NOT NULL,
Printermarkersupplyid varchar(36),PrinterID varchar(10),Description varchar(10),SupplyLevel int,ModifiedDate datetime)

insert into #prints(Printermarkersupplyid,PrinterID,Description,SupplyLevel,ModifiedDate)
select newid(),'P1','D1',100,'2013-08-1 03:28:38.203'
union all
select newid(),'P1','D1',0,'2013-8-2 03:28:38.203'
union all
select newid(),'P1','D1',100,'2013-8-3 03:28:38.203'
union all
select newid(),'P1','D1',80,'2013-8-4 03:28:38.203'
union all
select newid(),'P1','D1',0,'2013-8-5 03:28:38.203'
union all
select newid(),'P1','D1',100,'2013-8-6 03:28:38.203'
union all
select newid(),'P1','D2',80,'2013-8-7 03:28:38.203'
union all
select newid(),'P1','D2',0,'2013-8-8 03:28:38.203'
union all
select newid(),'P1','D2',0,'2013-8-9 03:28:38.203'
union all
select newid(),'P1','D2',80,'2013-8-10 03:28:38.203'
union all
select newid(),'P1','D2',70,'2013-8-11 03:28:38.203'

my requirement is to get the difference between adjacent rows.ie difference between 2nd and 3rd or 6th and 7th but not 6th and 8th.
if difference between 2nd and 3rd is less than zero and 3rd modified date > 2nd modified date,then i should get count as 1 against 3rd row.

Thanks,
Binto

Thanks & Regards
Binto Thomas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-25 : 07:44:26

something like


SELECT t.*,
CASE WHEN t.SupplyLevel -t1.SupplyLevel < 0 AND t.ModifiedDate > t1.ModifiedDate THEN 1 ELSE 0 END AS Cnt
FROM Table t
OUTER APPLY (SELECT TOP 1 SupplyLevel,ModifiedDate
             FROM Table
             WHERE PrinterID = t.PrinterID
             AND  Description = t.Description
             AND ModifiedDate < t.ModifiedDate
             ORDER BY ModifiedDate DESC)t1

see scenario 2 here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-25 : 08:36:40

2012 ?

select *,
CASE WHEN SupplyLevel - lag(SupplyLevel)over(partition by PrinterID,Description order by ModifiedDate) < 0
AND ModifiedDate > lag(ModifiedDate)over(partition by PrinterID,Description order by ModifiedDate) THEN 1 ELSE 0 END AS Cnt
from #prints;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources