Stored Procedure?? - SQL Server Forums

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

Stored Procedure??

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-20 : 14:28:15

Hi -

I have 4 separate queries that create projections for 4 different programs. I use a fifth query to union all of them so that I can have one projection for all programs. Is this the best way to do this or should I be using a stored procedure?

Any help would be greatly appreciated!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-20 : 15:21:53

Well I'm of the opinion that ALL data access should be in stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-20 : 15:52:34

I don't even know where to start. Can you please help me? I have never worked with an sp.

quote:
Originally posted by tkizer

Well I'm of the opinion that ALL data access should be in stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-20 : 15:57:34

A stored procedure is no different than other T-SQL stuff that you'd write. Show us what you have as the solution without the stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-21 : 10:23:12

Why create a stored procedure? What if the tase can be accomplished by several queries?

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-21 : 10:26:04

post the query

I'm assuming you passing the sql in from some language...what is it...and are you using sql server?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 10:51:38

Nigel gave you good answers when you asked the exact same question in another thread. What is the answer that you are looking for?

Jim

Everyday I learn something that somebody else already knew

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-21 : 11:27:38

Here it is:

/*****************************************************************************************************
Consolidated 12 period Member Month Projection
based on Business Development membership forecast revised 9/15/2010
*****************************************************************************************************/

--Extract total mms from DW.
--select starting point for mms projection (where clause)

use dw

select ReportingMonth as Date
, SponsorId
, PolicyNumber
, Program
, RateCode
, BenefitPlan
, PhysicalRegion
, case
when (pcpnetworkid) = '2' then 'HVMA' else 'NonHVMA' end
as Network
, MemberMonths

into ##tempmms

from MemberMonths

where year(reportingmonth) = '2010' and month(reportingmonth) = '12' --starting point

--MASSHEALTH
--Extract masshealth mms starting point from ##tempmms
use dw
select Date
, case
when RateCode like '%RC1%' then 'RC1'
when RateCode like '%RC2%' then 'RC2'
when RateCode like '%RC3%' then 'RC3'
when RateCode like '%RC4%' then 'RC4'
when RateCode like '%RC5%' then 'RC5'
when RateCode like '%RC6%' then 'RC6'
when RateCode like '%RC1%' then 'RC1'
when RateCode like '%RC7%' then 'RC7'
else RateCode end as RC
, case
when PhysicalRegion like 'Greater Boston%' then 'Greater Boston'
when PhysicalRegion like 'Northern%' then 'Northern'
when PhysicalRegion like 'Southern%' then 'Southern'
when PhysicalRegion like 'Western%' then 'Western'
when PhysicalRegion like 'Central%' then 'Central'
when PhysicalRegion like '' then 'Greater Boston' --blanks to GB
end as Region
, Network
, MemberMonths

into ##tempmms_MH

from ##tempmms

where sponsorid = '100002'

--##temp_mh_start is actual mms from the dw and is the starting point for this projection:
use dw
select convert(varchar(10), Date, 101) as StartingDate
, RC
, sum(membermonths) as mm

into ##temp_mh_start

from ##tempmms_MH

group by Date, RC

order by RC
--Import monthly increase file (last page of BD forecast) for use in sql.
--dbo.MassHealth2011$ on NHPUserReporting
--Create individual period forecasts for MH
--Period 1
use nhpuserreporting
declare @period1MH datetime
set @period1MH = '1/1/2011'
select @period1MH as Period
, s.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per1
from ##temp_mh_start s left join MassHealth2011$ g on s.rc = g.rc
--Period 2
use nhpuserreporting
declare @period2MH datetime
set @period2MH = '2/1/2011'
select @period2MH as Period
, P1.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per2
from ##tempMH_per1 P1 left join MassHealth2011$ g on P1.rc = g.rc
--Period 3
use nhpuserreporting
declare @period3MH datetime
set @period3MH = '3/1/2011'
select @period3MH as Period
, P2.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per3
from ##tempMH_per2 P2 left join MassHealth2011$ g on P2.rc = g.rc
--Period 4
use nhpuserreporting
declare @period4MH datetime
set @period4MH = '4/1/2011'
select @period4MH as Period
, P3.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per4
from ##tempMH_per3 P3 left join MassHealth2011$ g on P3.rc = g.rc
--Period 5
use nhpuserreporting
declare @period5MH datetime
set @period5MH = '5/1/2011'
select @period5MH as Period
, P4.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per5
from ##tempMH_per4 P4 left join MassHealth2011$ g on P4.rc = g.rc
--Period 6
use nhpuserreporting
declare @period6MH datetime
set @period6MH = '6/1/2011'
select @period6MH as Period
, P5.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per6
from ##tempMH_per5 P5 left join MassHealth2011$ g on P5.rc = g.rc
--Period 7
use nhpuserreporting
declare @period7MH datetime
set @period7MH = '7/1/2011'
select @period7MH as Period
, P6.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per7
from ##tempMH_per6 P6 left join MassHealth2011$ g on P6.rc = g.rc
--Period 8
use nhpuserreporting
declare @period8MH datetime
set @period8MH = '8/1/2011'
select @period8MH as Period
, P7.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per8
from ##tempMH_per7 P7 left join MassHealth2011$ g on P7.rc = g.rc
--Period 9
use nhpuserreporting
declare @period9MH datetime
set @period9MH = '9/1/2011'
select @period9MH as Period
, P8.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per9
from ##tempMH_per8 P8 left join MassHealth2011$ g on P8.rc = g.rc
--Period 10
use nhpuserreporting
declare @period10MH datetime
set @period10MH = '10/1/2011'
select @period10MH as Period
, P9.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per10
from ##tempMH_per9 P9 left join MassHealth2011$ g on P9.rc = g.rc
--Period 11
use nhpuserreporting
declare @period11MH datetime
set @period11MH = '11/1/2011'
select @period11MH as Period
, P10.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per11
from ##tempMH_per10 P10 left join MassHealth2011$ g on P10.rc = g.rc
--Period 12
use nhpuserreporting
declare @period12MH datetime
set @period12MH = '12/1/2011'
select @period12MH as Period
, P11.RC
, Mm = (mm+monthlygrowth)
into ##tempMH_per12
from ##tempMH_per11 P11 left join MassHealth2011$ g on P11.rc = g.rc
--Commonwealth CARE
--Extract CCARE mms starting point from ##tempmms
use dw
select Date
, SponsorId
, case
when PolicyNumber like '%FA%' then 'PT1'
when PolicyNumber like '%FB%' then 'PT2'
when PolicyNumber like '%FC%' then 'PT3'
end as PlanType
, case
when BenefitPlan = '' then 'NHP_COMMONWEALTH CARE I' else BenefitPlan
end as BenefitPlan
, case
when PhysicalRegion like 'Greater Boston%' then 'Greater Boston'
when PhysicalRegion like 'Northern%' then 'Northern'
when PhysicalRegion like 'Southern%' then 'Southern'
when PhysicalRegion like 'Western%' then 'Western'
when PhysicalRegion like 'Central%' then 'Central'
when PhysicalRegion like '' then 'Greater Boston' --blanks to GB
end as MemberPhysicalReg
, Network
, MemberMonths

into ##tempmms_CARE

from ##tempmms

where sponsorid = '100003'
--##temp_CARE_start is actual mms at 12/2010 from the dw and is the starting point for this projection:
use dw
select convert(varchar(10), Date, 101) as StartingDate
, PlanType
, sum(membermonths) as mm

into ##temp_CARE_start

from ##tempmms_CARE

group by Date, PlanType

order by PlanType

--Import monthly increase file (last page of BD forecast) for use in sql.
--dbo.CCARE2011$ on NHPUserReporting
--Create individual period forecasts for CCARE
--Period 1
use nhpuserreporting
declare @period1CARE datetime
set @period1CARE = '1/1/2011'
select @period1CARE as Period
, s.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per1
from ##temp_CARE_start s left join CCARE2011$ g on s.PlanType = g.PlanType
--Period 2
use nhpuserreporting
declare @period2CARE datetime
set @period2CARE = '2/1/2011'
select @period2CARE as Period
, P1.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per2
from ##tempCARE_per1 P1 left join CCARE2011$ g on P1.PlanType = g.PlanType
--Period 3
use nhpuserreporting
declare @period3CARE datetime
set @period3CARE = '3/1/2011'
select @period3CARE as Period
, P2.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per3
from ##tempCARE_per2 P2 left join CCARE2011$ g on P2.PlanType = g.PlanType
--Period 4
use nhpuserreporting
declare @period4CARE datetime
set @period4CARE = '4/1/2011'
select @period4CARE as Period
, P3.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per4
from ##tempCARE_per3 P3 left join CCARE2011$ g on P3.PlanType = g.PlanType
--Period 5
use nhpuserreporting
declare @period5CARE datetime
set @period5CARE = '5/1/2011'
select @period5CARE as Period
, P4.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per5
from ##tempCARE_per4 P4 left join CCARE2011$ g on P4.PlanType = g.PlanType
--Period 6
use nhpuserreporting
declare @period6CARE datetime
set @period6CARE = '6/1/2011'
select @period6CARE as Period
, P5.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per6
from ##tempCARE_per5 P5 left join CCARE2011$ g on P5.PlanType = g.PlanType
--Period 7
use nhpuserreporting
declare @period7CARE datetime
set @period7CARE = '7/1/2011'
select @period7CARE as Period
, P6.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per7
from ##tempCARE_per6 P6 left join CCARE2011$ g on P6.PlanType = g.PlanType
--Period 8
use nhpuserreporting
declare @period8CARE datetime
set @period8CARE = '8/1/2011'
select @period8CARE as Period
, P7.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per8
from ##tempCARE_per7 P7 left join CCARE2011$ g on P7.PlanType = g.PlanType
--Period 9
use nhpuserreporting
declare @period9CARE datetime
set @period9CARE = '9/1/2011'
select @period9CARE as Period
, P8.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per9
from ##tempCARE_per8 P8 left join CCARE2011$ g on P8.PlanType = g.PlanType
--Period 10
use nhpuserreporting
declare @period10CARE datetime
set @period10CARE = '10/1/2011'
select @period10CARE as Period
, P9.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per10
from ##tempCARE_per9 P9 left join CCARE2011$ g on P9.PlanType = g.PlanType
--Period 11
use nhpuserreporting
declare @period11CARE datetime
set @period11CARE = '11/1/2011'
select @period11CARE as Period
, P10.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per11
from ##tempCARE_per10 P10 left join CCARE2011$ g on P10.PlanType = g.PlanType
--Period 12
use nhpuserreporting
declare @period12CARE datetime
set @period12CARE = '12/1/2011'
select @period12CARE as Period
, P11.PlanType
, Mm = (mm+monthlygrowth)
into ##tempCARE_per12
from ##tempCARE_per11 P11 left join CCARE2011$ g on P11.PlanType = g.PlanType

--Commonwealth CHOICE
--Extract CCHOICE mms starting point from ##tempmms
use dw
select tm.Date
, tm.SponsorId
, tm.PolicyNumber
, case
when tm.RateCode = '** NO RATE CODE **' then 'Individual'
else tm.RateCode end as RateCode
, tm.BenefitPlan
, case
when tm.PhysicalRegion like 'Greater Boston%' then 'Greater Boston'
when tm.PhysicalRegion like 'Northern%' then 'Northern'
when tm.PhysicalRegion like 'Southern%' then 'Southern'
when tm.PhysicalRegion like 'Western%' then 'Western'
when tm.PhysicalRegion like 'Central%' then 'Central'
when tm.PhysicalRegion like '' then 'Greater Boston' --blanks to GB
end as MemberPhysicalReg
, case
when sd.PhysicalRegion like 'Greater Boston%' then 'Greater Boston'
when sd.PhysicalRegion like 'Northern%' then 'Northern'
when sd.PhysicalRegion like 'Southern%' then 'Southern'
when sd.PhysicalRegion like 'Western%' then 'Western'
when sd.PhysicalRegion like 'Central%' then 'Central'
when sd.PhysicalRegion like '' then 'Greater Boston' --blanks to GB
end as SponsorPhysicalReg
, tm.Program
, tm.Network
, tm.MemberMonths

into ##tempmms_CHOICE

from ##tempmms tm left join sponsordemographics sd on tm.sponsorid = sd.sponsorid

where tm.sponsorid = '100360'
-- ##temp_CHOICE_start is actual mms at 12/2010 from the dw and is the starting point for this projection:
use dw
select convert(varchar(10), Date, 101) as StartingDate
, Program
, sum(membermonths) as mm

into ##temp_CHOICE_start

from ##tempmms_CHOICE

group by Date, Program

--Import monthly increase file (last page of BD forecast) for use in sql.
--dbo.CCHOICE2011$ on NHPUserReporting
--Create individual period forecasts for CCHOICE
--Period 1
use nhpuserreporting
declare @period1CHOICE datetime
set @period1CHOICE = '1/1/2011'
select @period1CHOICE as Period
, s.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per1
from ##temp_CHOICE_start s left join CCHOICE2011$ g on s.program = g.program
--Period 2
use nhpuserreporting
declare @period2CHOICE datetime
set @period2CHOICE = '2/1/2011'
select @period2CHOICE as Period
, P1.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per2
from ##tempCHOICE_per1 P1 left join CCHOICE2011$ g on P1.program = g.program
--Period 3
use nhpuserreporting
declare @period3CHOICE datetime
set @period3CHOICE = '3/1/2011'
select @period3CHOICE as Period
, P2.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per3
from ##tempCHOICE_per2 P2 left join CCHOICE2011$ g on P2.program = g.program
--Period 4
use nhpuserreporting
declare @period4CHOICE datetime
set @period4CHOICE = '4/1/2011'
select @period4CHOICE as Period
, P3.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per4
from ##tempCHOICE_per3 P3 left join CCHOICE2011$ g on P3.Program = g.Program
--Period 5
use nhpuserreporting
declare @period5CHOICE datetime
set @period5CHOICE = '5/1/2011'
select @period5CHOICE as Period
, P4.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per5
from ##tempCHOICE_per4 P4 left join CCHOICE2011$ g on P4.Program = g.Program
--Period 6
use nhpuserreporting
declare @period6CHOICE datetime
set @period6CHOICE = '6/1/2011'
select @period6CHOICE as Period
, P5.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per6
from ##tempCHOICE_per5 P5 left join CCHOICE2011$ g on P5.Program = g.Program
--Period 7
use nhpuserreporting
declare @period7CHOICE datetime
set @period7CHOICE = '7/1/2011'
select @period7CHOICE as Period
, P6.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per7
from ##tempCHOICE_per6 P6 left join CCHOICE2011$ g on P6.Program = g.Program
--Period 8
use nhpuserreporting
declare @period8CHOICE datetime
set @period8CHOICE = '8/1/2011'
select @period8CHOICE as Period
, P7.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per8
from ##tempCHOICE_per7 P7 left join CCHOICE2011$ g on P7.Program = g.Program
--Period 9
use nhpuserreporting
declare @period9CHOICE datetime
set @period9CHOICE = '9/1/2011'
select @period9CHOICE as Period
, P8.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per9
from ##tempCHOICE_per8 P8 left join CCHOICE2011$ g on P8.Program = g.Program
--Period 10
use nhpuserreporting
declare @period10CHOICE datetime
set @period10CHOICE = '10/1/2011'
select @period10CHOICE as Period
, P9.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per10
from ##tempCHOICE_per9 P9 left join CCHOICE2011$ g on P9.Program = g.Program
--Period 11
use nhpuserreporting
declare @period11CHOICE datetime
set @period11CHOICE = '11/1/2011'
select @period11CHOICE as Period
, P10.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per11
from ##tempCHOICE_per10 P10 left join CCHOICE2011$ g on P10.Program = g.Program
--Period 12
use nhpuserreporting
declare @period12CHOICE datetime
set @period12CHOICE = '12/1/2011'
select @period12CHOICE as Period
, P11.Program
, Mm = (mm+monthlygrowth)
into ##tempCHOICE_per12
from ##tempCHOICE_per11 P11 left join CCHOICE2011$ g on P11.Program = g.Program

--Commercial
--Extract commercial mms starting point from ##tempmms
use dw
select tm.Date
, tm.SponsorId
, tm.PolicyNumber
, case
when tm.RateCode = '** NO RATE CODE **' then 'Individual'
else tm.RateCode end as RateCode
, tm.BenefitPlan
, case
when tm.PhysicalRegion like 'Greater Boston%' then 'Greater Boston'
when tm.PhysicalRegion like 'Northern%' then 'Northern'
when tm.PhysicalRegion like 'Southern%' then 'Southern'
when tm.PhysicalRegion like 'Western%' then 'Western'
when tm.PhysicalRegion like 'Central%' then 'Central'
when tm.PhysicalRegion like '' then 'Greater Boston' --blanks to GB
end as MemberPhysicalReg
, case
when sd.PhysicalRegion like 'Greater Boston%' then 'Greater Boston'
when sd.PhysicalRegion like 'Northern%' then 'Northern'
when sd.PhysicalRegion like 'Southern%' then 'Southern'
when sd.PhysicalRegion like 'Western%' then 'Western'
when sd.PhysicalRegion like 'Central%' then 'Central'
when sd.PhysicalRegion like '' then 'Greater Boston' --blanks to GB
end as SponsorPhysicalReg
, tm.Program
, tm.Network
, tm.MemberMonths

into ##tempmms_Comm

from ##tempmms tm left join sponsordemographics sd on tm.sponsorid = sd.sponsorid

where tm.sponsorid not in ('100002', '100003', '100360')
--##temp_Comm_start is actual mms at 12/2010 from the dw and is the starting point for this projection:
use dw
select convert(varchar(10), Date, 101) as StartingDate
, Program
, sum(membermonths) as mm

into ##temp_Comm_start

from ##tempmms_Comm

group by Date, Program

--Import monthly increase file (last page of BD forecast) for use in sql.
--dbo.Commercial2011$ on NHPUserReporting
--Create individual period forecasts for Commercial
--Period 1
use nhpuserreporting
declare @period1 datetime
set @period1 = '1/1/2011'
select @period1 as Period
, s.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per1
from ##temp_Comm_start s left join Commercial2011$ g on s.program = g.program
--Period 2
use nhpuserreporting
declare @period2 datetime
set @period2 = '2/1/2011'
select @period2 as Period
, P1.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per2
from ##tempComm_per1 P1 left join Commercial2011$ g on P1.program = g.program
--Period 3
use nhpuserreporting
declare @period3 datetime
set @period3 = '3/1/2011'
select @period3 as Period
, P2.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per3
from ##tempComm_per2 P2 left join Commercial2011$ g on P2.program = g.program
--Period 4
use nhpuserreporting
declare @period4 datetime
set @period4 = '4/1/2011'
select @period4 as Period
, P3.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per4
from ##tempComm_per3 P3 left join Commercial2011$ g on P3.Program = g.Program
--Period 5
use nhpuserreporting
declare @period5 datetime
set @period5 = '5/1/2011'
select @period5 as Period
, P4.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per5
from ##tempComm_per4 P4 left join Commercial2011$ g on P4.Program = g.Program
--Period 6
use nhpuserreporting
declare @period6 datetime
set @period6 = '6/1/2011'
select @period6 as Period
, P5.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per6
from ##tempComm_per5 P5 left join Commercial2011$ g on P5.Program = g.Program
--Period 7
use nhpuserreporting
declare @period7 datetime
set @period7 = '7/1/2011'
select @period7 as Period
, P6.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per7
from ##tempComm_per6 P6 left join Commercial2011$ g on P6.Program = g.Program
--Period 8
use nhpuserreporting
declare @period8 datetime
set @period8 = '8/1/2011'
select @period8 as Period
, P7.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per8
from ##tempComm_per7 P7 left join Commercial2011$ g on P7.Program = g.Program
--Period 9
use nhpuserreporting
declare @period9 datetime
set @period9 = '9/1/2011'
select @period9 as Period
, P8.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per9
from ##tempComm_per8 P8 left join Commercial2011$ g on P8.Program = g.Program
--Period 10
use nhpuserreporting
declare @period10 datetime
set @period10 = '10/1/2011'
select @period10 as Period
, P9.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per10
from ##tempComm_per9 P9 left join Commercial2011$ g on P9.Program = g.Program
--Period 11
use nhpuserreporting
declare @period11 datetime
set @period11 = '11/1/2011'
select @period11 as Period
, P10.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per11
from ##tempComm_per10 P10 left join Commercial2011$ g on P10.Program = g.Program
--Period 12
use nhpuserreporting
declare @period12 datetime
set @period12 = '12/1/2011'
select @period12 as Period
, P11.Program
, Mm = (mm+monthlygrowth)
into ##tempComm_per12
from ##tempComm_per11 P11 left join Commercial2011$ g on P11.Program = g.Program

--this consolidates mms into a 12 period forecast and reports it by plan type/rate code and program
use nhpuserreporting

--MassHealth
select * into ##temp12PerMMForecast
from ##tempMH_per1

union
select * from ##tempMH_per2
union
select * from ##tempMH_per3
union
select * from ##tempMH_per4
union
select * from ##tempMH_per5
union
select * from ##tempMH_per6
union
select * from ##tempMH_per7
union
select * from ##tempMH_per8
union
select * from ##tempMH_per9
union
select * from ##tempMH_per10
union
select * from ##tempMH_per11
union
select * from ##tempMH_per12
union
--CARE
select * from ##tempCARE_per1
union
select * from ##tempCARE_per2
union
select * from ##tempCARE_per3
union
select * from ##tempCARE_per4
union
select * from ##tempCARE_per5
union
select * from ##tempCARE_per6
union
select * from ##tempCARE_per7
union
select * from ##tempCARE_per8
union
select * from ##tempCARE_per9
union
select * from ##tempCARE_per10
union
select * from ##tempCARE_per11
union
select * from ##tempCARE_per12
--Commercial
union
select * from ##tempComm_per1
union
select * from ##tempComm_per2
union
select * from ##tempComm_per3
union
select * from ##tempComm_per4
union
select * from ##tempComm_per5
union
select * from ##tempComm_per6
union
select * from ##tempComm_per7
union
select * from ##tempComm_per8
union
select * from ##tempComm_per9
union
select * from ##tempComm_per10
union
select * from ##tempComm_per11
union
select * from ##tempComm_per12
--CHOICE
union
select * from ##tempCHOICE_per1
union
select * from ##tempCHOICE_per2
union
select * from ##tempCHOICE_per3
union
select * from ##tempCHOICE_per4
union
select * from ##tempCHOICE_per5
union
select * from ##tempCHOICE_per6
union
select * from ##tempCHOICE_per7
union
select * from ##tempCHOICE_per8
union
select * from ##tempCHOICE_per9
union
select * from ##tempCHOICE_per10
union
select * from ##tempCHOICE_per11
union
select * from ##tempCHOICE_per12
--final output
select convert(varchar(10), Period, 101) as Date
, RC
, case
when RC = 'CCHOICE' then 'CHOICE'
when RC = 'COMMERCIAL' then 'Commercial'
when RC = 'PT1' then 'CARE'
when RC = 'PT2' then 'CARE'
when RC = 'PT3' then 'CARE'
when RC in ('RC1', 'RC2', 'RC3', 'RC4', 'RC5', 'RC6', 'RC7') then 'MassHealth'
end as Program
, convert(decimal(9,2), MM) as MM

from ##temp12PerMMForecast

order by Date, Program, RC

quote:
Originally posted by X002548

post the query

I'm assuming you passing the sql in from some language...what is it...and are you using sql server?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-21 : 12:22:28

Does this help?

http://msdn.microsoft.com/en-us/library/ms187926.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-21 : 12:55:31

quote:
Originally posted by jcb267

Why create a stored procedure? What if the tase can be accomplished by several queries?

Because stored procedures should be used for ALL data access, IMO.

Let's take this in the reverse. Why shouldn't it be in a stored procedures? Why not just run one stored procedure that runs all of the queries for you instead of running all of the queries from the application?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-04-21 : 14:27:16

Good point......

quote:
Originally posted by tkizer

quote:
Originally posted by jcb267

Why create a stored procedure? What if the tase can be accomplished by several queries?

Because stored procedures should be used for ALL data access, IMO.

Let's take this in the reverse. Why shouldn't it be in a stored procedures? Why not just run one stored procedure that runs all of the queries for you instead of running all of the queries from the application?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 17:16:46

I probably should've asked this earlier in the day...

If you're planning to make a stored procedure out of this, can the query be modified? Also, were any of the criteria in the WHERE clauses changeable? (like year(reportingmonth)='2010')

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-22 : 10:05:11

Well, anyway, I was bored at work yesterday and did a little condensing:

-- Consolidated 12 period Member Month Projection based on Business Development membership forecast revised 9/15/2010
-- possible parameters:  -- where year(reportingmonth)='2010' and month(reportingmonth)='12'
DECLARE @reportmonth DATETIME
SET @reportmonth='12/1/2010'

--Extract total mms from DW.  --select starting point for mms projection (where clause)
;WITH	n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<12),	--counter to generate Periods
	growth(Program, RC, monthlygrowth) AS (				--growth factors for each Program
		SELECT 'MassHealth', RC, monthlygrowth FROM nhpuserreporting..MassHealth2011$ UNION ALL
		SELECT 'CARE', PlanType, monthlygrowth FROM nhpuserreporting..CCARE2011$ UNION ALL 
		SELECT 'CHOICE', Program, monthlygrowth FROM nhpuserreporting..CCHOICE2011$ UNION ALL 
		SELECT 'Commercial', Program, monthlygrowth FROM nhpuserreporting..Commercial2011$),
	mms(DATE, RC, MM, Program) AS (
		SELECT ReportingMonth DATE, CASE 
		WHEN SponsorID='100002' THEN CASE			--MassHealth
			WHEN RateCode LIKE '%RC[1-7]%' THEN SUBSTRING(RateCode, PATINDEX('%RC[1-7]%',RateCode),3) 
			ELSE RateCode END
		WHEN sponsorid='100003' THEN CASE			--CARE
			WHEN PolicyNumber LIKE '%FA%' THEN 'PT1'
			WHEN PolicyNumber LIKE '%FB%' THEN 'PT2'
			WHEN PolicyNumber LIKE '%FC%' THEN 'PT3'
			END 
		ELSE Program END RC, MemberMonths, 
		CASE SponsorID 
			WHEN '100002' THEN 'MassHealth' WHEN '100003' THEN 'CARE' WHEN '100360' THEN 'CHOICE' ELSE 'Commercial' END Program
		FROM dw..MemberMonths
		WHERE reportingmonth BETWEEN @reportmonth AND DATEADD(s, 86399, @reportmonth))
SELECT DATEADD(MONTH, n, @reportmonth) Date, m.RC, m.Program, SUM(m.MM)+(n*g.monthlygrowth) MM 
FROM mms m
LEFT JOIN growth g ON m.Program=g.Program AND m.RC=g.RC
CROSS JOIN n
GROUP BY m.Date, m.Program, m.RC, g.monthlygrowth, n

DO NOT ask me how long that took, or how many revisions I went through.

I'll tell you this, if that works first time, you owe me a

[beer].

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources