Solved

SQL query to compare the previous row and current values

Posted on 2011-02-28
9
3,124 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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