December 25, 2006 at 3:47 pm
HI,
i need help on my query.when i execute it i get accurate results for some months and inaccurate results for some months.Any suggestions on it
create table #tmp1
(PolicyNumber varchar(10) Collate SQL_Latin1_General_CP850_BIN
, TableCode varchar(3) Collate SQL_Latin1_General_CP850_BIN
, SeriesCode varchar(1) Collate SQL_Latin1_General_CP850_BIN
, IssueAge tinyint
, Gender char Collate SQL_Latin1_General_CP850_BIN
, EffectiveDate smalldatetime
, ReserveCase decimal
, ClaimNumber varchar(7) Collate SQL_Latin1_General_CP850_BIN
, LastName varchar(20) Collate SQL_Latin1_General_CP850_BIN
, FirstName varchar(15) Collate SQL_Latin1_General_CP850_BIN
, LossDate datetime
, InClaimFile bit
, Recaptured varchar(10) Collate SQL_Latin1_General_CP850_BIN
)
Insert Into #tmp1 (PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, ReserveCase)
Select PolicyNumber, TableCode, SeriesCode, IssueAge, Gender, Sum(ReserveCase*0.8) AS ReserveCase
From GE_Reserve
where Month_of_file = @Month_of_file_filter And ReserveCase <> 0
Group By PolicyNumber, TableCode, SeriesCode, IssueAge, Gender--, Month_of_file
Update #tmp1
Set ClaimNumber = c.ClaimNumber,LastName = c.LastName,FirstName = c.FirstName,LossDate = c.LossDate,InClaimFile = 1,EffectiveDate=c.EffectiveDate
From #tmp1 r Inner Join GE_Claim c
On r.PolicyNumber = c.PolicyNumber Collate SQL_Latin1_General_CP850_BIN
And r.TableCode = c.TableCode Collate SQL_Latin1_General_CP850_BIN
And r.SeriesCode = c.SeriesCode Collate SQL_Latin1_General_CP850_BIN
And r.IssueAge = c.IssueAge Collate SQL_Latin1_General_CP850_BIN
And r.Gender = c.Gender Collate SQL_Latin1_General_CP850_BIN
Where c.LossDate in (Select Max(LossDate) from GE_Claim
Where PolicyNumber = c.PolicyNumber And TableCode = c.TableCode And SeriesCode = c.SeriesCode And IssueAge = c.IssueAge And Gender = c.Gender
And Month_of_file = @Month_of_file_filter
Group By PolicyNumber, TableCode, SeriesCode, IssueAge, Gender)
UPDATE #tmp1
SET EffectiveDate = ge.EffectiveDate
FROM #tmp1 t
INNER JOIN GE_Claim ge ON t.PolicyNumber = ge.PolicyNumber
and t.SeriesCode = ge.SeriesCode
and t.TableCode = ge.TableCode
and t.IssueAge = ge.IssueAge
and t.Gender = ge.Gender
Where t.EffectiveDate is null and ge.Month_of_file <= @Month_of_file_filter
declare @PolicyNumber varchar(10), @TableCode varchar(3), @SeriesCode varchar(1), @IssueAge tinyint, @Gender char(1)
declare @LossDate datetime
declare @month varchar(8) -- Need for proper sorting
declare cursor1 cursor Forward_only
for select PolicyNumber, TableCode, SeriesCode, IssueAge, Gender
from #tmp1 where LossDate is null
and EffectiveDate is not null
order by PolicyNumber, TableCode, SeriesCode, IssueAge, Gender
open cursor1
fetch next from cursor1 into @PolicyNumber, @TableCode, @SeriesCode, @IssueAge, @Gender
while @@Fetch_Status = 0
begin
declare Cursor2 cursor forward_only for
select Month_of_file, Max(LossDate) LossDate from GE_Claim
where PolicyNumber = @PolicyNumber and TableCode = @TableCode and SeriesCode = @SeriesCode
and IssueAge = @IssueAge and Gender = @Gender
and Month_of_file <= @Month_of_file_filter
Group By Month_of_file
order by Month_of_file Desc
Open Cursor2
fetch next from Cursor2 into @month, @LossDate
While @@Fetch_Status = 0
begin
if @LossDate Is not Null
fetch next from Cursor2 into @month, @LossDate
end
close Cursor2
deallocate Cursor2
fetch next from cursor1 into @PolicyNumber, @TableCode, @SeriesCode, @IssueAge, @Gender
end
close cursor1
deallocate cursor1
--select * from #tmp1 where LossDate is null and EffectiveDate is not null
Update #tmp1
Set Recaptured = 'Recaptured'
Where LossDate Is Not Null
And DateAdd(yyyy, 10, EffectiveDate) < LossDate
Update #tmp1
Set Recaptured = 'Reinsured'
Where LossDate Is Not Null
And Recaptured Is Null
December 25, 2006 at 7:49 pm
It's the 2nd time you post that question. Looks like you didn't get much help last time. What I'm proposing is you change something in the question.
1 - Dump the current code.
2 - POST the DDL
3 - Sample data
4 - Required output
5 - Let our imagination run on this one.
TIA.
December 26, 2006 at 9:40 am
Knowing what is inaccurate and what is accurate is critical. IF you look at the results, can you tell which section of the code is not working?
Follow ninja's advice above.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply