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 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply