Solved

SQL query to compare the previous row and current values

Posted on 2011-02-28
9
3,299 Views
Last Modified: 2012-05-11
I need some help.
I need to compare the value of previous row  with the value of current row in a table in SQL Server 2008.
Table name is tbHeight with four (4) colunms.
measurements of height is done once a month. the height of current month should be higher than the height from previous month.
if value of current month (row) is lower than value of previous month then flag and listed in the output.
see attached for sample data and output.
thanks in advance.

output
-----------------------------------------------
pid      MDATE      MHEIGHT
1      7-Aug-10      60
4      23-May-10      55.9
5      24-Mar-10      56.7
 TBHEIGHT1.xlsx
0
Comment
Question by:LougaLo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 34995972
for a table called AHEIGHT (in my example, please change to your table name)

select B.*FROM aheight B where B.MHeight < (SELECT TOP 1 A.MHeight FROM aHEIGHT A
      WHERE A.MDATE < B.MDATE ORDER BY A.MDATE DESC) AND EXISTS(SELECT NULL FROM aHEIGHT C WHERE B.MDATE < C.MDATE)
0
 
LVL 18

Expert Comment

by:deighton
ID: 34995983
formatting vanished there, here is repost, code the same though

select B.* FROM aheight B 
                where B.MHeight < 
                     (SELECT TOP 1 A.MHeight FROM aHEIGHT A 
	                                                       WHERE 
                                                                           A.MDATE < B.MDATE ORDER BY A.MDATE DESC)
                                                               AND EXISTS(SELECT NULL FROM aHEIGHT C 
                                                                           WHERE B.MDATE < C.MDATE)

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34995992
Try this query. #Table is table name that I give. Correct it your actual table name.
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT a.* FROM CTE a
	LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
	WHERE b.MHEIGHT > a.MHEIGHT

Open in new window


This is how I tested it
CREATE TABLE #table
(
	pid      INT,
	MDATE    DATETIME,
	MHEIGHT	 DECIMAL(18,2)
)

INSERT INTO #table
SELECT 1,	'10-Mar-10',	49.2 UNION ALL
SELECT 1,	'11-Apr-10',	52 UNION ALL
SELECT 1,	'9-May-10',	55 UNION ALL
SELECT 1,	'13-Jul-10',	62.5 UNION ALL
SELECT 1,	'7-Aug-10',	60 UNION ALL
SELECT 1,	'8-Sep-10',	62.9 UNION ALL
SELECT 1,	'7-Oct-10',	63.5 UNION ALL
SELECT 2,	'14-Mar-10',	53.1 UNION ALL
SELECT 2,	'11-Apr-10',	55.2 UNION ALL
SELECT 2,	'29-May-10',	61.3 UNION ALL
SELECT 2,	'12-Jun-10',	62.1 UNION ALL
SELECT 2,	'13-Jul-10',	63.4 UNION ALL
SELECT 2,	'15-Aug-10',	63.6 UNION ALL
SELECT 2,	'30-Sep-10',	65.5 UNION ALL
SELECT 2,	'16-Oct-10',	66.9 UNION ALL
SELECT 3,	'14-Mar-10',	57.3 UNION ALL
SELECT 3,	'18-Apr-10',	57.6 UNION ALL
SELECT 3,	'13-May-10',	60.3 UNION ALL
SELECT 3,	'14-Jul-10',	64.5 UNION ALL
SELECT 3,	'16-Aug-10',	65.3 UNION ALL
SELECT 3,	'14-Sep-10',	65.4 UNION ALL
SELECT 4,	'22-Mar-10',	53.7 UNION ALL
SELECT 4,	'21-Apr-10',	57.5 UNION ALL
SELECT 4,	'23-May-10',	55.9 UNION ALL
SELECT 4,	'20-Jun-10',	58.7 UNION ALL
SELECT 4,	'20-Jul-10',	62.8 UNION ALL
SELECT 4,	'19-Aug-10',	63.7 UNION ALL
SELECT 4,	'20-Sep-10',	66.2 UNION ALL
SELECT 4,	'21-Oct-10',	67.2 UNION ALL
SELECT 5,	'21-Apr-10',	69.2 UNION ALL
SELECT 5,	'19-May-10',	58.6 UNION ALL
SELECT 5,	'24-Mar-10',	56.7


-- main query
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT a.* FROM CTE a
	LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
	WHERE b.MHEIGHT > a.MHEIGHT
	
DROP TABLE #table

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34995998
Or it could be
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT x.*, CASE WHEN y.pid IS NOT NULL THEN 'FLAG' ELSE NULL END FLAG FROM CTE x
LEFT JOIN
(
SELECT a.*
FROM CTE a
	LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
	WHERE b.MHEIGHT > a.MHEIGHT
) y ON x.pid = y.pid AND x.MDATE = y.MDATE

Open in new window


Replacing #table with your tablename
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34996009
Corrected
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT x.*, CASE WHEN y.pid IS NOT NULL THEN 'FLAG' ELSE '' END FLAG FROM CTE x
LEFT JOIN
(
SELECT a.*
FROM CTE a
	LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
	WHERE b.MHEIGHT > a.MHEIGHT
) y ON x.pid = y.pid AND x.MDATE = y.MDATE

Open in new window


Change #table to your tablename

Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 34996053

Query returns only those records
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT a.* FROM CTE a
	LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
	WHERE b.MHEIGHT > a.MHEIGHT

Open in new window

     

Query that returns all records and show flag for those records      
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT x.*, CASE WHEN y.pid IS NOT NULL THEN 'FLAG' ELSE '' END FLAG FROM CTE x
LEFT JOIN
(
SELECT a.*
FROM CTE a
	LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
	WHERE b.MHEIGHT > a.MHEIGHT
) y ON x.pid = y.pid AND x.MDATE = y.MDATE

Open in new window


Replace #table with your table name
0
 

Author Comment

by:LougaLo
ID: 34996239
deighton:
i ran your query here is the output i got. i need to show (flag) only higher curent values. thanks

PId      mdate      mheight
1      2010-05-09      55
1      2010-08-07      60
1      2010-09-08      62.9
1      2010-10-07      63.5
1      2010-04-11      52
2      2010-04-11      55.2
2      2010-09-30      65.5
4      2010-04-21      57.5
4      2010-05-23      55.9
4      2010-06-20      58.7
4      2010-07-20      62.8
4      2010-08-19      63.7
5      2010-05-19      58.6
0
 

Author Comment

by:LougaLo
ID: 34996260
RajkumarGS:
thank you it works fine.
0
 
LVL 18

Expert Comment

by:deighton
ID: 34996996
in that case, maybe

select B.* FROM aheight B 
                where B.MHeight < 
                     (SELECT TOP 1 A.MHeight FROM aHEIGHT A 
	                                                       WHERE 
                                                                          A.pid = b.pid and  A.MDATE < B.MDATE ORDER BY A.MDATE DESC)
                                                               AND EXISTS(SELECT NULL FROM aHEIGHT C 
                                                                           WHERE b.pid = c.pid and B.MDATE < C.MDATE)

Open in new window

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question