SQL to get previous value in the range

Transact-SQL
1

Hi,

from the below table

prod runTimeStamp value
ABCD 8/2/21 12:30 AM** 101278.52**
ABCD 8/3/21 12:30 AM 101703.52
ABCD 8/7/21 12:30 AM 101703.52
ABCD 8/8/21 12:30 AM 101703.52
ABCD 8/9/21 12:30 AM 101703.52
ABCD 8/18/21 12:30 AM 108843.52
ABCD 8/23/21 12:30 AM 108843.52
ABCD 8/24/21 12:30 AM 108843.52
ABCD 8/25/21 12:30 AM 108843.52
ABCD 8/26/21 12:30 AM 108843.52
ABCD 8/27/21 12:30 AM 108843.52
ABCD 9/3/21 12:30 AM 113043.52
ABCD 9/6/21 12:30 AM 113043.52
ABCD 9/7/21 12:30 AM 113043.52
ABCD 9/22/21 12:30 AM 167590.56

I need to query only when the value changed

|prod|runTimeStamp|current value |previousvalue |
|ABCD|8/2/21 12:30 AM|101278.52|Null|
|ABCD|8/3/21 12:30 AM|101703.52|101278.52|
|ABCD|8/18/21 12:30 AM|108843.52|101703.52|
|ABCD|9/3/21 12:30 AM|113043.52|108843.52|
|ABCD|9/22/21 12:30 AM|167590.56|113043.52|

using row_number() over PARTITION BY ( Prod order by runtimestamp) , I got runtimestamp and current value. Need help in getting the previous value. Any pointer will be of great help

thanks in advance.

2 
-- First a test rig which you should have provided.
-- Dates in ISO format.
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t
(
    prod varchar(10) NOT NULL
    ,runTimeStamp datetime NOT NULL
    ,[value] decimal (16,2) NOT NULL
    ,PRIMARY KEY (prod, runTimeStamp)
);
GO
INSERT INTO #t
VALUES ('ABCD', '20210802', 101278.52)
    ,('ABCD', '20210803', 101703.52)
    ,('ABCD', '20210807', 101703.52)
    ,('ABCD', '20210808', 101703.52)
    ,('ABCD', '20210809', 101703.52)
    ,('ABCD', '20210818', 108843.52)
    ,('ABCD', '20210823', 108843.52)
    ,('ABCD', '20210824', 108843.52)
    ,('ABCD', '20210825', 108843.52)
    ,('ABCD', '20210826', 108843.52)
    ,('ABCD', '20210827', 108843.52)
    ,('ABCD', '20210903', 113043.52)
    ,('ABCD', '20210906', 113043.52)
    ,('ABCD', '20210907', 113043.52)
    ,('ABCD', '20210922', 167590.56);
GO

-- Try
WITH PrevValues
AS
(
    SELECT prod, runTimeStamp, [value]
        ,LAG([value]) OVER (PARTITION BY prod ORDER BY runTimeStamp) AS PrevValue
    FROM #t
)
SELECT prod, runTimeStamp, [value], PrevValue
FROM PrevValues
WHERE [value] <> COALESCE(PrevValue, -1);
GO
3

Sorry to say I am using SQL 2008 and LAG is not recognized function.

4 
SELECT *
FROM #t current_row
WHERE value <> ISNULL((
    SELECT TOP (1) previous_row.value
    FROM #t previous_row
    WHERE previous_row.prod = current_row.prod AND
        previous_row.runTimeStamp < current_row.runTimeStamp
    ORDER BY previous_row.runTimeStamp DESC
    ), -9999)
ORDER BY prod, runTimeStamp
5

Hi Scott,

It is giving only current value, I need to get corresponding previous value from which it was changed.

|prod|runTimeStamp|current value |previousvalue |
|ABCD|8/2/21 12:30 AM|101278.52|Null|
|ABCD|8/3/21 12:30 AM|101703.52|101278.52|
|ABCD|8/18/21 12:30 AM|108843.52|101703.52|
|ABCD|9/3/21 12:30 AM|113043.52|108843.52|
|ABCD|9/22/21 12:30 AM|167590.56|113043.52|

thanks

6

hi

i got this .. this is part of the work . now i have to get what you want

; with cte as 
(
select ROW_NUMBER() over(partition by value order by runTimeStamp ) as rn , * from #t 
)
select 'SQL Output', * from cte where rn = 1

image
image517×608 9.43 KB

7 
; with cte as 
(
select ROW_NUMBER() over(partition by value order by runTimeStamp ) as rn , * from #t 
) , cte_rn_1 as 
(
select  * from cte where rn = 1 
) , cte_rn_now as 
(
select ROW_NUMBER() over (order by runTimeStamp) as rn_1 , * from cte_rn_1 
)
select * from cte_rn_now a join cte_rn_now b on a.rn_1+1= b.rn_1

image
image744×133 3.25 KB

8

thanks Harishagg for the logic. I was able to implement similar row_cnt and got my results