Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query to compare the previous row and current values

Posted on 2011-02-28
9
Medium Priority
?
3,578 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

688 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