SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 1 week change calculation help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kishoremcp
Starting Member

India
39 Posts

Posted - 10/21/2013 :  11:52:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/21/2013 :  14:37:25  Show Profile  Reply with Quote
Whats PeriodWK? does it have week number value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

kishoremcp
Starting Member

India
39 Posts

Posted - 10/22/2013 :  02:02:41  Show Profile  Reply with Quote
PeriodWK is in 2010-02-26 09:53:45.397 format..

Regards
Kishore
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/22/2013 :  07:52:49  Show Profile  Reply with Quote
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
Go to Top of Page

kishoremcp
Starting Member

India
39 Posts

Posted - 10/22/2013 :  09:23:27  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/22/2013 :  10:07:55  Show Profile  Reply with Quote
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
Go to Top of Page

kishoremcp
Starting Member

India
39 Posts

Posted - 10/22/2013 :  23:02:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/23/2013 :  02:21:22  Show Profile  Reply with Quote

;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)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

kishoremcp
Starting Member

India
39 Posts

Posted - 10/23/2013 :  07:21:54  Show Profile  Reply with Quote
Shall I club the two queries or what should i do? please tell me ...i am new to sql

Regards
Kishore
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/23/2013 :  08:45:55  Show Profile  Reply with Quote
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
Go to Top of Page

kishoremcp
Starting Member

India
39 Posts

Posted - 10/23/2013 :  09:40:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/23/2013 :  13:21:04  Show Profile  Reply with Quote
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
Go to Top of Page

kishoremcp
Starting Member

India
39 Posts

Posted - 10/24/2013 :  02:23:38  Show Profile  Reply with Quote
Okay. Not a problem. I will try to club it. thank you for your help

Regards
Kishore
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000