Calculating data based on historical values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculating data based on historical values

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-07-06 : 16:17:53

I am trying to create a stored procedure that would return output that I am showing below. I need to take the value for the latest date and substruct from it 4th past date's value, then divide by the past date and multiple the value by 100. Can you please suggest how to accomplish this? Thank you

FullName Date Size Used
onwisxa02:/onwisxa02_prd_global 6/30/2012 1002 645
onwisxa02:/onwisxa02_prd_global 6/23/2012 1002 933
onwisxa02:/onwisxa02_prd_global 6/16/2012 1122 964
onwisxa02:/onwisxa02_prd_global 6/9/2012 3099 490
onwisxa02:/onwisxa02_prd_global 6/2/2012 1002 743
456frsaa01:/456frsaa01_vol1 6/30/2012 1002 927
456frsaa01:/456frsaa01_vol1 6/23/2012 1002 907
456frsaa01:/456frsaa01_vol1 6/16/2012 1002 764
456frsaa01:/456frsaa01_vol1 6/9/2012 1002 777
456frsaa01:/456frsaa01_vol1 6/2/2012 1002 564
456frsaa02:/456frsaa02_prd_global 6/30/2012 1002 532
456frsaa02:/456frsaa02_prd_global 6/23/2012 1002 542
456frsaa02:/456frsaa02_prd_global 6/16/2012 1002 567
456frsaa02:/456frsaa02_prd_global 6/9/2012 1002 222
456frsaa02:/456frsaa02_prd_global 6/2/2012 1202 532

DESIRED OUTPUT:
FullName Size [Monthly Growth]
onwisxa02:/onwisxa02_prd_global 1002 (645-490)/490*100
456frsaa01:/456frsaa01_vol1 1002 (927-777)/777*100
456frsaa02:/456frsaa02_prd_global 1002 (532-222)/222*100

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-06 : 16:49:28

Tkae a look at the ROW_NUMBER() function:
http://msdn.microsoft.com/en-us/library/ms186734.aspx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-06 : 16:51:08

[code]DECLARE @t TABLE(FullName VARCHAR(50), DATE DATE, SIZE INT, Used INT)
INSERT @t SELECT 'onwisxa02:/onwisxa02_prd_global','6/30/2012',1002,645 UNION ALL SELECT
'onwisxa02:/onwisxa02_prd_global','6/23/2012',1002,933 UNION ALL SELECT
'onwisxa02:/onwisxa02_prd_global','6/16/2012',1122,964 UNION ALL SELECT
'onwisxa02:/onwisxa02_prd_global','6/9/2012',3099,490 UNION ALL SELECT
'onwisxa02:/onwisxa02_prd_global','6/2/2012',1002,743 UNION ALL SELECT
'456frsaa01:/456frsaa01_vol1','6/30/2012',1002,927 UNION ALL SELECT
'456frsaa01:/456frsaa01_vol1','6/23/2012',1002,907 UNION ALL SELECT
'456frsaa01:/456frsaa01_vol1','6/16/2012',1002,764 UNION ALL SELECT
'456frsaa01:/456frsaa01_vol1','6/9/2012',1002,777 UNION ALL SELECT
'456frsaa01:/456frsaa01_vol1','6/2/2012',1002,564 UNION ALL SELECT
'456frsaa02:/456frsaa02_prd_global','6/30/2012',1002,532 UNION ALL SELECT
'456frsaa02:/456frsaa02_prd_global','6/23/2012',1002,542 UNION ALL SELECT
'456frsaa02:/456frsaa02_prd_global','6/16/2012',1002,567 UNION ALL SELECT
'456frsaa02:/456frsaa02_prd_global','6/9/2012',1002,222 UNION ALL SELECT
'456frsaa02:/456frsaa02_prd_global','6/2/2012',1202,532

;WITH CTE AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY FullName ORDER BY DATE DESC) rn
FROM @t)
,CTE2 AS (SELECT * FROM CTE WHERE rn IN(1,4))
SELECT A.FullName, A.Size, (A.Used-B.Used)/1.0/B.Used*100
FROM CTE2 A INNER JOIN CTE2 B ON A.FullName=B.FullName AND A.rn=1 AND B.rn=4[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 16:52:05

something like


;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY FullName ORDER BY [Date] DESC) AS Seq,*
FROM Table
)
SELECT FullName,
MAX(CASE WHEN Seq=1 THEN Size END) AS [Size],
SUM(CASE WHEN Seq=1 THEN Used WHEN Seq = 4 THEN -1 * Used ELSE 0 END) /SUM(CASE WHEN Seq = 4 THEN Used ELSE 0 END) * 100 AS MonthlyGrowth
FROM CTE 
GROUP BY FullName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-07-06 : 19:31:31

Thank You all. I got what I was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 23:11:53

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-07-24 : 16:54:16

How do I modify this statement to not to display for example onwisxa02:/onwisxa02_prd_global if 3 or more most recent dates for onwisxa02:/onwisxa02_prd_global
are null?

FullName Date Size Used
onwisxa02:/onwisxa02_prd_global 6/30/2012 1002 null
onwisxa02:/onwisxa02_prd_global 6/23/2012 1002 null
onwisxa02:/onwisxa02_prd_global 6/16/2012 1122 null
onwisxa02:/onwisxa02_prd_global 6/9/2012 3099 490
onwisxa02:/onwisxa02_prd_global 6/2/2012 1002 743
onwisxa02:/onwisxa02_prd_global 5/26/2012 1002 722

Current algorithm:

;WITH CTE AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY FullName ORDER BY DATE DESC) rn
FROM @t)
,CTE2 AS (SELECT * FROM CTE WHERE rn IN(1,4))
SELECT A.FullName, A.Size, (A.Used-B.Used)/1.0/B.Used*100
FROM CTE2 A INNER JOIN CTE2 B ON A.FullName=B.FullName AND A.rn=1 AND B.rn=4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 17:00:09

[code]
;WITH CTE AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY FullName ORDER BY DATE DESC) rn
FROM @t)
,CTE2 AS (SELECT * FROM CTE WHERE rn IN(1,4))
SELECT A.FullName, A.Size, (A.Used-B.Used)/1.0/B.Used*100
FROM CTE2 A INNER JOIN CTE2 B ON A.FullName=B.FullName AND A.rn=1 AND B.rn=4
CROSS APPLY (SELECT COUNT(1) AS Cnt
FROM CTE
WHERE Rn <=3
AND FullName = A.FullName
AND used is not null
)C
WHERE C.Cnt >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources