1 week change calculation help

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

General SQL Server Forums

Script Library

1 week change calculation help

Author

Topic

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-21 : 11:52:13

I have the below code which is giving me the required output. I am getting 26 weeks data. Now I want to calculate 1 month change (Latest week value/previous week value) which i am unable to calculate. Please help me with that...

select SubMetric,isnull(Value,0) as [Value], PeriodWK,
REPLACE(CONVERT(VARCHAR(9), PeriodWK, 6), ' ', '-') AS [DD-Mon-YY]
into #temp1
from [RawData].[dbo].[VMWare_Virtual]
where SubMetric in('Consolidation', 'Host', 'Physical', 'Total', 'Virtual', 'Virtualization %', 'Virtualization Host%')
and PeriodWK >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-28,0) and PeriodWK < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
--group by SubMetric, Value, PeriodWK

select SubMetric, sum(Value)/26 as [Average] into #temp2 from #temp1
group by SubMetric

select * into #temp3 from #temp1
where PeriodWK >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-15,0) and PeriodWK < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

select SubMetric, sum(Value)/13 as [13 M Average] into #temp4 from #temp3
group by SubMetric

select #temp3.SubMetric, #temp3.Value, #temp3.PeriodWK,#temp3.[DD-Mon-YY], #temp2.[Average]
into #temp5
from #temp3
join #temp2
on #temp3.SubMetric=#temp2.SubMetric

select #temp5.SubMetric, #temp5.Value, #temp5.PeriodWK, #temp5.[DD-Mon-YY], #temp5.[Average], #temp4.[13 M Average]
from #temp5
join #temp4
on #temp5.SubMetric=#temp4.SubMetric

Ex: If it is showing Sep-30 as latest week in the report and sep-23 as the previous week then i want (sep30!value/Sep23!value-1).

Regards
Kishore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 14:37:25

Whats PeriodWK? does it have week number value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-22 : 02:02:41

PeriodWK is in 2010-02-26 09:53:45.397 format..

Regards
Kishore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 07:52:49

quote:
Originally posted by kishoremcp

PeriodWK is in 2010-02-26 09:53:45.397 format..

Regards
Kishore

then which field has weekno values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-22 : 09:23:27

By using the query i am getting the result like that. So now I want the calculation as mentioned. I am using the same query which you mentioned in your earlier post.

Regards
Kishore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 10:07:55

quote:
Originally posted by kishoremcp

By using the query i am getting the result like that. So now I want the calculation as mentioned. I am using the same query which you mentioned in your earlier post.

Regards
Kishore

nope. My question was how are you currently getting week number? You need a field containing week number to determine prev week.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-22 : 23:02:42

I am not getting week numbers any where. I am your answer in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188953 to get the required output. It is giving me weekly data. Now I want to calculate the difference in week (last shown week value/previous week shown value-1). I hope i have answered your question.

thank you in advance for your attempt to help me....

Regards
Kishore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 02:21:22

[code]
;With CTE
AS
(
select ...,
DATEADD(wk,DATEDIFF(wk,0,[date]),0) AS Weekdate
from ...
where [date] >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-26,0)
AND [date] < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
)
SELECT c1.*,
c1.Value/c2.value -1
FROM CTE c1
LEFT JOIN CTE c2
ON c2.Weekdate = DATEADD(wk,-1,c1.WeekDate)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-23 : 07:21:54

Shall I club the two queries or what should i do? please tell me ...i am new to sql

Regards
Kishore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 08:45:55

you need to put your query inside CTE part and add additional column Weekdate.
then just use the given select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-23 : 09:40:19

I am sorry to give you trouble once again. I am unable to understand that, so could you please do that for me instead of i doing it in the wrong way..?

Regards
Kishore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:21:04

Nope..I cant spoonfeed you with the complete code. You need to put in some effort yourself.
If you face some issues in query we will help you to fix it but without seeing any attempt we cant help.
Also in case of any issues you need to properly explain the issue giving some sample data and with your expected output if you want to get some help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kishoremcp
Starting Member

41 Posts

Posted - 2013-10-24 : 02:23:38

Okay. Not a problem. I will try to club it. thank you for your help

Regards
Kishore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources