[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4195
  • Last Modified:

SQL query to compare the previous row and current values

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
LougaLo
Asked:
LougaLo
  • 4
  • 3
  • 2
1 Solution
 
deightonCommented:
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
 
deightonCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:

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
 
LougaLoAuthor Commented:
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
 
LougaLoAuthor Commented:
RajkumarGS:
thank you it works fine.
0
 
deightonCommented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now