September 22, 2013 at 10:16 pm
Hi guys,
I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out for a couple days now, as well as get help, but to no avail.
What I'm trying to do is replicate a 3 month average cost metric that I have in excel to a sql query, so that I can use it in SSRS as a data set. In excel I take the avergae of the sum of the cost and divide it by the count of members.
I have two tables that derive the data.
Below is example of my tables:
Table name: Addmission
Contract Admissiondate SumofCost
0606 200701 8639.38
0607 200702 22895.94
0608 200703 123752.28
null 200704 61378.49
Table name: Members
Contract Admissiondate CountofMembers
0606 200701 86
0607 200702 102
0608 200703 90
null 200704 120
September 23, 2013 at 6:26 am
Is there always a 1 to 1 relationship between Admissions and Members? If so, why 2 tables? Could you move CountOfMembers from members into Admissions?
If you can, then the query is simple:
select Contract_ID, AdmitDate, SumOfCost / CountOfMembers
from Admissions
If you have to keep the data in 2 different tables, then this might work:
select A.Contract_ID, A.AdmitDate, A.SumOfCost / M.CountOfMembers
from Admissions A
inner join Members M on M.Contract_ID = A.Contract_ID
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 23, 2013 at 8:30 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 7:50 pm
As Sean suggested (1 and 2):
DECLARE @Admission TABLE
(
[Contract] VARCHAR(4)
,Admissiondate VARCHAR(6)
,SumofCost MONEY
);
INSERT INTO @Admission
SELECT '0606','200701',8639.38
UNION ALL SELECT '0607','200702',22895.94
UNION ALL SELECT '0608','200703',123752.28
UNION ALL SELECT null,'200704',61378.49;
DECLARE @Members TABLE
(
[Contract] VARCHAR(4)
,Admissiondate VARCHAR(6)
,CountofMembers INT
);
INSERT INTO @Members
SELECT '0606','200701',86
UNION ALL SELECT '0607','200702',102
UNION ALL SELECT '0608','200703',90
UNION ALL SELECT null,'200704',120;
I agree that there is a certain lack of clarity in requirements which expected results (Sean's #3) would resolve, but I'll give it a shot anyway and hope this is at least something to get you close.
WITH Tally (n) AS
(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
),
MyData AS
(
SELECT a.[Contract], a.AdmissionDate, SumofCost, CountofMembers
FROM @Members a
JOIN @Admission b ON ISNULL(a.[Contract], '') = ISNULL(b.[Contract], '') AND
a.AdmissionDate = b.AdmissionDate
)
SELECT AdmissionDate=MAX(AdmissionDate), SummaryDate
,SumofCost=SUM(SumofCost), CountofMembers=SUM(CountofMembers)
,AvgCost=SUM(SumofCost)/CASE WHEN SUM(CountofMembers) = 0 THEN 1 ELSE SUM(CountofMembers) END
FROM
(
SELECT *, SummaryDate=DATEADD(month, n-1, CAST(AdmissionDate + '01' AS DATE))
FROM MyData
CROSS APPLY Tally
) a
GROUP BY SummaryDate
HAVING MAX(CAST(AdmissionDate + '01' AS DATE)) >= SummaryDate;
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 24, 2013 at 8:19 am
Sorry for the delayed reply. I have been caught up with school and work since my first post. Thank you to those who chimed in.
If there was a lack of clarity in my first post, I do apologize for that as well. I will do my best to be more clear going forward.
I will read the article the that one of you guys mentioned, as well as post my create and insert statements shortly once I get chance to look more indeptly and try out the codes you guys have replied with.
Thanks,
Brice
September 25, 2013 at 12:37 pm
Hi guys,
Below is an example of my data using a create and insert.
--===== Create the test table with
Create table Admission
(Contract Varchar(4),
Admissiondate Varchar(6),
SumofCost DECIMAL(19, 4));
--===== Insert the test data into the test table
Insert into Admission
(Contract,Admissiondate,SumofCost)
Values
('0606','200701','8639.38'),
('0607','200702','22895.94'),
('0608','200703','123752.28'),
('0609','200704', '61378.49')
--===== Create the test table with
Create table Members
(Contract Varchar(4),
Admissiondate Varchar(6),
CountofMembers INT;
--===== Insert the test data into the test table
Insert into Members
Values
('0606','200701', '86'),
('0607', '200702', '102'),
('0608', '200703', '90'),
('0609', '200704', '120')
-====== Likely Output
Contract Admissiondate 3 month average cost
0606 200701
0607 200702
0608 200703
0609 200704 $577.85
September 25, 2013 at 6:29 pm
Briceston (9/25/2013)
Hi guys,Below is an example of my data using a create and insert.
--===== Create the test table with
Create table Admission
(Contract Varchar(4),
Admissiondate Varchar(6),
SumofCost DECIMAL(19, 4));
--===== Insert the test data into the test table
Insert into Admission
(Contract,Admissiondate,SumofCost)
Values
('0606','200701','8639.38'),
('0607','200702','22895.94'),
('0608','200703','123752.28'),
('0609','200704', '61378.49')
--===== Create the test table with
Create table Members
(Contract Varchar(4),
Admissiondate Varchar(6),
CountofMembers INT;
--===== Insert the test data into the test table
Insert into Members
Values
('0606','200701', '86'),
('0607', '200702', '102'),
('0608', '200703', '90'),
('0609', '200704', '120')
-====== Likely Output
Contract Admissiondate 3 month average cost
0606 200701
0607 200702
0608 200703
0609 200704 $577.85
Questions:
1. If you're doing a rolling 3 month average, shouldn't the first row containing an average cost be 0608?
2. If you're doing a rolling 3 month average of the prior 3 months, that would not be the case, but in that case I think your math is off. I get $558.58.
In any event, with just a few tweaks the code I provided should work for this case. For example, try this:
WITH Tally (n) AS
(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
),
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 AdmissionDate, SummaryDate, SumofCost, CountofMembers
,AvgCost=CASE WHEN rn > 2 THEN AvgCost END
FROM
(
SELECT AdmissionDate, SummaryDate, SumofCost, CountofMembers, AvgCost
,rn=ROW_NUMBER() OVER (ORDER BY AdmissionDate)
FROM (
SELECT AdmissionDate=MAX(AdmissionDate), SummaryDate
,SumofCost=SUM(SumofCost), CountofMembers=SUM(CountofMembers)
,AvgCost=SUM(SumofCost)/CASE WHEN SUM(CountofMembers) = 0 THEN 1 ELSE SUM(CountofMembers) END
FROM
(
SELECT *, SummaryDate=DATEADD(month, n-1, CAST(AdmissionDate + '01' AS DATE))
FROM MyData
CROSS APPLY Tally
) a
GROUP BY SummaryDate
HAVING MAX(CAST(AdmissionDate + '01' AS DATE)) >= SummaryDate
) a
) b;
Results returned are:
AdmissionDate SummaryDate SumofCost CountofMembers AvgCost
200701 2007-01-01 8639.38 86 NULL
200702 2007-02-01 31535.32 188 NULL
200703 2007-03-01 155287.60 278 558.5884
200704 2007-04-01 208026.71 312 666.7522
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 29, 2013 at 9:46 am
First, thank you for replying. How are you deriving your average cost figures?
September 29, 2013 at 6:13 pm
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.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 30, 2013 at 6:50 am
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.
September 30, 2013 at 6:09 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 30, 2013 at 7:36 pm
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 CountofMembersAvgCost
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 30, 2013 at 8:31 pm
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[/url]
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 2, 2013 at 9:26 pm
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-001200701 8639.38
P3347-001200702 31895.94
P3347-001200703 213752.29
P3347-001200704 223869.89
P3347-001200705 61378.49
P3347-002200801 90198.43
P3347-002200802 125753.98
P3347-002200803 215915.85
Membership
SELECT
(ContractCode+'-'+BenefitPlanCode)As Product,
EffectiveCCYYMM,
count(memberid) AS numberofMembers
FROM
factMembership
GROUP BY
EffectiveCCYYMM,
ContractCode,
BenefitPlanCode
order by 1,2
ContractEffectiveCCYYMMnumberofMembers
P3347-001200701 104
P3347-001200702 200
P3347-001200703 310
P3347-001200704 218
P3347-001200705 318
H3347-002200801 257
H3347-002200802 309
H3347-002200803 330
October 2, 2013 at 9:47 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply