Joining two tables: Provided/Budget

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining two tables: Provided/Budget

Author

Topic

mgarret
Starting Member

14 Posts

Posted - 2013-08-09 : 14:02:50

I have written two programs; One which sum the total cost for services provided and one which pulls in the projected budgets for the services. The budgets can be renewed once a year or every six months. I’m having trouble joining to the programs together so I can calculate the % of budgets used for each waver service by month.

Projected Budget Program

SELECT 
ChildID,
WAIVER_SERVICES,
START_DT,
END_DT,
RATE_PER_UNIT_OF_SERVICE,
PROJECTED_COST_PER_MONTH
FROM [ECMS_BACKUP].[dbo].[B2H_DSP]
where CLT_NBR=10177
and WAIVER_SERVICES NOT IN (1,10,11,12) 
and WAIVER_PROGRAM=2

The Budget data set I bring back looks something like this:

CREATE TABLE Budget  (
    ChildID int,
   WAIVER_SERVICES      int,
    START_DT date, 
    END_DT date,
    RATE_PER_UNIT_OF_SERVICE int(30),
    PROJECTED_COST_PER_MONTH 
    ); 
INSERT INTO Budget VALUES 

(10177, 2,  '06/01/12', '06/01/13', 13.23,  158.76),
(10177, 3,  '06/01/12', '06/01/13', 13.23,  211.68),
(10177, 5,  '06/01/12', '06/01/13', 19.45,  466.8),
(10177, 8,  '06/01/12', '06/01/13', 236.7,  473.4),
(10177, 9,  '06/01/12', '06/01/13', 19.55,  312.8),
(10177, 2,  '06/03/13', '06/03/14', 13.23,  158.76),
(10177, 3,  '06/03/13', '06/03/14', 13.23,  211.68),
(10177, 5,  '06/03/13', '06/03/14', 19.45,  466.8),
(10177, 6,  '06/03/13', '06/03/14', 55.68,  222.72),
(10177, 8,  '06/03/13', '06/03/14', 236.7,  473.4),
(10177, 9,  '06/03/13', '06/03/14', 19.55,  312.8)

Notice the START_DT and END_DT are not set set monthly but yearly.

Services Provided Program

select 
a.ChildID,
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'
group by ChildID, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES
order by childid,  month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES

The Services Provided data set I bring back looks something like this:

CREATE TABLE Provided  (
   ChildID int,
   WAIVER_SERVICES      int,
   Month int, 
    Year int,
   total  int,
    ); 
INSERT INTO Provided VALUES 
('10177',   3,  3,  2013,   13.23),
('10177',   5,  3,  2013,   77.8),
('10177',   8,  3,  2013,   236.7),
('10177',   9,  3,  2013,   19.55),
('10177',   3,  4,  2013,   13.23),
('10177',   5,  4,  2013,   19.45),
('10177',   5,  5,  2013,   19.45),
('10177',   8,  5,  2013,   236.7),
('10177',   9,  5,  2013,   19.55),
('10177',   5,  6,  2013,   19.45),
('10177',   8,  6,  2013,   236.7),
('10177',   3,  8,  2012,   17.2),
('10177',   3,  10, 2012,   13.23),
('10177',   8,  10, 2012,   473.4),
('10177',   9,  10, 2012,   19.55),
('10177',   2,  11, 2012,   13.23),
('10177',   3,  11, 2012,   13.23)

Again, I am have trouble joining these two data sets to get something that looks like this

ChildID WAIVER_SERVICES Month/Year services_provided Budget % used 10177 3 12/2012 125 125 100% 10177 3 1/2013 75 125 60%
10177 3 2/2013 100 175 57% 10177 9 1/2013 50 50 100%
10177 9 5/2013 800 950 84%

Any assistance is greatly appreciated!

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-08-10 : 15:38:48

hi
i created your two tables

CREATE TABLE Budget (
ChildID int,
WAIVER_SERVICES int,
START_DT datetime,
END_DT datetime,
RATE_PER_UNIT_OF_SERVICE int,
PROJECTED_COST_PER_MONTH int
);
INSERT INTO Budget VALUES

(10177, 2, '06/01/12', '06/01/13', 13.23, 158.76)
INSERT INTO Budget VALUES
(10177, 3, '06/01/12', '06/01/13', 13.23, 211.68)
INSERT INTO Budget VALUES
(10177, 5, '06/01/12', '06/01/13', 19.45, 466.8)
INSERT INTO Budget VALUES
(10177, 8, '06/01/12', '06/01/13', 236.7, 473.4)
INSERT INTO Budget VALUES
(10177, 9, '06/01/12', '06/01/13', 19.55, 312.8)
INSERT INTO Budget VALUES
(10177, 2, '06/03/13', '06/03/14', 13.23, 158.76)
INSERT INTO Budget VALUES
(10177, 3, '06/03/13', '06/03/14', 13.23, 211.68)
INSERT INTO Budget VALUES
(10177, 5, '06/03/13', '06/03/14', 19.45, 466.8)
INSERT INTO Budget VALUES
(10177, 6, '06/03/13', '06/03/14', 55.68, 222.72)
INSERT INTO Budget VALUES
(10177, 8, '06/03/13', '06/03/14', 236.7, 473.4)
INSERT INTO Budget VALUES
(10177, 9, '06/03/13', '06/03/14', 19.55, 312.8)

CREATE TABLE Provided (
ChildID int,
WAIVER_SERVICES int,
Month int,
Year int,
total int,
);
INSERT INTO Provided VALUES
('10177', 3, 3, 2013, 13.23)
INSERT INTO Provided VALUES
('10177', 5, 3, 2013, 77.8)
INSERT INTO Provided VALUES
('10177', 8, 3, 2013, 236.7)
INSERT INTO Provided VALUES
('10177', 9, 3, 2013, 19.55)
INSERT INTO Provided VALUES
('10177', 3, 4, 2013, 13.23)
INSERT INTO Provided VALUES
('10177', 5, 4, 2013, 19.45)
INSERT INTO Provided VALUES
('10177', 5, 5, 2013, 19.45)
INSERT INTO Provided VALUES
('10177', 8, 5, 2013, 236.7)
INSERT INTO Provided VALUES
('10177', 9, 5, 2013, 19.55)
INSERT INTO Provided VALUES
('10177', 5, 6, 2013, 19.45)
INSERT INTO Provided VALUES
('10177', 8, 6, 2013, 236.7)
INSERT INTO Provided VALUES
('10177', 3, 8, 2012, 17.2)
INSERT INTO Provided VALUES
('10177', 3, 10, 2012, 13.23)
INSERT INTO Provided VALUES
('10177', 8, 10, 2012, 473.4)
INSERT INTO Provided VALUES
('10177', 9, 10, 2012, 19.55)
INSERT INTO Provided VALUES
('10177', 2, 11, 2012, 13.23)
INSERT INTO Provided VALUES
('10177', 3, 11, 2012, 13.23)

then what you need

http://sql-az.tr.gg/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources