Hi, So I need to add tag parameter to a stored procedure that is created already. I have add the script to the stored procedure( the where clause at the bottom and declared variables), but I'm coming up with the following error:
'Msg 156, Level 15, State 1, Procedure sp_REPEnrollmentVariance, Line 544 [Batch Start Line 9]
Incorrect syntax near the keyword 'where'.'
Please help! Here is the following procedure:
if exists (select * from sysobjects where id = object_id('dbo.sp_REPEnrollmentVariance') and sysstat & 0xf = 4)
drop procedure dbo.sp_REPEnrollmentVariance
GO
create procedure dbo.sp_REPEnrollmentVariance (
@Type Char(3), -- 'Off' for Office, 'Und' for Underwriter
@Variation int, -- O = All Months, 1 = Current Month, 2 = Initial
@Value int, -- Number value to specify the Underwriter or Office requested
@EffDateFrom DateTime,
@EffDateTo DateTime
)
as
/*
Located: Reports -> Policy Administration -> Enrollment Variance Report
Reports -> Marketing -> Enrollment Variance
Created by: ?
Called by: REPEnrollmentVariance.dfm
Calls: nothing
Purpose: Returns result set of policies and their enrollments as submitted with the
1) underwritten policy (intial estimated census),
2) first premium payment (initial reported census)
3) most recent premium payment (current reported census)
4) prior month premium payment (prior reported census)
These census numbers can also be provided for each month the premiumn was paid ("All Months" option)
The actual variance calculations and threshhold percentages are coded in REPEnrollmentVariance.rpt.
As of 06/12/02, these calculations are as follows:
"Initial Variance" option: first month census changed > 10% from estimated census
abs( (InitRepCensus - InitEstCensus) / InitEstCensus ) > 10%
"Monthly Variance" options: current month census changed > 15% from first month census
OR current month census changed > 10% from previous month's census
abs( (CurrentCensus - InitRepCensus) / InitRepCensus ) > 15%
OR abs( (CurrentCensus - PriorCensus ) / PriorCensus ) > 10%
declare
@rc int,
@op varchar(16),
@obj varchar(64),
--@RequestTagId int = 0, --5 Tags
@Tag1Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag1 selection
@Tag2Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag2 selection
@Tag3Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag3 selection
@Tag4Id int = 0, -- 0 or Value for RequestID column in MgmtReportRequests table for Tag4 selection
@Tag5Id int = 0 -- 0 or Value for RequestID column in MgmtReportRequests table for Tag5 selection
declare @RequestProducerID int = 0,
@RequestBrokerId int = 0,
@RequestTPAId int =0,
@BegDate Date = '02/01/2015' , -- Used for Eff Date
@EndDate Date = '02/01/2015' , -- Used for Eff Date
@IsAll BIT = 0 -- Bring Everything
set nocount on
//
/ Create temporary tables /
//
create Table #Policies (
PlcPolicyId int null,
PolicyHolder varchar(75) null,
OfficeName varchar(20) null,
UnderwriterName varchar(40) null,
EffectiveDate datetime null,
InitialEstCensus int null
)
set @rc = @@error
if ( @rc <> 0 )
begin
set @op = 'Create'
set @obj = '#Policies'
goto OnError
end
create table #InitialMonthReported (
PlcPolicyId int not null,
MonthReported int null,
YearReported int null
)
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'create'
set @obj = '#InitialMonthReported'
goto OnError
end
create table #CurrentMonthReported (
PlcPolicyId int not null,
CurrentDateReported datetime null,
MonthReported int null,
YearReported int null
)
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'create'
set @obj = '#CurrentMonthReported'
goto OnError
end
create table #InitialRepCensus (
PlcPolicyId int null,
InitialRepCensus int null
)
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'create'
set @obj = '#InitialRepCensus'
goto OnError
end
create table #CurrentRepCensus (
PlcPolicyId int null,
MonthReported int null,
YearReported int null,
CurrentRepCensus int null
)
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'create'
set @obj = '#CurrentRepCensus'
goto OnError
end
create Table #Results (
PolicyID int null,
PolicyHolder varchar(75) null,
OfficeName varchar(20) null,
UnderwriterName varchar(40) null,
EffectiveDate datetime null,
YearReported int null,
MonthReported int null,
InitialEstCensus int null,
InitialRepCensus int null,
PriorRepCensus int null,
CurrentRepCensus int null,
InitialVariance float null,
MonthlyVariance float null,
CurrentVariance float null
)
set @rc = @@error
if ( @rc <> 0 )
begin
set @op = 'Create'
set @obj = '#Results'
goto OnError
end
//
/ Get Policies to Report on and Initial Estimated Census /
//
insert Into #Policies
select distinct
Plc.PlcPolicyID,
DC_Grp.Name,
case when DC_Off.Name like '%Graffam%' then DEA_Off.City + '-' + 'Graffam' -- Hardcoded the 'Graffam' name since this office should be going away at some point
else DEA_Off.City -- & didn't want to display the 'HCCB' name all of the time - dln 8/23/02
end,
rtrim( DC.FirstName ) + ' ' + DC.LastName,
Plc.EffectiveDate,
sum(rts.Enrollment) -- Includes additional schedules
from PlcPolicy Plc
inner join TtyTreaty Tty on Tty.TtyTreatyId = Plc.TreatyId
and Tty.LineOfInsurance in ('MDSL', 'OTRN')
inner join CodePlcStatus CPS on CPS.StatusId = Plc.StatusId
inner join PlcRateSchedule sch with (nolock) on sch.PlcPolicyId = Plc.PlcPolicyId
and sch.EffectiveDate = Plc.EffectiveDate
inner join PlcRate rts with (nolock) on rts.PlcRateScheduleId = sch.PlcRateScheduleId
inner join CodeCoverageType cct with (nolock) on cct.CoverageTypeId = sch.CoverageTypeId
and cct.CoverageType in ('SPEC', 'OTRN')
inner join DirCompany DC_Grp on DC_Grp.dirCompanyId = Plc.AccountId
left outer join DirCompany DC_Off on DC_Off.DirCompanyId = Plc.OfficeId
left outer join DirAddress da_off ON da_off.DirEntityId = dc_off.DirCompanyId
AND da_off.AddressType = 'STRT'
left outer join DirEntityAddress DEA_Off on DEA_Off.DirEntityAddressId = da_off.DirEntityAddressId
join DirContact DC on DC.DirContactId = Plc.UnderwriterId
where CPS.Status <> 'RESC'
and Plc.EffectiveDate between @EffDateFrom and @EffDateTo -- !!! Initial did not limit this to selected dates
and ( (@Type = 'Off' and Plc.OfficeId = @Value)
OR (@Type = 'Und' and Plc.UnderwriterId in (select DU.DirContactID from DirContact DC
join DirContact DU on DU.LastName = DC.LastName
and DU.FirstName = DC.FirstName
where DC.DirContactID = @Value) ) )
group by Plc.PlcPolicyId, DC_Grp.Name, Plc.EffectiveDate, DC_Off.Name, DEA_Off.City, rtrim( DC.FirstName ) + ' ' + DC.LastName
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'insert'
set @obj = '#Policies'
goto OnError
end
//
/* Get Intial Month Reported */
//
insert #InitialMonthReported
select P.PlcPolicyID, DatePart(mm, EffectiveDate), DatePart(yyyy, EffectiveDate)
from PlcPolicy P
inner join CodePlcStatus CPS on CPS.StatusId = P.StatusId
where CPS.Status <> 'RESC'
and P.EffectiveDate between @EffDateFrom and @EffDateTo
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'insert'
set @obj = '#InitialMonthReported'
goto OnError
end
//
/ Get Current Month(s) Reported /
//
if @Variation = 0 -- All Transaction Months
begin
-- Pull all months that are related to the policy
insert #CurrentMonthReported (PlcPolicyId, MonthReported, YearReported)
select distinct PP.PlcPolicyId,
PDAP.MonthNumber,
PDAP.YearNumber
from PlcPolicy PP
inner join PrmDepAdjPolicy PDAP on PDAP.PlcPolicyId = PP.PlcPolicyId
inner join PrmTransPremium PTP on PTP.PrmDepAdjPolicyId = PDAP.PrmDepAdjPolicyId
inner join PrmTransPremiumDetail PTPD on PTPD.PrmTransPremiumId = PTP.PrmTransPremiumId
inner join CodeCoverageType CCT on CCT.CoverageTypeId = PTP.CoverageTypeId
inner join CodePlcStatus CPS on CPS.StatusId = PP.StatusId
where CPS.Status not in ('RESC', 'DEAD', 'SENT', 'REQD')
and PP.EffectiveDate between @EffDateFrom and @EffDateTo
and CCT.CoverageType in ('SPEC', 'OTRN')
and PTPD.Enrollment > 0
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'insert'
set @obj = '#CurrentMonthReported(1)'
goto OnError
end
end -- All Transaction Months
else
begin -- Current Transaction Month
-- Get only the initial or last month applied to the policy
-- ( need to get some type of Current month records, even for the Initial report, to allow later routines to select records properly )
insert #CurrentMonthReported (PlcPolicyId, CurrentDateReported)
select PP.PlcPolicyId,
case @Variation
when 1 then max(convert(datetime, (convert(varchar(2), PDAP.MonthNumber) + '/1/' + convert(varchar(4), PDAP.YearNumber))))
when 2 then min(convert(datetime, (convert(varchar(2), PDAP.MonthNumber) + '/1/' + convert(varchar(4), PDAP.YearNumber))))
end
from PlcPolicy PP
join PrmDepAdjPolicy PDAP on PDAP.PlcPolicyId = PP.PlcPolicyId
join PrmTransPremium PTP on PTP.PrmDepAdjPolicyId = PDAP.PrmDepAdjPolicyId
inner join PrmTransPremiumDetail PTPD on PTPD.PrmTransPremiumId = PTP.PrmTransPremiumId
inner join CodeCoverageType CCT on CCT.CoverageTypeId = PTP.CoverageTypeId
inner join CodePlcStatus CPS on CPS.StatusId = PP.StatusId
where CPS.Status not in ('RESC', 'DEAD', 'SENT', 'REQD')
and PP.EffectiveDate between @EffDateFrom and @EffDateTo
and CCT.CoverageType in ('SPEC', 'OTRN')
group by PP.PlcPolicyId
having sum(PTPD.Enrollment) > 0
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'insert'
set @obj = '#CurrentMonthReported(2)'
goto OnError
end
Update #CurrentMonthReported
set MonthReported = DatePart(mm, CurrentDateReported),
YearReported = DatePart(yyyy, CurrentDateReported)
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'Update'
set @obj = '#CurrentMonthReported'
goto OnError
end
end -- Current Transaction Month
//
/ Get Intial Reported Census /
//
insert into #InitialRepCensus
select PP.PlcPolicyId,
sum( PTPD.Enrollment )
from PlcPolicy PP
inner join #InitialMonthReported IMP on IMP.PlcPolicyId = PP.PlcPolicyId
inner join PrmDepAdjPolicy PDAP on PDAP.PlcPolicyId = PP.PlcPolicyId
and PDAP.MonthNumber = IMP.MonthReported
and PDAP.YearNumber = IMP.YearReported
inner join PrmDepAdj PDA on PDA.PrmDepAdjId = PDAP.PrmDepAdjId
inner join PrmTransPremium PTP on PTP.PrmDepAdjPolicyId = PDAP.PrmDepAdjPolicyId
inner join PrmTransPremiumDetail PTPD on PTPD.PrmTransPremiumId = PTP.PrmTransPremiumId
inner join #Policies P on P.PlcPolicyId = PP.PlcPolicyId
inner join CodePlcstatus CPS on CPS.StatusId = PP.StatusId
inner join CodeCoverageType CCT on CCT.CoverageTypeId = PTP.CoverageTypeId
where CPS.Status not in ('RESC', 'DEAD', 'SENT', 'REQD')
and CCT.CoverageType in ('SPEC', 'OTRN')
and PDA.DepAdjStatus = 'P' --Posted
and PDA.TransactionType in ('DPST', 'PMAD', 'VDDP')
group by PP.PlcPolicyId
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'insert'
set @obj = '#InitialRepCensus'
goto OnError
end
//
/ Get Current Reported Census(s) /
//
insert into #CurrentRepCensus(PlcPolicyId, MonthReported, YearReported, CurrentRepCensus)
select PP.PlcPolicyId,
CMR.MonthReported,
CMR.YearReported,
sum( PTPD.Enrollment )
from PlcPolicy PP
inner join #CurrentMonthReported CMR on CMR.PlcPolicyId = PP.PlcPolicyId
inner join PrmDepAdjPolicy PDAP on PDAP.PlcPolicyId = PP.PlcPolicyId
and PDAP.MonthNumber = CMR.MonthReported
and PDAP.YearNumber = CMR.YearReported
inner join PrmDepAdj PDA on PDA.PrmDepAdjId = PDAP.PrmDepAdjId
inner join PrmTransPremium PTP on PTP.PrmDepAdjPolicyId = PDAP.PrmDepAdjPolicyId
inner join PrmTransPremiumDetail PTPD on PTPD.PrmTransPremiumId = PTP.PrmTransPremiumId
inner join #Policies P on P.PlcPolicyId = PP.PlcPolicyId
inner join CodePlcstatus CPS on CPS.StatusId = PP.StatusId
inner join CodeCoverageType CCT on CCT.CoverageTypeId = PTP.CoverageTypeId
where CPS.Status not in ('RESC', 'DEAD', 'SENT', 'REQD')
and CCT.CoverageType in ('SPEC', 'OTRN')
and PDA.DepAdjStatus = 'P' --Posted
and PDA.TransactionType in ('DPST', 'PMAD', 'VDDP')
group by PP.PlcPolicyId, CMR.MonthReported, CMR.YearReported
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'insert'
set @obj = '#CurrentRepCensus'
goto OnError
end
//
/* Combine the data into an initial Result Set /
/ ( from this, variances will be calculated and contract criteria applied ) */
//
Insert #Results
select P.PlcPolicyId,
P.PolicyHolder,
P.OfficeName,
P.UnderwriterName,
P.EffectiveDate,
MR.YearReported,
MR.MonthReported,
P.InitialEstCensus,
I.InitialRepCensus,
Pr.CurrentRepCensus,
C.CurrentRepCensus,
Null,
Null,
Null
from #Policies P
inner join #CurrentMonthReported MR on MR.PlcPolicyID = P.PlcPolicyId
inner join #InitialRepCensus I on I.PlcPolicyId = P.PlcPolicyId
inner join #CurrentRepCensus C on C.PlcPolicyId = P.PlcPolicyId
and C.MonthReported = MR.MonthReported
and C.YearReported = MR.YearReported
left join #CurrentRepCensus Pr on Pr.PlcPolicyID = C.PlcPolicyID
and Pr.YearReported = C.YearReported
and Pr.MonthReported = C.MonthReported - 1
//
/* Calculate Variances */
//
-- We will always calculate InitialVariance; but will only calculate Monthly and Current if we are not running the Initial Report
update #Results
set InitialVariance = case when IsNull(InitialEstCensus, 0) = 0 then 0
when IsNull(InitialRepCensus, 0) = 0 then 0
else cast(abs(InitialEstCensus - InitialRepCensus) as float) / cast(InitialEstCensus as float)
end,
MonthlyVariance = case when @Variation = 2 then 0
when IsNull(PriorRepCensus, 0) = 0 then 0
when IsNull(CurrentRepCensus, 0) = 0 then 0
else cast(abs(PriorRepCensus - CurrentRepCensus) as float) / cast(PriorRepCensus as float)
end,
CurrentVariance = case when @Variation = 2 then 0
when IsNull(InitialRepCensus, 0) = 0 then 0
when IsNull(CurrentRepCensus, 0) = 0 then 0
else cast(abs(InitialRepCensus - CurrentRepCensus) as float) / cast(InitialRepCensus as float)
end,
-- Do not want to see the Current Rep Census if this is the Inital Variance report
CurrentRepCensus = case when @Variation = 2 then null
else CurrentRepCensus
end
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'update'
set @obj = '#Results-Variances'
goto OnError
end
//
/* Set to NULL Variances not exceeding contract allowances /
/ (because the report will show records that could have /
/ one or more allowances that are exceeded and we want /
/ it to be easy to identify the excesses.) */
//
update #Results
set InitialVariance = Null
where InitialVariance < .10
update #Results
set MonthlyVariance = Null
where MonthlyVariance < .10
update #Results
set CurrentVariance = Null
where CurrentVariance < .15
set @rc = @@error
if (@rc <> 0)
begin
set @op = 'update'
set @obj = '#Results-Null Variances'
goto OnError
end
//
/ Select Finial Result Set /
//
select PolicyID,
PolicyHolder,
OfficeName,
UnderwriterName,
EffectiveDate,
YearReported,
MonthReported,
InitialEstCensus,
InitialRepCensus,
PriorRepCensus,
CurrentRepCensus,
InitialVariance,
MonthlyVariance,
CurrentVariance,
@EffDateFrom as FromEffDate,
@EffDateTo as ToEffDate,
@Type as Type,
@Variation as Variation
from #Results
where NOT(InitialVariance Is Null)
or NOT(MonthlyVariance Is Null)
or NOT(CurrentVariance Is Null)
order by EffectiveDate, PolicyHolder, OfficeName, UnderwriterName, MonthReported desc, YearReported desc
return(0)
OnError:
raiserror('$DBGIR$:1;sp_REPEnrollmentVariance;%d;%s;%s;', 18, 1, @rc, @op, @obj)
return(1)
where
( pp.EffectiveDate BETWEEN @BegDate AND @EndDate
AND
(-- Bring everything
(
@IsAll = 1
)
OR
( (-- Broker
@RequestBrokerId = 0
OR
pp.BrokerId IN (select IntId from MgmtReportRequests where RequestId = @RequestBrokerId)
)
AND
(-- Producer
@RequestProducerId = 0
OR
pp.ProducerId IN (select IntId from MgmtReportRequests where RequestId = @RequestProducerId)
)
AND
( --TPA
@RequestTPAId = 0
OR
pmd.PremiumAdministratorId IN (select IntId from MgmtReportRequests where RequestId = @RequestTPAId)
)
)
)
)
AND NOT EXISTS( select PPT.PlcPolicyId
from PlcPolicyTag PPT
where PPT.PlcPolicyId = pp.plcPolicyId
and (PPT.ExpirationDate IS NULL OR PPT.ExpirationDate >= PP.ExpirationDate)
and PPT.TagId in ( select IntId from MgMtReportRequests where RequestID IN (NULLIF(@Tag1Id, 0), NULLIF(@Tag2Id, 0), NULLIF(@Tag3Id, 0), NULLIF(@Tag4Id, 0), NULLIF(@Tag5Id, 0) ) )
)
GO
exec dbo.sp_SetObjectPermissions 'sp_REPEnrollmentVariance'
GO