SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Joining two tables: Provided/Budget


Joining two tables: Provided/Budget

Author
Message
Rick44
Rick44
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 107
Hi all. 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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62239 Visits: 17954
Awesome job posting ddl and sample data. I did make a few modifications to make your datatypes numeric(9,2) for RATE_PER_UNIT_OF_SERVICE, PROJECTED_COST_PER_MONTH and total. You had them all as ints so the calculations would have been skewed.

What I don't understand though is the desired output. Can you explain the logic and what the values represent? I think you are going to need a cross tab and maybe a calendar table. You can links in my signature to cross tabs and calendar tables you can find using the site search.

If you can explain the logic for the output I will be happy to help.

_______________________________________________________________

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 Modens 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)
Rick44
Rick44
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 107
Hi there. Thank you for getting back to me. I basically need to combine the two above programs about so I get the amount budgeted for each waver service by month, the amount actually provided for each wavier service by month and then the % provided.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62239 Visits: 17954
matt_garretson (8/9/2013)
Hi there. Thank you for getting back to me. I basically need to combine the two above programs about so I get the amount budgeted for each waver service by month, the amount actually provided for each wavier service by month and then the % provided.


OK that is what I was thinking. What about months that have no data? Do you want them to appear with zeros or not be present?

If at all possible can you post your desired output with column headers so it is clear what the values should be?

_______________________________________________________________

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 Modens 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)
Rick44
Rick44
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 107
Man, you are good. Yes, 0's for those missing months.

So

 ChildID  Waver_Service  Month/Year  Total_Amount_Provided Total_Amount_Budgeted Budget_%_use 


Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62239 Visits: 17954
matt_garretson (8/9/2013)
Man, you are good. Yes, 0's for those missing months.

So

 ChildID  Waver_Service  Month/Year  Total_Amount_Provided Total_Amount_Budgeted Budget_%_use 



cool. What i was really hoping for is maybe a temp table or something so I can match up your desired output with the values you posted.

_______________________________________________________________

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 Modens 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)
Rick44
Rick44
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 107
See message below
LinksUp
LinksUp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 4640

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%




Looking at the last line of your desired output you have 800 for services_provided and 950 for Budget.
Using the tables and values you provided, how did you arrive at these 2 numbers?

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Rick44
Rick44
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 107
Hi that was just an example. It had no relation
Rick44
Rick44
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 107
The calculations were made up because I did not know how to combine the two programs correctly. its just an example of the final product
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search