SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Incorrect syntax near the keyword 'as'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rahman87
Starting Member

India
2 Posts

Posted - 02/14/2012 :  07:37:37  Show Profile  Reply with Quote
facing a strange situation with a procedure. When I compiles and saves it. The query doesnt show any error, when I try to run it following errors are shown.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.

Msg 156, Level 15, State 1,
Line 2

Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.

Msg 208, Level 16, State 1, Procedure S2_XLLM_AVE, Line 68
Invalid object name 'S2_XLLM_PP_from_ave_gross'.



the first 3 errors points to the Alter Procedure statement. following is the code. Sorry for the long code but I am in desperate need of help


USE [actuarialdb]
GO
/****** Object:  StoredProcedure [dbo].[S2_XLLM_AVE]    Script Date: 02/14/2012 14:20:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Exec dbo.[S2_XLLM_AVE] 25,'2011Q4',1
ALTER procedure [dbo].[S2_XLLM_AVE]
@ssr as int,
@beg as nvarchar(max),
@quarter as int
AS
--declare @ssr as varchar(max) 
--declare @beg as varchar(max)
--declare @quarter as int
declare @a as int
declare @b as int
declare @c as int
declare @d as int
declare @e as int
declare @f as int
declare @start as varchar(max)
declare @end as varchar(max)
--set @ssr='25'
--set @beg='2011Q4'
--set @quarter=1

set @a = @quarter
set @b=cast((select month(vdate)/3 as CurrQtr from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg
group by vdate) as int)
set @c=@quarter+@b
set @d=cast((select year(vdate) as CurrYear from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg
group by vdate) as int)
set @e= case when @c%4=0 then 4 else @c%4 end
set @f=4-@a
set @start= cast(@b as varchar(4))+'Q'+left(@beg,4)
set @end=case when @c>4 then cast(@e as varchar(4))+'Q'+cast(@d+1 as varchar(4)) else cast(@c as varchar(4))+'Q'+cast(@d as varchar(4)) end

--From Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_gross') drop table dbo.S2_XLLM_PP_from_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_gross],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_gross],
1-yr1+'+@d+' as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_gross') drop table dbo.S2_XLLM_PP_to_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_gross],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_gross], 
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--From Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_ceded') drop table dbo.S2_XLLM_PP_from_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_ceded],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_ceded],
1-yr1+'+@d+' as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_ceded') drop table dbo.S2_XLLM_PP_to_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_ceded],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_ceded], 
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')

--Combined Payment Patterns

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_ave') drop table dbo.S2_XLLM_PP_ave
select A.[Reserving Line],A.[ProgramCode/UnderwritingYear],A.[From Inc Pattern_gross],A.[From Cum Pattern_gross],B.[To Inc Pattern_gross],B.[To Cum Pattern_gross],
case when round([From Cum Pattern_gross],10) = 1 then 0 else ([To Cum Pattern_gross]-[From Cum Pattern_gross])/(1-[From Cum Pattern_gross]) end as ExpPP_Gross,
C.[From Inc Pattern_ceded],C.[From Cum Pattern_ceded],D.[To Inc Pattern_ceded],D.[To Cum Pattern_ceded],
case when round([From Cum Pattern_ceded],10) = 1 then 0 else ([To Cum Pattern_gross]-[From Cum Pattern_ceded])/(1-[From Cum Pattern_ceded]) end as ExpPP_ceded
into dbo.[S2_XLLM_PP_ave]
from
S2_XLLM_PP_from_ave_gross A inner join S2_XLLM_PP_to_ave_gross B
on A.[Reserving Line]=B.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=B.[ProgramCode/UnderwritingYear] inner join
S2_XLLM_PP_from_ave_ceded C on
A.[Reserving Line]=C.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=C.[ProgramCode/UnderwritingYear]  inner join
S2_XLLM_PP_to_ave_ceded D on
A.[Reserving Line]=D.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=D.[ProgramCode/UnderwritingYear]  



--Case+IBNR from S2F2F

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_Exp_ave') drop table dbo.S2_XLLM_Exp_ave
select B.[ProgramCode/UnderwritingYear],B.[Reserving Line] as Profile_ID,ExpPP_Gross, isnull(ExpGRes,0) as ExpGRes,isnull(ExpCRes,0) as ExpCRes,
isnull(ExpGRes*ExpPP_Gross,0) as ExpPaid_Gross ,
[From Cum Pattern_Gross],[To Cum Pattern_Gross],
ExpPP_Ceded,isnull(ExpCRes*ExpPP_Ceded,0) as ExpPaid_Ceded ,[From Cum Pattern_ceded],[To Cum Pattern_ceded]
into dbo.S2_XLLM_Exp_ave
from 
(select [ProgramCode/UnderwritingYear],Profile_ID,
sum([Dcase Reserves]+[ACase Reserves Ext]+[ACase Reserves Int]+[DIBNR]+[AIBNR Ext]+[AIBNR Int]) as ExpGRes,
sum([CCase Reserves Ext]+[CCase Reserves Int]+[CIBNR Ext]+[CIBNR Int]) as ExpCRes
from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg and 
[BusinessUnit/Branch] =51209 and [Department/CostCentre] = 806670
group by Profile_ID,[ProgramCode/UnderwritingYear]) A right join S2_XLLM_PP_ave B
on A.Profile_ID=B.[Reserving Line] and A.[ProgramCode/UnderwritingYear]=B.[ProgramCode/UnderwritingYear]


--Actual Paid & AVE

if(@c>4)
Begin
	if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_final_ave') drop table dbo.S2_XLLM_final_ave
	select A.[Reserving Line],A.YOA,A.[SII Lob],sum(A.[ActPaid]*exch_rate) as [ActPaid],sum([ActPaid_Ceded]*exch_rate) as [ActPaid_Ceded],
	isnull(ExpGRes,0)/1000 as ExpGRes,isnull(ExpPaid_Gross,0)/1000 as ExpPaid_Gross,isnull(ExpPaid_Gross,0)/1000-sum(isnull([ActPaid],0)*exch_rate) as AVE_Gross,
	isnull([From Cum Pattern_Gross],1) as [From Cum Pattern_Gross],isnull([To Cum Pattern_Gross],1) as [To Cum Pattern_Gross],
	case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end as [From Development Age],
	(@d-yoa+1)*12+(@e*3) as [To Development Age],
	isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0) as [Incremental Paid to ultimate Patterns_Gross],(1-isnull([From Cum Pattern_Gross],1)) as [Undeveloped Paid to ultimate Patterns_Gross],
	isnull(ExpCRes,0)/1000 as ExpCRes,isnull(ExpPaid_Ceded,0)/1000 as ExpPaid_Ceded,
	isnull(ExpPaid_Ceded,0)/1000-sum(isnull([ActPaid_Ceded],0)*exch_rate) as AVE_Ceded,
	isnull([From Cum Pattern_Ceded],1) as [From Cum Pattern_Ceded],isnull([To Cum Pattern_Ceded],1) as [To Cum Pattern_Ceded],
	isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0) as [Incremental Paid to ultimate Patterns_Ceded],
	(1-isnull([From Cum Pattern_Ceded],1)) as [Undeveloped Paid to ultimate Patterns_Ceded] 
	into dbo.S2_XLLM_final_ave from 
	(select [New SSR] as [Reserving Line],YOA,[SII Lob],[ActPaid],[ActPaid_Ceded],settcurrency from 
	(select MantriClass as [Reserving Line],YoA,settcurrency,
	SUM(case when period =@end then [Gross Paid] else -[Gross Paid] end) as [ActPaid],
	SUM(case when period =@end then [Gross Paid]-[Net Paid] else -([Gross Paid]-[Net Paid]) end) as [ActPaid_Ceded] 
	from [XLLM_T92 - Flat Pack file after earnings] 
	where period in (@start,@end)
	and Syn=1209  group by MantriClass,YoA,settcurrency) A inner join
	dbo.[s2_xllm_mapping_mastertable] B 
	on A.[Reserving Line] =B.[New SSR] where [Reserving Line]=@ssr) A
	left join S2_XLLM_Exp_ave B on A.[Reserving Line]=B.Profile_ID and A.yoa=b.[ProgramCode/UnderwritingYear]  inner join
	(select exch_rate,from_cur from S2_exchange_rates where to_cur='gbp' 
	and eff_date= (select vdate from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg group by vdate) ) C  
	on A.settcurrency=C.from_cur
	group by A.[Reserving Line],A.YOA,A.[SII Lob],isnull(ExpGRes,0)/1000,isnull(ExpPaid_Gross,0)/1000,isnull([From Cum Pattern_Gross],1),
	isnull([To Cum Pattern_Gross],1),
	case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end ,case when @d=yoa then (@e*3) else (@d-yoa)*12+(@e*3) end ,
	isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0),(1-isnull([From Cum Pattern_Gross],1)),isnull(ExpCRes,0)/1000,
	isnull(ExpPaid_Ceded,0)/1000,isnull([From Cum Pattern_Ceded],1),isnull([To Cum Pattern_Ceded],1),
	isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0),(1-isnull([From Cum Pattern_Ceded],1))
	order by yoa
End

if(@c<=4)
Begin
	if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_final_ave') drop table dbo.S2_XLLM_final_ave
	select A.[Reserving Line],A.YOA,A.[SII Lob],sum(A.[ActPaid]*exch_rate) as [ActPaid],
	sum([ActPaid_Ceded]*exch_rate) as [ActPaid_Ceded],
	isnull(ExpGRes,0)/1000 as ExpGRes,isnull(ExpPaid_Gross,0)/1000 as ExpPaid_Gross,isnull(ExpPaid_Gross,0)/1000-sum(isnull([ActPaid],0)*exch_rate) as AVE_Gross,
	isnull([From Cum Pattern_Gross],1) as [From Cum Pattern_Gross],isnull([To Cum Pattern_Gross],1) as [To Cum Pattern_Gross],
	case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end as [From Development Age],
	case when @d=yoa then (@e*3) else (@d-yoa)*12+(@e*3) end as [To Development Age],
	isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0) as [Incremental Paid to ultimate Patterns_Gross],(1-isnull([From Cum Pattern_Gross],1)) as [Undeveloped Paid to ultimate Patterns_Gross] ,
	isnull(ExpCRes,0)/1000 as ExpCRes,isnull(ExpPaid_Ceded,0)/1000 as ExpPaid_Ceded,isnull(ExpPaid_Ceded,0)/1000-sum(isnull([ActPaid_Ceded],0)*exch_rate) as AVE_Ceded,
	isnull([From Cum Pattern_Ceded],1) as [From Cum Pattern_Ceded],isnull([To Cum Pattern_Ceded],1) as [To Cum Pattern_Ceded],
	isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0) as [Incremental Paid to ultimate Patterns_Ceded],(1-isnull([From Cum Pattern_Ceded],1)) as [Undeveloped Paid to ultimate Patterns_Ceded] 
	into dbo.S2_XLLM_final_ave from		
	
	(select [New SSR] as [Reserving Line],YOA,[SII Lob],[ActPaid],[ActPaid_Ceded],settcurrency from 
	(select MantriClass as [Reserving Line],YoA,settcurrency,
	SUM(case when period =@end then [Gross Paid] else -[Gross Paid] end) as [ActPaid],
	SUM(case when period =@end then [Gross Paid]-[Net Paid] else -([Gross Paid]-[Net Paid]) end) as [ActPaid_Ceded] 
	from [XLLM_T92 - Flat Pack file after earnings] 
	where period in (@start,@end)
	and Syn=1209  group by MantriClass,YoA,settcurrency ) A inner join dbo.[s2_xllm_mapping_mastertable] B 
	on A.[Reserving Line] =B.[New SSR] where [Reserving Line]=@ssr) A
	left join S2_XLLM_Exp_ave B on A.[Reserving Line]=B.Profile_ID and A.yoa=b.[ProgramCode/UnderwritingYear] inner join
	(select exch_rate,from_cur from S2_exchange_rates where  to_cur='usd' 
	and eff_date= (select vdate from S2_XLLM_F2F_Mastertable where cast(year(vdate) as varchar(4))+'Q'+cast(month(vdate)/3 as varchar(2))=@beg group by vdate) ) C  
	on A.settcurrency=C.from_cur
	group by A.[Reserving Line],A.YOA,A.[SII Lob],isnull(ExpGRes,0)/1000,isnull(ExpPaid_Gross,0)/1000,isnull([From Cum Pattern_Gross],1),
	isnull([To Cum Pattern_Gross],1),
	case when @d=yoa then (@b*3) else (@d-yoa)*12+(@b*3) end ,case when @d=yoa then (@e*3) else (@d-yoa)*12+(@e*3) end ,
	isnull([To Cum Pattern_Gross],0)- isnull([From Cum Pattern_Gross],0),(1-isnull([From Cum Pattern_Gross],1)),isnull(ExpCRes,0)/1000,
	isnull(ExpPaid_Ceded,0)/1000,isnull([From Cum Pattern_Ceded],1),isnull([To Cum Pattern_Ceded],1),
	isnull([To Cum Pattern_Ceded],0)- isnull([From Cum Pattern_Ceded],0),(1-isnull([From Cum Pattern_Ceded],1))
	
	order by yoa
End


--select * from S2_exchange_rates




Command(s) completed successfully

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/14/2012 :  07:45:58  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
If it doesn't show syntax errors when you save, it means the syntax errors are in the dynamic SQL that you have, so one of these pieces

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_gross') drop table dbo.S2_XLLM_PP_from_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_gross],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_gross],
1-yr1+'+@d+' as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_gross') drop table dbo.S2_XLLM_PP_to_ave_gross
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_gross],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_gross], 
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_gross from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''Gross'' and currency=''GBP''')

--From Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_from_ave_ceded') drop table dbo.S2_XLLM_PP_from_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@b+'] as [From Inc Pattern_ceded],[Cum Pattern_Q'+@b+'] as [From Cum Pattern_ceded],
1-yr1+'+@d+' as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_from_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')

--To Development Age Payment Pattern from discounting procedure

if exists (Select * from dbo.sysobjects where Name = N'S2_XLLM_PP_to_ave_ceded') drop table dbo.S2_XLLM_PP_to_ave_ceded
exec ('select [Reserving Line],[Inc Pattern_Q'+@e+'] as [To Inc Pattern_ceded],[Cum Pattern_Q'+@e+'] as [To Cum Pattern_ceded], 
case when '+@c+'<=4 then 1-yr1+'+@d+' else '+@d+'+2-yr1 end as  [ProgramCode/UnderwritingYear]
into dbo.S2_XLLM_PP_to_ave_ceded from [G2_XLLM_PPInc_Q_cum_All_Combined] 
where [Provision]=''claims'' and [Type of Provision]=''claims'' and [Gross/RI]=''RI'' and currency=''GBP''')


Rather than EXEC it directly, use a variable to build up the dynamic SQL and use PRINT to return the built-up dynamic SQL as a message. You should be able to see where the errors are in that.

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 02/14/2012 08:26:59
Go to Top of Page

rahman87
Starting Member

India
2 Posts

Posted - 02/17/2012 :  04:13:55  Show Profile  Reply with Quote
Thanks a lot Gail, that was brillilant of you. I rechecked the executable part and found the error. As always it was a small issue with the parameter I am passing.

Command(s) completed successfully
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/17/2012 :  04:22:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Glad to hear it's sorted

--
Gail Shaw
SQL Server MVP
Go to Top of Page

tksqqo
Starting Member

South Africa
1 Posts

Posted - 07/04/2013 :  07:19:50  Show Profile  Reply with Quote
Please help ... I am trying to excute this sp_ but keep on failing..

--USE [MIS]
--GO
--/****** Object: StoredProcedure [dbo].[usp_UploadSantam] Script Date: 07/04/2013 11:21:11 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO



--ALTER PROC [dbo].[usp_UploadSantam] (@ImportOriginator varchar (50))
--AS
DECLARE @Headers VARCHAR(1000)
DECLARE @x VARCHAR(8000)
DECLARE @Maxrows int
DECLARE @RowCount int
DECLARE @branch varchar(50)
DECLARE @UploadID int
DECLARE @InsurerID tinyint
DECLARE @RecCount tinyint
DECLARE @PolicyNumber varchar(25)
DECLARE @Type char(3)
DECLARE @BrokerIDSecondary INT
DECLARE @Partnerid INT
DECLARE @Brokerid INT
DECLARE @SegmentID INT
DECLARE @PolicyID INT
DECLARE @CompanyName varchar (255)
DECLARE @ClientID int
DECLARE @BranchID int
DECLARE @AgentcodeData varchar(255)
DECLARE @AgentCodeID int
DECLARE @TransactionDateChar char(8)
DECLARE @TransactionDate datetime
DECLARE @EarningMonth int
DECLARE @EarningYear int
DECLARE @PremiumVested DECIMAL (12,4)
DECLARE @PremiumPolicyFee DECIMAL (12,4)
DECLARE @PremiumBase DECIMAL (12,4)
DECLARE @Commission DECIMAL (12,4)
DECLARE @CommissionPerc DECIMAL (12,4)
DECLARE @FullName varchar(255)
DECLARE @CompanyID INT
DECLARE @ProductID smallint
DECLARE @PortFolioID smallint
DECLARE @Sender varchar (100)
DECLARE @SystemID int
DECLARE @FiscalPeriodID int
--ROLL-UP--

DECLARE @Table TABLE
([UploadID] [int] identity NOT NULL ,
[Branch] [int] ,
[AgentCode] [varchar] (255) ,
[PolicyNo] [varchar] (35) ,
[FullName] [varchar] (255),
[Premium] [decimal](15, 2) NULL ,
[PolicyFee] [decimal](15, 2) NULL ,
[Commission] [decimal](15, 2) NULL ,
[Earning Month] [int] NULL ,
[Earning Year] [int] NULL )

BEGIN
SET @Sender = 'usp_UploadSantam'

--usp_UploadSantam
--usp_UploadSBInvest

DELETE FROM tblMIS_UploadSantam WHERE PolicyNo IS NULL
-------------------------------------------------------------------------------------------------------
-- First validate Branch and agentcode

UPDATE tblMIS_UploadSantam
SET Branch = (case WHEN len(branch) = 1 THEN '00'+Branch
WHEN len(Branch) = 2 THEN '0'+ Branch
ELSE Branch END)

UPDATE tblMIS_UploadSantam
SET AgentCode = LTRIM(RTRIM(Branch)) + '/' + LTRIM(RTRIM(AgentCode))


UPDATE tblMIS_UploadSantam
SET Branch = T.BranchID
FROM tblMIS_AgentCodeTranslation T, tblMIS_UploadSantam U
WHERE T.AgentCode = U.AgentCode


-- ----now process individual records----------------------------------------------------------------------
SELECT @PartnerID = 1, @BrokerIDSecondary = 1, @SegmentID = 1, @ProductID = 1, @PortFolioID = 1, @SystemID = 3


--get brokerID from New Business Table
IF EXISTS (SELECT BrokerID
FROM tblMIS_BrokerNewBusiness
WHERE PolicyNumber = @PolicyNumber)
SELECT @BrokerID = BrokerID
FROM tblMIS_BrokerNewBusiness
WHERE PolicyNumber = @PolicyNumber
ELSE
SET @BrokerID = 1

INSERT INTO @Table (Branch, AgentCode,PolicyNo, FullName,
Premium, PolicyFee, Commission,
[Earning Month], [Earning Year] )
SELECT Branch, AgentCode,PolicyNo, FullName,
Sum(isnull(PremiumVested,0)), sum(isnull([Policy Fee],0)), sum(isnull(Commission,0)),
[Earning Month], [Earning Year]
FROM tblMIS_UploadSantam
GROUP BY Branch, AgentCode, PolicyNo, FullName, [Earning Month], [Earning Year]

Update @Table
set Premium = Premium+PolicyFee

SET @RowCount = 1
SELECT @MaxRows = count(1) FROM @Table

WHILE @RowCount <= @MaxRows
BEGIN
SELECT @Policynumber = PolicyNo,
@BranchID = Branch,
@FullName = FullName,
@AgentCodeData = AgentCode,
@PremiumVested = Premium,
@PremiumPolicyFee = PolicyFee,
@Commission = Commission,
@CommissionPerc = (case when premium <> 0 then (commission/premium)*100 else 0 end),
@EarningYear = [Earning Year],
@EarningMonth = [Earning Month]
FROM @Table
WHERE UploadID = @RowCount

SELECT @InsurerID =
CASE
WHEN SUBSTRING(@AgentcodeData,1,3) IN ('432','433','434','439','527') THEN 6
WHEN SUBSTRING(@AgentcodeData,1,3) IN ('225') THEN 58
ELSE 37
END


--get details for header record
SELECT @ClientID = 1
IF EXISTS (select ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName)
SELECT @ClientID = ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName
--create new client if necessary
IF @ClientID = 1
EXEC usp_InsertClient null, null, @FullName, @FullName, null, @FullName, NULL, NULL, @ClientID OUTPUT
--if header exists modify else insert
IF EXISTS (SELECT PolicyID FROM tblMIS_PolicyHead (NOLOCK) WHERE PolicyNumber = @PolicyNumber)
SELECT @Type = 'UPD'
ELSE
SELECT @Type = 'INS'
--process header record
EXEC usp_InsertPolicyHead
@Sender, @Type,@PolicyNumber, 3, null, @BrokerID,@PartnerID, @ClientID, 4, @SegmentID, null,
@InsurerID, @SystemID, @AgentCodeData, @ProductID, @PortFolioID, @PolicyID OUTPUT


--------------------------------------------------------------------------------------------------------
--get details for detail record
SELECT @TransactiondateChar = FiscalDateChar , @FiscalPeriodID = FiscalPeriodID,
@TransactionDate = FiscalDate FROM tblMIS_FiscalPeriods WHERE EarningYear = @EarningYear AND EarningMonth = @EarningMonth

IF EXISTS ( SELECT AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID)
SELECT @AgentCodeID = AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID
ELSE
SELECT @AgentCodeID = 1

IF isnull(@AgentCodeID,0) = 0
RAISERROR ('AgentCode does NOT EXIST' ,11,1,@AgentCodeData)


IF EXISTS (SELECT DetailID
FROM tblMIS_PolicyDetail
WHERE PolicyID = @PolicyID
AND FiscalPeriodID = @FiscalPeriodID
AND AgentCodeID = @AgentCodeID)
SELECT @Type = 'UPD'
ELSE

SELECT @Type = 'INS'

IF EXISTS ( SELECT PolicyNumber FROM tblMIS_BrokerNewBusiness WHERE PolicyNumber = @PolicyNumber)
SELECT @PremiumBase = PremiumGross FROM tblMIS_BrokerNewBusiness (NOLOCK) WHERE PolicyNumber = @PolicyNumber
ELSE
SELECT @PremiumBase = @PremiumVested

EXEC usp_InsertPolicyDetail
@Type, @PolicyID, @TransactionDate, @TransactionDateChar, @PremiumBase,
@PremiumVested, @PremiumPolicyFee, @Commission, @CommissionPerc, @AgentCodeID,
@BranchID, @InsurerID, @SystemID,'Thabo'

SET @RowCount = @RowCount + 1
END


DELETE FROM tblMIS_PolicyDetail
WHERE PremiumVestedIncl = 0
AND Commission = 0
AND PremiumPolicyFee=0

---santam direct have been unable to provide policy fee details
---since mar 2006. therefore we are copying the policyfee from march to
---subsequent months as a provision
-- update tblMIS_PolicyDetail
-- set PremiumPolicyFee = M.PremiumPolicyFee
-- from tblMIS_SantamDirectPFMar2006 M inner join
-- tblMIS_PolicyDetail on tblMIS_PolicyDetail.PolicyId = M.PolicyId
-- where TransactionDateChar = @TransactionDateChar

END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000