Urgent Expert level Help on a query
-
Hi ,
i need help on this .when i run this query i use Month_of_file as a parameter it works fine but the only problem is it gives me the
right result only for the month of june-06 , when i try to enter any other month such as september as in the query below i have entered '20060901'
the results does not matches.
drop table #tmp1
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 = '20060901' 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 = '20060901'
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 <= '20060901'
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 <= '20060901'
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
begin
Update #tmp1
Set LossDate = @LossDate
Where PolicyNumber = @PolicyNumber and TableCode = @TableCode and SeriesCode = @SeriesCode
and IssueAge = @IssueAge and Gender = @Gender
Break
end
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
-
What data type is Month_of_file?
Assuming it's datetime or smalldatetime, you might want to experiment with the DATEPART function.
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
-
it is datetime and i dont understand how to experiment with the DATEPART function .can you show me how to do it.
thanks
-
Since the column is datetime data type, it stores date/time date down to the millisecond. Assuming you're only interested in the MONTH, you can use DATEPART to strip out the month for purposes of comparison.
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
-
>>Where t.EffectiveDate is null and ge.Month_of_file <= '20060901'
Why are you using less than or equal to in this part of your join ?
What are you expecting SQL to do if the table tagged as "ge" has 2 or more rows that match the criteria, and you're updating those multiple rows into 1 row in #tmp1 ?
-
The column is called "Month_of_file".... why are you looking for just one day? Try this as your WHERE clause to return rows for the month of September 2006....
- where Month_of_file >= '20060901'
- AND Month_of_file < '20061001'
...do pay attention to what the second date actually is... I wrote it correctly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
... and why on Earth are you using a
cursor? Do this set based...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply