March 16, 2009 at 9:13 am
Try this:
Alter Proc spPerfProduct( @DT as datetime ) as
WITH cteCrossTab as (
Select ID as MID
, Coalesce(Max(Case When Perf_dt=@DT Then Net_Perf End), 0.0) as [Dt1]
, Coalesce(Max(Case When Perf_dt=(@DT-1) Then Net_Perf End), 0.0) as [Dt2]
, Coalesce(Max(Case When Perf_dt=(@DT-2) Then Net_Perf End), 0.0) as [Dt3]
, Coalesce(Max(Case When Perf_dt=(@DT-3) Then Net_Perf End), 0.0) as [Dt4]
, Coalesce(Max(Case When Perf_dt=(@DT-4) Then Net_Perf End), 0.0) as [Dt5]
, Coalesce(Max(Case When Perf_dt=(@DT-5) Then Net_Perf End), 0.0) as [Dt6]
, Coalesce(Max(Case When Perf_dt=(@DT-6) Then Net_Perf End), 0.0) as [Dt7]
, Coalesce(Max(Case When Perf_dt=(@DT-7) Then Net_Perf End), 0.0) as [Dt8]
, Coalesce(Max(Case When Perf_dt=(@DT-8) Then Net_Perf End), 0.0) as [Dt9]
, Coalesce(Max(Case When Perf_dt=(@DT-9) Then Net_Perf End), 0.0) as [Dt10]
, Coalesce(Max(Case When Perf_dt=(@DT-10) Then Net_Perf End), 0.0) as [Dt11]
, Coalesce(Max(Case When Perf_dt=(@DT-11) Then Net_Perf End), 0.0) as [Dt12]
From Perf
Group By ID
)
SELECT MID,
(((1.0+Dt1/100.0)
*(1.0+Dt2/100.0)
*(1.0+Dt3/100.0)
*(1.0+Dt4/100.0)
*(1.0+Dt5/100.0)
*(1.0+Dt6/100.0)
*(1.0+Dt7/100.0)
*(1.0+Dt8/100.0)
*(1.0+Dt9/100.0)
*(1.0+Dt10/100.0)
*(1.0+Dt11/100.0)
*(1.0+Dt12/100.0))-1.0) * 100.0
From cteCrossTab
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2009 at 11:38 pm
I think it wont work in sql2000.
Also i don't know the date range. sometime it may start from today's date and end at jan/02/2009(working day) or it may end at mar/02/2009(working day).
For time being i used cursor, but i dont want to use it.
karthik
March 17, 2009 at 12:01 am
Oops, sorry. I missed that I was in the SQL2000 forum. You just need to change the CTE to be a derived table:
Alter Proc spPerfProduct( @DT as datetime ) as
SELECT MID,
(((1.0+Dt1/100.0)
*(1.0+Dt2/100.0)
*(1.0+Dt3/100.0)
*(1.0+Dt4/100.0)
*(1.0+Dt5/100.0)
*(1.0+Dt6/100.0)
*(1.0+Dt7/100.0)
*(1.0+Dt8/100.0)
*(1.0+Dt9/100.0)
*(1.0+Dt10/100.0)
*(1.0+Dt11/100.0)
*(1.0+Dt12/100.0))-1.0) * 100.0
From (
Select ID as MID
, Coalesce(Max(Case When Perf_dt=@DT Then Net_Perf End), 0.0) as [Dt1]
, Coalesce(Max(Case When Perf_dt=(@DT-1) Then Net_Perf End), 0.0) as [Dt2]
, Coalesce(Max(Case When Perf_dt=(@DT-2) Then Net_Perf End), 0.0) as [Dt3]
, Coalesce(Max(Case When Perf_dt=(@DT-3) Then Net_Perf End), 0.0) as [Dt4]
, Coalesce(Max(Case When Perf_dt=(@DT-4) Then Net_Perf End), 0.0) as [Dt5]
, Coalesce(Max(Case When Perf_dt=(@DT-5) Then Net_Perf End), 0.0) as [Dt6]
, Coalesce(Max(Case When Perf_dt=(@DT-6) Then Net_Perf End), 0.0) as [Dt7]
, Coalesce(Max(Case When Perf_dt=(@DT-7) Then Net_Perf End), 0.0) as [Dt8]
, Coalesce(Max(Case When Perf_dt=(@DT-8) Then Net_Perf End), 0.0) as [Dt9]
, Coalesce(Max(Case When Perf_dt=(@DT-9) Then Net_Perf End), 0.0) as [Dt10]
, Coalesce(Max(Case When Perf_dt=(@DT-10) Then Net_Perf End), 0.0) as [Dt11]
, Coalesce(Max(Case When Perf_dt=(@DT-11) Then Net_Perf End), 0.0) as [Dt12]
From Perf
Group By ID
) a
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2009 at 12:04 am
My routine handles a variable end-date, but not a variable range. To do that you will need to use Dynamic SQL. Use my routine as the starting point, and use a Tally table to enumerate the number of days.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2009 at 12:09 am
RBarryYoung,
Thank you so much! I will try and get back to you.
karthik
March 17, 2009 at 3:39 pm
Kartik,
with a little bit of effort from your side you could easily transform "string concatenation" function (which you already copied from this forum) to "float multiplication" one.
_____________
Code for TallyGenerator
March 18, 2009 at 3:21 am
Sergiy (3/17/2009)
Kartik,with a little bit of effort from your side you could easily transform "string concatenation" function (which you already copied from this forum) to "float multiplication" one.
"string concatenation" function ? i couldn't get you. you mean to say from this topic or from other topic.
karthik
Viewing 7 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy