Hi there! 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 . Notes the B2H_Rate table is a reference table which pulls the cost of the wavier_services.
I need the total amount of each WAIVER_SERVICE that was budgeted, the total amount for each wavier service that was provided and the % Budget Used. Grouped by WAIVER_SERVICES and Month/Year
Again, the final out put I am looking for is something 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 1000 1100 90%
10178 2 2 2013 2535 2000 126%
10178 3 10 2012 353 400 88%
10178 3 11 2012 555 555 100%
10178 3 12 2012 555 560 99%
Again yes if a client was budgeted a service and did not revice it for a month a 0 value is needed. Or I need to display 0's for months with no data.
Your assistance is greatly appreciated!!!!
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_MONTHint,
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 TABLE
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)