LougaLo
asked on
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
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
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)
Try this query. #Table is table name that I give. Correct it your actual table name.
This is how I tested it
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
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
Or it could be
Replacing #table with your tablename
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
Replacing #table with your tablename
Corrected
Change #table to your tablename
Raj
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
Change #table to your tablename
Raj
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
RajkumarGS:
thank you it works fine.
thank you it works fine.
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)
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)