Posted: 09 Aug 2013 05:12 AM PDT

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[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 CLT_NBR=10177and WAIVER_SERVICES NOT IN (1,10,11,12) and WAIVER_PROGRAM=2[/code]The Budget data set I bring back looks something like this:[code="sql"]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)[/code]Notice the START_DT and END_DT are not set set monthly but yearly.Services Provided Program[code="sql"]select a.ChildID,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'group by ChildID, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICESorder by childid, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES[/code]The Services Provided data set I bring back looks something like this:[code="sql"]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)[/code]Again, I am have trouble joining these two data sets to get something that looks like thisChildID 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!

Posted: 09 Aug 2013 02:24 AM PDT

Question: when creating a failover cluster I made the mistake of selecting available storage which had all the drives under it. When I tried to install the second instance i didn't have any storage options. I believe that when I get to the step of cluster groups i should have group options of dtc, group 0 and group 2 which has separate data drives for instances. Is this correct?Secondly I thought when the virtual server is created it would appear in the cluster manager, What makes visible? When it was there before the install said invalid name since was already there.Confused??

SSIS SQL - to AD update question

Posted: 09 Aug 2013 12:57 AM PDT

fixed

Sql samples, sql tips

Friday, August 9, 2013

[SQL Server] Joining two tables: Provided/Budget

[SQL Server] Joining two tables: Provided/Budget

No comments:

Post a Comment

Search This Blog