Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Rolling 3 month average cost help Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 6:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 PM
Points: 3,640, Visits: 5,287
Briceston (9/30/2013)
Ok, understood.

I used your last code example in my environment, and it's taking long to execute. I keep having to cancel it at the 4min mark or else the DBA will send me a less than stellar email about the performance of my query. Any adjustments I can possible make within the code?


dwain.c (9/29/2013)
Briceston (9/29/2013)
First, thank you for replying. How are you deriving your average cost figures?



I included the intermediate cost and members columns so you could see that. Each is the sum of the current record plus the prior 2. The average cost is then cost/members.


Can you post more info on your source table, specifically PRIMARY KEY and any available indexes?

Too bad you're not on SQL 2012 as that has a nifty solution for this problem.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500231
Posted Monday, September 30, 2013 7:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 PM
Points: 3,640, Visits: 5,287
In the meantime, you could also try this version, which provides a slightly different but possibly useable result also.

WITH MyData AS
(
SELECT a.[Contract], a.AdmissionDate, SumofCost, CountofMembers
FROM Members a
JOIN Admission b ON a.[Contract] = b.[Contract] AND
a.AdmissionDate = b.AdmissionDate
)
SELECT [Contract], AdmissionDate, SumofCost, CountofMembers, AvgCost
FROM
(
SELECT a.[Contract], a.AdmissionDate
,SumofCost=a.SumofCost + b.SumofCost
,CountofMembers=a.CountofMembers + b.CountofMembers
,AvgCost=CASE a.CountofMembers + b.CountofMembers WHEN 0 THEN 0
ELSE (a.SumofCost + b.SumofCost) / (a.CountofMembers + b.CountofMembers) END *
CASE WHEN ROW_NUMBER() OVER (ORDER BY AdmissionDate) < 3 THEN NULL ELSE 1 END
FROM MyData a
OUTER APPLY
(
SELECT SumofCost=SUM(SumofCost), CountofMembers=SUM(CountofMembers)
FROM
(
SELECT TOP 2 AdmissionDate, SumofCost, CountofMembers
FROM MyData b
WHERE a.AdmissionDate > b.AdmissionDate
ORDER BY AdmissionDate DESC
) b
) b
) b

These are the results (note difference is in the SumofCost, CountofMembers in the second row):

Contract  AdmissionDate  SumofCost    CountofMembers	AvgCost
0606 200701 NULL NULL NULL
0607 200702 31535.3200 188 NULL
0608 200703 155287.6000 278 558.588489
0609 200704 208026.7100 312 666.752275



The AvgCost column is the same in both versions.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500246
Posted Monday, September 30, 2013 8:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 PM
Points: 3,640, Visits: 5,287
If that still wasn't fast enough, this one should blow the doors off of both.

SELECT a.[Contract], a.AdmissionDate, Cost=SumofCost, Members=CountofMembers
,SumofCost=CAST(0.0 AS DECIMAL(19,4))
,CountofMembers=0
,AvgCost=CAST(0.0 AS DECIMAL(19,4))
INTO #NewTable
FROM Members a
JOIN Admission b ON a.[Contract] = b.[Contract] AND
a.AdmissionDate = b.AdmissionDate

ALTER TABLE #NewTable ALTER COLUMN AdmissionDate VARCHAR(6) NOT NULL;
ALTER TABLE #NewTable ADD PRIMARY KEY(AdmissionDate);

DECLARE @Lag1SOC DECIMAL(19, 4) = 0
,@Lag2SOC DECIMAL(19, 4) = 0
,@Lag3SOC DECIMAL(19, 4) = 0
,@Lag1CM INT = 0
,@Lag2CM INT = 0
,@Lag3CM INT = 0
,@rtSOC DECIMAL(19, 4) = 0
,@rtCM INT = 0
,@rn INT = 0;

UPDATE #NewTable WITH(TABLOCKX)
SET @rtSOC = SumOfCost = @rtSOC + Cost - @Lag3SOC
,@rtCM = CountOfMembers = @rtCM + Members - @Lag3CM
,AvgCost = CASE WHEN @rn < 3 THEN NULL
WHEN @rtCM = 0 THEN 0
ELSE @rtSOC / @rtCM
END
,@Lag3SOC = @Lag2SOC
,@Lag2SOC = @Lag1SOC
,@Lag1SOC = Cost
,@Lag3CM = @Lag2CM
,@Lag2CM = @Lag1CM
,@Lag1CM = Members
,@rn = @rn + 1
OPTION (MAXDOP 1);

SELECT [Contract], AdmissionDate, SumofCost, CountofMembers, AvgCost
FROM #NewTable;

GO
DROP TABLE #NewTable;


Understanding how the Quirky Update (QU) works and all the rules can be a bit of a challenge, but this article by SQL MVP Jeff Moden goes into all the details:
Solving the Running Total and Ordinal Rank Problems

While the article covers only running totals, what I've done here is to limit the running totals to the current + prior 2 rows using the @lag variables.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500250
Posted Wednesday, October 2, 2013 9:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:40 AM
Points: 25, Visits: 168
dwain.c, Thanks for your insight thus far, I really appreciate it.

Your code is not working with my environment, and I'm not exactly sure why.

Below is the code from my environment that I use to pull my admissions data and my membership data.

Also, I have included the result from both query. Both these table would be use toegether to achieve a 3 month average cost.


Admission

SELECT
(ContractCode+'-'+BenefitPlanCode)As Contract,
AdmitCCYYMM,
IsNull(SUM(AmountPaid),0) AS Cost

FROM
factAdmissions
Where ContractCode Is not null
And BenefitPlanCode Is not null
GROUP BY
ContractCode,
BenefitPlanCode,
AdmitCCYYMM
Order by 1,2

Contract AdmitCCYYMM Cost
P3347-001 200701 8639.38
P3347-001 200702 31895.94
P3347-001 200703 213752.29
P3347-001 200704 223869.89
P3347-001 200705 61378.49
P3347-002 200801 90198.43
P3347-002 200802 125753.98
P3347-002 200803 215915.85



Membership
SELECT
(ContractCode+'-'+BenefitPlanCode)As Product,
EffectiveCCYYMM,
count(memberid) AS numberofMembers

FROM
factMembership
GROUP BY
EffectiveCCYYMM,
ContractCode,
BenefitPlanCode
order by 1,2


Contract EffectiveCCYYMM numberofMembers
P3347-001 200701 104
P3347-001 200702 200
P3347-001 200703 310
P3347-001 200704 218
P3347-001 200705 318
H3347-002 200801 257
H3347-002 200802 309
H3347-002 200803 330

Post #1500973
Posted Wednesday, October 2, 2013 9:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 PM
Points: 3,640, Visits: 5,287
Which code?

Guessing here but I see that both your tables (from output results) have missing months. You'll need to fill in those missing months with a Calendar or Tally table.

Try putting both SELECTs (from the separate tables) into separate CTEs and then doing a LEFT JOIN (in each CTE) to the Calendar table to generate the dates. You can use these CTEs (using the INNER JOIN I did) to put rows into the #NewTable created with my Quirky Update method.

If that explanation isn't sufficient, feel free to ask more questions but I strongly recommend you Google "Calendar table" first so you can get that concept.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500974
Posted Thursday, October 31, 2013 11:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:40 AM
Points: 25, Visits: 168
Actually, the missing months was an error on my part when providing a sampling. Both tables have equals months between them.

I constructed a cte, however, I'm still having some issues with the output. I'm getting the following errors below: Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.

I tried casting the date, but still had issues. My date field format is: YYYYMM

Your insight is appreciated.


;WITH cte1 AS
(
SELECT
(ContractCode+'-'+ BenefitPlanCode)Product,
AdmitCCYYMM,
SUM(AmountPaid)Cost
From factAdmissions
GROUP BY
ContractCode,
BenefitPlanCode,
AdmitCCYYMM
),
cte2 AS
(
SELECT
(ContractCode+'-'+BenefitPlanCode)Product,
EffectiveCCYYMM,
Count(MemberId) AS numberofMembers
FROM
factmembership
GROUP BY
ContractCode,
BenefitPlanCode,
EffectiveCCYYMM
)
SELECT
(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)
FROM
cte1 a1
INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM
LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = DATEADD(Month,1,a2.AdmitCCYYMM)
LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = DATEADD(Month,2,a3.AdmitCCYYMM)






dwain.c (10/2/2013)
Which code?

Guessing here but I see that both your tables (from output results) have missing months. You'll need to fill in those missing months with a Calendar or Tally table.

Try putting both SELECTs (from the separate tables) into separate CTEs and then doing a LEFT JOIN (in each CTE) to the Calendar table to generate the dates. You can use these CTEs (using the INNER JOIN I did) to put rows into the #NewTable created with my Quirky Update method.

If that explanation isn't sufficient, feel free to ask more questions but I strongly recommend you Google "Calendar table" first so you can get that concept.
Post #1510356
Posted Friday, November 1, 2013 12:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:40 AM
Points: 25, Visits: 168
Can someone please chime on my last post, I really need some insight.
Post #1510750
Posted Friday, November 1, 2013 1:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 346, Visits: 1,393
Briceston (10/31/2013)


I tried casting the date, but still had issues. My date field format is: YYYYMM

SELECT
(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)
FROM
cte1 a1
INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM
LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = DATEADD(Month,1,a2.AdmitCCYYMM)
LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = DATEADD(Month,2,a3.AdmitCCYYMM)



From an earlier post
. . .
Contract Varchar(4),
Admissiondate Varchar(6),

I am guessing that AdmitCCYYMM is the same as Admissiondate. You are trying to do date arithmetic on a VarChar. Casting it does not help because of the format you have chosen. (YYYYMM) When you Cast('201301' as Date) the Cast sees year = 20, (2020), month=13 and day = 01. And of course the month is out of bounds.

You could convert AdmissionDate to a proper datetime in the table and this problem will go away. Or do something like DATEADD(Month,1,a2.AdmitCCYYMM + '01'). But this gives a couple more issues to worry about.

1. A time component is added and has to be accounted for when comparing for equality
2. This has to be done on both sides of the = and that brings Sargeability issues into play.

Or you could do something like:

SELECT SubString(CONVERT(varchar, DATEADD(MM, 1, '201301'+'01'), 112), 1, 6)

Returns 201302


INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM
LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,1,a2.AdmitCCYYMM) + '01'), 112), 1, 6)
LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,2,a3.AdmitCCYYMM) + '01'), 112), 1, 6)


These are just some thoughts off the top of my head.

HTH


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1510784
Posted Saturday, November 2, 2013 2:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:40 AM
Points: 25, Visits: 168
Thanks for repyling. I tried your code below and got :Msg 174, Level 15, State 1, Line 32
The substring function requires 3 argument(s).



LinksUp (11/1/2013)
Briceston (10/31/2013)


I tried casting the date, but still had issues. My date field format is: YYYYMM

SELECT
(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)
FROM
cte1 a1
INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM
LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = DATEADD(Month,1,a2.AdmitCCYYMM)
LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = DATEADD(Month,2,a3.AdmitCCYYMM)



From an earlier post
. . .
Contract Varchar(4),
Admissiondate Varchar(6),

I am guessing that AdmitCCYYMM is the same as Admissiondate. You are trying to do date arithmetic on a VarChar. Casting it does not help because of the format you have chosen. (YYYYMM) When you Cast('201301' as Date) the Cast sees year = 20, (2020), month=13 and day = 01. And of course the month is out of bounds.

You could convert AdmissionDate to a proper datetime in the table and this problem will go away. Or do something like DATEADD(Month,1,a2.AdmitCCYYMM + '01'). But this gives a couple more issues to worry about.

1. A time component is added and has to be accounted for when comparing for equality
2. This has to be done on both sides of the = and that brings Sargeability issues into play.

Or you could do something like:

SELECT SubString(CONVERT(varchar, DATEADD(MM, 1, '201301'+'01'), 112), 1, 6)

Returns 201302


INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM
LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,1,a2.AdmitCCYYMM) + '01'), 112), 1, 6)
LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,2,a3.AdmitCCYYMM) + '01'), 112), 1, 6)


These are just some thoughts off the top of my head.

HTH
Post #1510866
Posted Saturday, November 2, 2013 9:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 346, Visits: 1,393
Just an extra closing paren.


INNER JOIN cte2 b ON a1.AdmitCCYYMM = b.EffectiveCCYYMM
LEFT JOIN cte1 a2 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,1,a2.AdmitCCYYMM + '01'), 112), 1, 6)
LEFT JOIN cte1 a3 ON a1.AdmitCCYYMM = SubString(CONVERT(varchar, DATEADD(Month,2,a3.AdmitCCYYMM + '01'), 112), 1, 6)



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1510872
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse