February 20, 2015 at 11:59 pm
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 ?
February 21, 2015 at 2:54 am
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
February 21, 2015 at 3:39 am
Ok
Thank you :w00t:
February 21, 2015 at 9:20 am
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
Change is inevitable... Change for the better is not.
February 23, 2015 at 3:52 am
Thank you jeff
I will read it and
I will act according to instructions.
March 1, 2015 at 2:44 am
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"
March 1, 2015 at 3:10 am
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.
😎
March 1, 2015 at 3:54 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply