Solved

SQL query to compare the previous row and current values

Posted on 2011-02-28
9
2,837 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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