SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rolling 3 month average cost help


Rolling 3 month average cost help

Author
Message
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Briceston
Briceston
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 441
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


dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Briceston
Briceston
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 441
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.

Briceston
Briceston
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 441
Can someone please chime on my last post, I really need some insight.
LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4456
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/
Briceston
Briceston
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 441
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

LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4456
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search