August 11, 2013 at 11:14 am
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:
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%
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 B2H_DSP, B2H_SummaryForms and B2H_RATE . Note the B2H_Rate table is a reference table which pulls the cost of the wavier_services.
B2H_DSP TABLE
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)
B2H_SummaryForms TABLE
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)
B2H_RATE
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)
This is what I have so far. I've been trying but I can't figure out how to get the output above.
Budgeted
SELECT
ChildID,
WAIVER_SERVICES,
START_DT,
END_DT,
RATE_PER_UNIT_OF_SERVICE,
PROJECTED_COST_PER_MONTH
FROM [ECMS_BACKUP].[dbo].[B2H_DSP]
where WAIVER_PROGRAM=2
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.
Services Provided
select
a.CLT_NBR,
b.WAIVER_SERVICES,
month(a.DOSStart) as Month,
YEAR(a.DOSStart) as Year,
sum(b.RATE_AMOUNT)as total
from
[ECMS_BACKUP].[dbo].[B2H_SummaryForms]a
left JOIN ECMS_BACKUP.dbo.B2H_RATE b ON a.RateCode=b.RATE_CODE
where 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_SERVICES
order by childid, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES
I hope I was able to articulate this better. Any assistance is appreciated. Thanks. 😀
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply