I'm new to Microsoft SQL Server:
I have this:
USE master
GO
CREATE PROCEDURE dbo.CreateTriangle @vCurMoNum varchar(2) @vCurYr5 varchar(4) @vCurYr varchar(4)
AS
BEGIN
SELECT
PREM.MCX_CD as MCX_CD,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 1 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+0||'@vCurMoNum','YYYYMM')
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>-1
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL1,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 2 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+1||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>0
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL2,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 3 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+2||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>1
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL3,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 4 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+3||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>2
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL4,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 5 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+4||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>3
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL5,
sum(PREM.EP_US_CURY_AMT) as EP_US_CURY_AMT,
from PREMIUM PREM
LEFT JOIN MCX MCX
ON MCX.MCX_CD = PREM.MCX_CD
WHERE
PREM.ACTG_YR >= '@vCurYr5'
AND
PREM.ACTG_YR_MO <= '@vCurYr@vCurMoNum'
AND
MCX.MCX_LV_3_CD IN
('XXX','YYY')
and
PREM.INS_CD IN ('DIR', 'ASM')
group by
PREM.MCX_CD
Having sum(PREM.EP_US_CURY_AMT) <> 0 ;
END
Can I simplify doing this:
USE master
GO
CREATE PROCEDURE dbo.CreateTriangle @vCurMoNum varchar(2) @vCurYr5 varchar(4) @vCurYr varchar(4)
AS
BEGIN
SELECT
PREM.MCX_CD as MCX_CD,
BEGIN
DECLARE
@X int= 1
@Y int=0
@Z int=0
WHILE @X< @vCurMoNum
BEGIN
SET @Y=@X - 2
SET @Z=@X – 1
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < @X and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+@Z||'@vCurMoNum','YYYYMM')
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>@Y
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL@X,
SET @X= @X + 1
END
END
sum(PREM.EP_US_CURY_AMT) as EP_US_CURY_AMT
from PREMIUM PREM
LEFT JOIN MCX MCX
ON MCX.MCX_CD = PREM.MCX_CD
WHERE
PREM.ACTG_YR >= '@vCurYr5'
AND
PREM.ACTG_YR_MO <= '@vCurYr@vCurMoNum'
AND
MCX.MCX_LV_3_CD IN
('XXX','YYY')
and
PREM.INS_CD IN ('DIR', 'ASM')
group by
PREM.MCX_CD
Having sum(PREM.EP_US_CURY_AMT) <> 0 ;
END
Excuting the Procedure using this:
USE master
GO
EXEC dbo.CreateTriangle '06', '2017', '2022'
If not, what can I do to simplify this way
