How to calculate Difference

Question

Post reply

How to calculate Difference

sm_iransoftware

Ten Centuries

Points: 1228
More actions
February 20, 2015 at 11:59 pm

#294798

I saved Table size in a log table .
Then now I have These records :
InfoDate ---- SizeMB ---- TableName
D1 ----------- 100 --------T1
D1 ----------- 250 --------T2
D1 ----------- 70 ---------T3
D1 ----------- 300 --------T4
D2 ----------- 110 --------T1
D2 ----------- 270 --------T2
D2 ----------- 100 --------T3
D2 ----------- 301 --------T4
---------------------------------------------
---------------------------------------------
I Need These Result :
InfoDate ---- SizeMB ---- TableName
D2-D1 ------- 10 --------T1
D2-D1 ------- 20 --------T2
D2-D1 ------- 30 --------T3
D2-D1 ------- 1 ---------T4
What Do I Do ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick LAG window function solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_LOG') IS NOT NULL DROP TABLE dbo.TBL_LOG;

CREATE TABLE dbo.TBL_LOG

(

InfoDate DATE NOT NULL

,SizeMB INT NOT NULL

,TableName VARCHAR(15) NOT NULL

);

INSERT INTO dbo.TBL_LOG (InfoDate,SizeMB,TableName)

VALUES

('2015-01-10',100,'T1')

,('2015-01-10',250,'T2')

,('2015-01-10', 70,'T3')

,('2015-01-10',300,'T4')

,('2015-01-11',110,'T1')

,('2015-01-11',270,'T2')

,('2015-01-11',100,'T3')

,('2015-01-11',301,'T4')

,('2015-01-12',115,'T1')

,('2015-01-12',290,'T2')

,('2015-01-12',110,'T3')

,('2015-01-12',371,'T4')

;

;WITH BASE_DATA AS

(

SELECT

TL.InfoDate

,LAG(TL.InfoDate) OVER

(

PARTITION BY TL.TableName

ORDER BY TL.InfoDate ASC

) AS PREV_DATE

,TL.SizeMB

,TL.TableName

,TL.SizeMB - LAG(TL.SizeMB,1) OVER

(

PARTITION BY TL.TableName

ORDER BY TL.InfoDate ASC

) AS SIZE_DIFF

FROM dbo.TBL_LOG TL

)

SELECT

*

FROM BASE_DATA BD

WHERE BD.SIZE_DIFF IS NOT NULL;

Results

InfoDate PREV_DATE SizeMB TableName SIZE_DIFF

---------- ---------- ----------- --------------- -----------

2015-01-11 2015-01-10 110 T1 10

2015-01-12 2015-01-11 115 T1 5

2015-01-11 2015-01-10 270 T2 20

2015-01-12 2015-01-11 290 T2 20

2015-01-11 2015-01-10 100 T3 30

2015-01-12 2015-01-11 110 T3 10

2015-01-11 2015-01-10 301 T4 1

2015-01-12 2015-01-11 371 T4 70

sm_iransoftware Ten Centuries Points: 1228 More actions · Answer 2

sm_iransoftware

Ten Centuries

Points: 1228

February 21, 2015 at 3:39 am

#1779108

Ok

Thank you :w00t:

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

sm_iransoftware (2/20/2015)
I saved Table size in a log table .
Then now I have These records :
InfoDate ---- SizeMB ---- TableName
D1 ----------- 100 --------T1
D1 ----------- 250 --------T2
D1 ----------- 70 ---------T3
D1 ----------- 300 --------T4
D2 ----------- 110 --------T1
D2 ----------- 270 --------T2
D2 ----------- 100 --------T3
D2 ----------- 301 --------T4
---------------------------------------------
---------------------------------------------
I Need These Result :
InfoDate ---- SizeMB ---- TableName
D2-D1 ------- 10 --------T1
D2-D1 ------- 20 --------T2
D2-D1 ------- 30 --------T3
D2-D1 ------- 1 ---------T4
What Do I Do ?

As a bit of a sidebar, please see the first link under "Helpful Links" in my signature line below for future posts. Eirikur is quite generous in his conversion of posts to "readily consumable" data so that he can provide actual working code to demonstrate a solution but a lot of people won't be so generous. If you take the time to post the data in a "readily consumable" format as outlined in the article I mention above, more people will likely jump in to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sm_iransoftware Ten Centuries Points: 1228 More actions · Answer 4

Thank you jeff

I will read it and

I will act according to instructions.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Be very careful with LAG here. If you for some reason don't have data for the previous measure period, you will get the measure period before that.

That's why why need the INTERVAL implementation for windowed functions.

N 56°04'39.16"
E 12°55'05.25"

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

SwePeso (3/1/2015)
Be very careful with LAG here. If you for some reason don't have data for the previous measure period, you will get the measure period before that.
That's why why need the INTERVAL implementation for windowed functions.

Quick thought, even if measure points where missing which is normal if only DELTA <> 0 are recorded, obviously the only thing missing would be the length of the periods in which the last recorded value would be carried forward as a DELTA 0.

😎

TomThomson SSC Guru Points: 104773 More actions · Answer 7

SwePeso (3/1/2015)
Be very careful with LAG here. If you for some reason don't have data for the previous measure period, you will get the measure period before that.
That's why why need the INTERVAL implementation for windowed functions.

Given that the output includes both the start date and the end date for the interval reported, that strikes me as singularly irrelevant. Indeed there's nothing to tell us that all the measuring points (labels Tn) use the same length periods at all times, except that this just happens to be true in the tiny sample of data we are shown.

Tom