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