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 Sunday, September 22, 2013 10:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:26 AM
Points: 18, Visits: 121
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






Post #1497261
Posted Monday, September 23, 2013 6:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 319, Visits: 1,142
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/
Post #1497347
Posted Monday, September 23, 2013 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497415
Posted Monday, September 23, 2013 7:50 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: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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 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 #1497619
Posted Tuesday, September 24, 2013 8:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:26 AM
Points: 18, Visits: 121
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
Post #1497893
Posted Wednesday, September 25, 2013 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:26 AM
Points: 18, Visits: 121
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


Post #1498542
Posted Wednesday, September 25, 2013 6:29 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: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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 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 #1498638
Posted Sunday, September 29, 2013 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:26 AM
Points: 18, Visits: 121
First, thank you for replying. How are you deriving your average cost figures?

Post #1499758
Posted Sunday, September 29, 2013 6:13 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: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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 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 #1499804
Posted Monday, September 30, 2013 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:26 AM
Points: 18, Visits: 121
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.
Post #1499967
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse