SQL Query help

Transact-SQL
1

Hello SQL Expert,

Below are my sql query and the result set:

SELECT YR_MTH, Total_Part
FROM Part_Tbl

result set:
01

The result I need to achieve:
02

Basically I want to find the different between record # 2 to Record # 1, then Records # 3 to # 2, #4 to #3 and so on.

How do I achieve this? Anyone?

Thanks all the SQL Expert

2

If you are on SQL Server 2012 or above, you can use LAG function to get the previous record value.

declare @Part_TBL TABLE
(YR_MTH INT NOT NULL
, Total_Part INT NOT NULL
);

INSERT INTO @Part_TBL(YR_MTH,Total_Part)
VALUES(201605,3)
,(201606,5)
,(201607,10)


SELECT
   YR_MTH
   ,Total_Part
   ,ISNULL(
      Total_Part - LAG(Total_Part,1,NULL) OVER(ORDER BY YR_MTH ASC)
      ,0) AS Different
   ,LAG(Total_Part,1,NULL) OVER(ORDER BY YR_MTH ASC) AS Total_Part_Prev
FROM
   @Part_TBL AS S

the output of it:

|YR_MTH|Total_Part|Different|Total_Part_Prev|
|201605|3|0|null|
|201606|5|2|3|
|201607|10|5|5|
1 Like
3

It worked, you are sql genius stepson!!! Thank you.

1 Like
4

You're welcome!

1 Like