Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Rolling 3 month average cost help Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, September 22, 2013 10:16 PM
 Valued Member Group: General Forum Members Last Login: Monday, December 5, 2016 2:13 PM Points: 56, Visits: 383
 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: AddmissionContract Admissiondate SumofCost 0606 200701 8639.380607 200702 22895.940608 200703 123752.28null 200704 61378.49Table name: MembersContract Admissiondate CountofMembers0606 200701 860607 200702 1020608 200703 90null 200704 120
Post #1497261
 Posted Monday, September 23, 2013 6:26 AM
 Say Hey Kid Group: General Forum Members Last Login: 2 days ago @ 7:24 PM Points: 693, Visits: 3,802
 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
 SSCoach Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:08 PM Points: 16,145, Visits: 16,850
 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 statements2. Sample data in the form of INSERT INTO statements3. Expected results based on the sample dataPlease 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 As Sean suggested (1 and 2):`DECLARE @Admission TABLE( [Contract] VARCHAR(4) ,Admissiondate VARCHAR(6) ,SumofCost MONEY);INSERT INTO @AdmissionSELECT '0606','200701',8639.38UNION ALL SELECT '0607','200702',22895.94UNION ALL SELECT '0608','200703',123752.28UNION ALL SELECT null,'200704',61378.49;DECLARE @Members TABLE( [Contract] VARCHAR(4) ,Admissiondate VARCHAR(6) ,CountofMembers INT);INSERT INTO @MembersSELECT '0606','200701',86UNION ALL SELECT '0607','200702',102UNION ALL SELECT '0608','200703',90UNION 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) ENDFROM ( SELECT *, SummaryDate=DATEADD(month, n-1, CAST(AdmissionDate + '01' AS DATE)) FROM MyData CROSS APPLY Tally) aGROUP BY SummaryDateHAVING 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1497619
 Posted Tuesday, September 24, 2013 8:19 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 5, 2016 2:13 PM Points: 56, Visits: 383
 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
 Valued Member Group: General Forum Members Last Login: Monday, December 5, 2016 2:13 PM Points: 56, Visits: 383
 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 tableInsert 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 tableInsert into MembersValues('0606','200701', '86'),('0607', '200702', '102'),('0608', '200703', '90'),('0609', '200704', '120')-====== Likely OutputContract Admissiondate 3 month average cost0606 200701 0607 2007020608 2007030609 200704 \$577.85 `
Post #1498542
 Posted Wednesday, September 25, 2013 6:29 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 tableInsert 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 tableInsert into MembersValues('0606','200701', '86'),('0607', '200702', '102'),('0608', '200703', '90'),('0609', '200704', '120')-====== Likely OutputContract Admissiondate 3 month average cost0606 200701 0607 2007020608 2007030609 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 ENDFROM( 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 AvgCost200701 2007-01-01 8639.38 86 NULL200702 2007-02-01 31535.32 188 NULL200703 2007-03-01 155287.60 278 558.5884200704 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1498638
 Posted Sunday, September 29, 2013 9:46 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 5, 2016 2:13 PM Points: 56, Visits: 383
 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1499804
 Posted Monday, September 30, 2013 6:50 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 5, 2016 2:13 PM Points: 56, Visits: 383
 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

 Permissions