Sunday, August 11, 2013

[SQL Server] Provided Servcies / Budgeted Servcies

[SQL Server] Provided Servcies / Budgeted Servcies

Provided Servcies / Budgeted Servcies

Posted: 11 Aug 2013 04:14 AM PDT

Hi all. I posted a similar post yesterday but I don't think I articulated what I am trying to accomplish well - I am a SQL server newbie. I need calculate (1) the total amount of each WAIVER_SERVICE that was budgeted, (2) the total amount for each wavier service that was provided and (3) the % Budget Used. Grouped by WAIVER_SERVICES and Month/Year.The output I'm looking for should look like this:[code="sql"]CLT_NBR WAIVER_SERVICES Month/Year Amount_Services_Provided Amount_Budgeted %_Budget_Used 10178 2 1 2013 1000 1100 90% 10178 2 2 2013 950 1000 95% 10178 2 3 2013 759 650 115% 10178 4 11 2012 600 1000 60%10178 4 9 2013 100 100 100%[/code]If a client was budgeted a service and did not receive it for a month a 0 value is needed and I need to display 0's for months with no data.Below are 3 temp tables which are examples of the original 3 tables used for the two programs. The tables are [b]B2H_DSP[/b], [b]B2H_SummaryForms[/b] and [b]B2H_RATE [/b]. Note the [b]B2H_Rate[/b] table is a reference table which pulls the cost of the wavier_services. [b]B2H_DSP TABLE[/b][code="sql"]CREATE TABLE B2H_DSP ( BGT_NBR int, CLT_NBR int, WAIVER_SERVICES int, WAIVER_PROGRAM int, START_DT date, END_DT date, BILLABLE_UNITS_OF_SERVICE int, UNITS_PER_MONTH int, RATE_PER_UNIT_OF_SERVICE int, PROJECTED_COST_PER_MONTH int, NUMBER_OF_MONTHS_OF_SERVICES int, PROJECTED_COST_PER_12_MONTHS int); INSERT INTO B2H_DSP VALUES (1, 10178, 9, 2, '7/21/2011', '7/21/2012', 5, 16, 12.7, 203.2, 12, 2633.47),(1, 10178, 10, 2, '7/21/2011', '7/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(1, 10178, 11, 2, '7/21/2011', '7/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(1, 10178, 5, 2, '7/21/2011', '7/21/2012', 5, 28, 12.64, 353.92, 12, 4586.8),(1, 10178, 2, 2, '7/21/2011', '7/21/2012', 5, 8, 13.23, 105.84, 12, 1371.69),(1, 10178, 3, 2, '7/21/2011', '7/21/2012', 5, 28, 13.23, 370.44, 12, 4800.9),(1, 10178, 8, 2, '7/21/2011', '7/21/2012', 6, 16, 13.23, 211.68, 12, 2743.37),(1, 10178, 1, 1, '7/21/2011', '7/21/2012', 1, 1, 2040, 2040, 12, 24480),(406, 10178, 1, 1, '7/21/2012', '7/21/2013', 1, 1, 2040, 2040, 12, 24480),(406, 10178, 2, 2, '7/21/2012', '7/21/2013', 5, 8, 13.23, 105.84, 12, 1371.69),(406, 10178, 3, 2, '7/21/2012', '7/21/2013', 5, 28, 13.23, 370.44, 12, 4800.9),(406, 10178, 5, 2, '7/21/2012', '7/21/2013', 5, 20, 19.45, 389, 12, 5041.44),(406, 10178, 8, 2, '7/21/2012', '7/21/2013', 6, 16, 13.23, 211.68, 12, 2743.37),(406, 10178, 9, 2, '7/21/2012', '7/21/2013', 5, 16, 19.55, 312.8, 12, 4053.89),(406, 10178, 10, 2, '7/21/2012', '7/21/2013', 5, 8, 19.55, 156.4, 12, 2026.94),(406, 10178, 11, 2, '7/21/2012', '7/21/2013', 5, 8, 19.55, 156.4, 12, 2026.94),(933, 10178, 1, 1, '7/21/2013', '7/21/2014', 1, 1, 2040, 2040, 12, 24480),(933, 10178, 2, 2, '7/21/2013', '7/21/2014', 5, 8, 13.23, 105.84, 12, 1371.69),(933, 10178, 5, 2, '7/21/2013', '7/21/2014', 5, 16, 19.45, 311.2, 12, 4033.15),(933, 10178, 10, 2, '7/21/2013', '7/21/2014', 5, 4, 19.55, 78.2, 12, 1013.47),(933, 10178, 11, 2, '7/21/2013', '7/21/2014', 5, 4, 19.55, 78.2, 12, 1013.47),(933, 10178, 3, 2, '7/21/2013', '7/21/2014', 5, 18, 13.23, 238.14, 12, 3086.29),(933, 10178, 8, 2, '7/21/2013', '7/21/2014', 3, 3, 236.7, 710.1, 12, 9202.9),(933, 10178, 9, 2, '7/21/2013', '7/21/2014', 5, 18, 19.55, 351.9, 12, 4560.62),(480, 12014, 1, 1, '9/21/2011', '9/21/2012', 1, 1, 2040, 2040, 12, 24480),(480, 12014, 1, 1, '9/21/2011', '10/21/2011', 1, 1, 2038, 2038, 1, 2038),(480, 12014, 2, 2, '9/21/2011', '9/21/2012', 5, 8, 8.6, 68.8, 12, 891.65),(480, 12014, 3, 2, '9/21/2011', '9/21/2012', 5, 8, 13.23, 105.84, 12, 1371.69),(480, 12014, 5, 2, '9/21/2011', '9/21/2012', 5, 8, 19.45, 155.6, 12, 2016.58),(480, 12014, 8, 2, '9/21/2011', '9/21/2012', 3, 2, 236.7, 473.4, 12, 6135.26),(480, 12014, 9, 2, '9/21/2011', '9/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(480, 12014, 10, 2, '9/21/2011', '9/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(480, 12014, 11, 2, '9/21/2011', '9/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(481, 12014, 1, 1, '9/21/2012', '9/21/2013', 1, 1, 2040, 2040, 12, 24480),(481, 12014, 2, 2, '9/21/2012', '9/21/2013', 5, 8, 8.6, 68.8, 12, 891.65),(481, 12014, 3, 2, '9/21/2012', '9/21/2013', 5, 8, 13.23, 105.84, 12, 1371.69),(481, 12014, 5, 2, '9/21/2012', '9/21/2013', 5, 8, 19.45, 155.6, 12, 2016.58),(481, 12014, 8, 2, '9/21/2012', '9/21/2013', 3, 2, 236.7, 473.4, 12, 6135.26),(481, 12014, 9, 2, '9/21/2012', '9/21/2013', 5, 4, 19.55, 78.2, 12, 1013.47),(481, 12014, 10, 2, '9/21/2012', '9/21/2013', 5, 4, 19.55, 78.2, 12, 1013.47),(481, 12014, 11, 2, '9/21/2012', '9/21/2013', 5, 4, 19.55, 78.2, 12, 1013.47)[/code][b]B2H_SummaryForms TABLE[/b][code="sql"]CREATE TABLE B2H_SummaryForms ( CLT_NBR int, DOSStart date, DOSEnd date, BillableUnits int, RateCode int ); INSERT INTO B2H_SummaryForms VALUES (12014, '3/2/2012', '3/2/2012', 10, 1312),(12014, '3/30/2012', '3/30/2012', 4, 1312),(10178, '4/19/2012', '4/19/2012', 4, 1312),(12014, '5/9/2012', '5/9/2012', 0, 1300),(12014, '5/21/2012', '5/21/2012', 1, 1300),(10178, '6/1/2012', '6/1/2012', 0, 1300),(10178, '5/10/2012', '5/10/2012', 4, 1312),(12014, '6/19/2012', '6/19/2012', 2, 1319),(10178, '6/26/2012', '6/26/2012', 2, 1312),(12014, '7/27/2012', '7/27/2012', 1, 1300),(10178, '8/8/2012', '8/8/2012', 1, 1300),(12014, '7/13/2012', '7/13/2012', 0, 1300),(12014, '7/1/2012', '7/1/2012', 8, 1312),(10178, '7/2/2012', '7/2/2012', 0, 1300),(10178, '5/8/2012', '5/8/2012', 0, 1300),(12014, '5/10/2012', '5/10/2012', 4, 1312),(10178, '5/8/2012', '5/8/2012', 0, 1300),(10178, '4/2/2012', '4/2/2012', 0, 1300),(10178, '3/8/2012', '3/8/2012', 1, 1312),(10178, '5/10/2012', '5/10/2012', 14, 1319),(12014, '4/10/2012', '4/10/2012', 1, 1300),(12014, '2/29/2012', '2/29/2012', 2, 1312),(12014, '3/5/2012', '3/5/2012', 0, 1300),(10178, '2/7/2012', '2/7/2012', 0, 1300),(10178, '2/15/2012', '2/15/2012', 1, 1300),(10178, '2/29/2012', '2/29/2012', 4, 1312),(10178, '3/8/2012', '3/8/2012', 0, 1300),(10178, '5/10/2012', '5/10/2012', 4, 1312),(12014, '3/2/2012', '3/2/2012', 10, 1312),(10178, '3/15/2012', '3/15/2012', 1, 1300),(12014,'3/30/2012', '3/30/2012', 1, 1300),(10178, '4/4/2012', '4/4/2012', 4, 1312),(12014, '4/5/2012', '4/5/2012', 0, 1300),(10178, '4/19/2012', '4/19/2012', 10, 1319),(10178, '4/10/2012', '4/10/2012', 1, 1300),(10178, '5/17/2012', '5/17/2012', 1, 1300),(10178, '6/6/2012', '6/6/2012', 1, 1300),(12014, '6/19/2012', '6/19/2012', 2, 1312),(12014, '6/19/2012', '6/19/2012', 2, 1319),(10178, '7/23/2012', '7/23/2012', 1, 1300),(12014, '8/20/2012', '8/20/2012', 0, 1300),(10178, '8/25/2012', '8/25/2012', 28, 1309),(10178, '9/5/2012', '9/5/2012', 0, 1300),(10178, '8/25/2012', '8/25/2012', 55, 1309),(12014, '9/17/2012', '9/17/2012', 1, 1300),(12014, '9/14/2012', '9/14/2012', 0, 1300),(12014, '9/1/2012', '9/1/2012', 1, 1318),(12014, '8/31/2012', '8/31/2012', 1, 1300),(10178, '8/1/2012', '8/1/2012', 0, 1300),(12014, '5/10/2012', '5/10/2012', 4, 1312),(12014, '6/19/2012', '6/19/2012', 2, 1312),(12014, '6/19/2012', '6/20/2012', 1, 1300),(12014, '5/10/2012', '5/10/2012', 4, 1312),(12014, '6/11/2012', '6/11/2012', 0, 1300),(12014, '4/4/2012', '4/4/2012', 2, 1312),(12014, '2/22/2012', '2/22/2012', 1, 1300),(12014, '3/13/2012', '3/13/2012', 4, 1312),(12014, '3/20/2012', '3/20/2012', 1, 1312),(12014, '2/7/2012', '2/7/2012', 0, 1300),(12014, '9/15/2012', '9/15/2012', 1, 1318),(12014, '9/21/2012', '9/21/2012', 4, 1312),(10178, '9/13/2012', '9/13/2012', 1, 1300),(12014, '9/22/2012', '9/22/2012', 1, 1318),(10178, '1/8/2013', '1/8/2013', 1, 1300),(12014, '9/21/2012', '9/21/2012', 4, 1312),(12014, '10/5/2012', '10/5/2012', 0, 1300),(12014, '10/8/2012', '10/8/2012', 1, 1300),(10178, '10/10/2012', '10/10/2012', 1, 1300),(10178, '10/2/2012', '10/2/2012', 0, 1300),(12014, '10/6/2012', '10/6/2012', 16, 1308),(10178, '10/19/2012', '10/19/2012', 1, 1318),(10178, '10/20/2012', '10/20/2012', 16, 1319),(10178, '10/20/2012', '10/20/2012', 16, 1319),(10178, '10/19/2012', '10/19/2012', 1, 1318),(10178, '11/5/2012', '11/5/2012', 0, 1300),(12014, '11/6/2012', '11/6/2012', 0, 1300),(12014, '9/28/2012', '9/28/2012', 16, 1308),(12014, '10/27/2012', '10/27/2012', 16, 1308),(12014, '11/9/2012', '11/9/2012', 1, 1300),(12014, '10/15/2012', '10/15/2012', 4, 1312),(12014, '10/6/2012', '10/6/2012', 16, 1308),(12014, '9/28/2012', '9/28/2012', 16, 1308),(12014, '10/27/2012', '10/27/2012', 16, 1308),(12014, '11/10/2012', '11/10/2012', 16, 1308),(10178, '11/12/2012', '11/12/2012', 1, 1300),(12014, '11/10/2012', '11/10/2012', 16, 1308),(12014, '11/17/2012', '11/17/2012', 12, 1308),(12014, '10/15/2012', '10/15/2012', 4, 1312),(12014, '11/16/2012', '11/16/2012', 4, 1312),(12014, '11/24/2012', '11/24/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '11/17/2012', '11/17/2012', 12, 1308),(12014, '11/24/2012', '11/24/2012', 16, 1308),(12014, '12/1/2012', '12/1/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(10178, '12/3/2012', '12/3/2012', 0, 1300),(12014, '11/24/2012', '11/24/2012', 16, 1308),(12014, '12/1/2012', '12/1/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '12/17/2012', '12/17/2012', 0, 1300),(10178, '12/10/2012', '12/10/2012', 1, 1300),(10178, '12/10/2012', '12/19/2012', 1, 1300),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '12/21/2012', '12/21/2012', 1, 1300),(12014, '12/22/2012', '12/22/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '1/5/2013', '1/5/2013', 1, 1318),(10178, '1/8/2013', '1/8/2013', 1, 1300),(10178, '1/7/2013', '1/7/2013', 0, 1300),(10178, '1/7/2013', '1/7/2013', 0, 1300),(10178, '5/8/2012', '5/8/2012', 0, 1300),(12014, '1/23/2013', '1/23/2013', 0, 1300),(12014, '1/25/2013', '1/25/2013', 1, 1300),(10178, '2/4/2013', '2/4/2013', 0, 1300),(12014, '2/2/2013', '2/2/2013', 1, 1318),(12014,'2/11/2013', '2/11/2013', 0, 1300),(12014, '2/19/2013', '2/19/2013', 1, 1300),(10178, '2/19/2013', '2/19/2013', 1, 1300),(12014,'2/23/2013', '2/23/2013', 8, 1308),(10178, '3/4/2013', '3/4/2013', 0, 1300),(12014, '3/9/2013', '3/9/2013', 1, 1318),(12014, '3/9/2013', '3/9/2013', 1, 1318),(12014, '3/7/2013', '3/7/2013', 0, 1300),(12014, '3/15/2013', '3/15/2013', 1, 1300),(10178, '3/12/2013', '3/12/2013', 8, 1306),(10178, '3/13/2013', '3/13/2013', 3, 1312),(10178, '3/12/2013', '3/12/2013', 1, 1300),(10178, '3/21/2013', '3/21/2013', 2, 1308),(10178, '3/21/2013', '3/21/2013', 4, 1319),(10178, '3/20/2013', '3/20/2013', 2, 1312),(10178, '3/23/2013', '3/23/2013', 1, 1318),(12014, '4/2/2013', '4/2/2013', 0, 1300),(10178, '4/2/2013', '4/2/2013', 0, 1300),(12014, '4/6/2013', '4/6/2013', 1, 1318),(10178, '4/5/2013', '4/5/2013', 1, 1300),(10178, '4/10/2013', '4/10/2013', 4, 1312),(10178, '4/11/2013', '4/11/2013', 6, 1308),(12014, '4/22/2013', '4/22/2013', 1, 1300),(10178, '5/2/2013', '5/2/2013', 0, 1300),(10178, '5/2/2013', '5/2/2013', 6, 1319),(10178, '5/4/2013', '5/4/2013', 1, 1318),(10178, '5/16/2013', '5/16/2013', 1, 1300),(10178, '5/14/2013', '5/14/2013', 6, 1312),(12014, '5/17/2013', '5/17/2013', 0, 1300),(12014, '5/20/2013', '5/20/2013', 1, 1300),(10178, '6/6/2013', '6/6/2013', 0, 1300),(10178, '6/10/2013', '6/10/2013', 1, 1300),(10178, '6/1/2013', '6/1/2013', 1, 1318),(12014, '6/7/2013', '6/7/2013', 0, 1300),(12014, '6/17/2013', '6/17/2013', 1, 1300),(12014, '6/19/2013', '6/19/2013', 4, 1308),(12014, '6/29/2013', '6/29/2013', 1, 1318),(12014, '6/29/2013', '6/29/2013', 8, 1308),(10178, '7/10/2013', '7/10/2013', 0, 1300),(12014, '7/11/2013', '7/11/2013', 0, 1300),(12014, '7/3/2013', '7/3/2013', 4, 1308),(12014, '7/13/2013', '7/13/2013', 1, 1318),(10178, '7/24/2013', '7/24/2013', 1, 1300)[/code][b]B2H_RATE [/b] [code="sql"]CREATE TABLE B2H_RATE ( RATE_CODE int, WAIVER_SERVICES date, RATE_AMOUNT date, BILLABLE_UNIT int);(1300, 1, 2040, 1),(1301, 1, 2038, 1),(1302, 1, 1020, 2),(1303, 1, 1020, 2),(1304, 1, 2040, 1),(1305, 1, 2040, 1),(1306, 2, 13.23, 5),(1307, 2, 8.6, 5),,(1308, 3, 13.23, 5),(1309, 3, 8.6, 5),(1310, 4, 77, 4),(1311, 4, 40.85, 4),(1312, 5, 19.45, 5),(1313, 5, 12.64, 5),(1314, 6, 55.68, 4),(1315, 6, 30.19, 4),(1316, 7, 70, 4),(1317, 8, 13.23, 6),(1318, 8, 236.7, 3),(1319, 9, 19.55, 5),(1320, 9, 12.7, 5),(1321, 10, 19.55, 5),(1322, 11, 19.55, 5),(1323, 12, 17.59, 6),(1324, 12, 304.5, 3),(1327, 1, 2040, 1),(1328, 1, 2038, 1),(1329, 1, 1020, 2),(1330, 1, 1020, 2)(1331, 1, 2040, 1),(1332, 1, 2040, 1),(1333, 2, 13.23, 5),(1334, 2, 8.6, 5),(1335, 3, 13.23, 5),(1336, 3, 8.6, 5),(1337, 4, 77, 4),(1338, 4, 40.85, 4),(1339, 5, 19.45, 5),(1340, 5, 12.64, 5),(1341, 6, 55.68, 4)(1342, 6, 30.19, 4),(1343, 7, 70, 4),(1344, 8, 13.23, 6),(1345, 8, 236.7, 3),(1346, 9, 19.55, 5),(1347, 9, 12.7, 5),(1348, 10, 19.55, 5),(1349, 11, 19.55, 5),(1350, 12, 17.59, 6),(1351, 12, 304.5, 3),(1354, 1, 2040, 1),(1355, 1, 2038, 1),(1356, 1, 1020, 2),(1357, 1, 1020, 2),(1358, 1, 2040, 1),(1359, 1, 2040, 1),(1360, 2, 13.23, 5),(1361, 2, 8.6, 5),(1362, 3, 13.23, 5),(1363, 3, 8.6, 5),(1364, 4, 77, 4),(1365, 4, 40.85, 4),(1366, 5, 19.45, 5),(1367, 5, 12.64, 5),(1368, 6, 55.68, 4),(1369, 6, 30.19, 4),(1370, 7, 70, 4),(1371, 8, 17.3, 6),(1372, 8, 309.42, 3),(1373, 9, 19.55, 5),(1374, 9, 12.7, 5),(1375, 10, 19.55, 5),(1376, 11, 19.55, 5),(1377, 12, 23.94, 6),(1378, 12, 414.48, 3)[/code]This is what I have so far. I've been trying but I can't figure out how to get the output above. [b]Budgeted [/b][code="sql"]SELECT ChildID, WAIVER_SERVICES, START_DT, END_DT, RATE_PER_UNIT_OF_SERVICE, PROJECTED_COST_PER_MONTHFROM [ECMS_BACKUP].[dbo].[B2H_DSP]where WAIVER_PROGRAM=2[/code]The budgets can be renewed once a year So the Start_Dt and End_dt are always a year long. This makes it tricky to break down the budget amount by month. [b]Services Provided[/b][code="sql"]select a.CLT_NBR, b.WAIVER_SERVICES, month(a.DOSStart) as Month, YEAR(a.DOSStart) as Year, sum(b.RATE_AMOUNT)as totalfrom [ECMS_BACKUP].[dbo].[B2H_SummaryForms]aleft JOIN ECMS_BACKUP.dbo.B2H_RATE b ON a.RateCode=b.RATE_CODEwhere DOSStart>='2013-01-01' AND DOSStart<'2013-06-30' /*I selected a 6th month time frame for services provided */group by ChildID, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICESorder by childid, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES[/code]I hope I was able to articulate this better. Any assistance is appreciated. Thanks. :-D
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog