Stored Procedure... pls help

[Deven Oza]

Create PROCEDURE NextGen_Convert Date_Deven AS
declare @DLSStart smalldatetime

set @DLSStart = (select dbo.fn_GetDayli ghtSavingsTimeS tart(convert(va rchar,datepart( year,getdate()) )))
/* please Note this funcution (fn_GetDaylight SavingsTimeStar t) returns values is 2008-03-09 02:00:00 */


update NexGen_New_Loss es_Deven
set F37_CLAIM_ENTER ED_S_DT_conv =

CASE
WHEN XM_REPORTING_MC O_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MC O_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MC O_NM = 'INDIANAPOLIS EXPRESS'
AND F37_CLAIM_ENTER ED_S_DT <= @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -5 ,F37_CLAIM_ENTE RED_S_DT ) --WINTER
WHEN XM_REPORTING_MC O_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MC O_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MC O_NM = 'INDIANAPOLIS EXPRESS'
AND F37_CLAIM_ENTER ED_S_DT > @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -4 ,F37_CLAIM_ENTE RED_S_DT )

ELSE NULL

END
------------------------------------
when I run this stored procedure, it updates only first case of WHEN for each state, no matter what date I entered here. for example, always it reduce 5 hours for hudson, charlotte and Indianapolis and not reducing 4 hours for any date of year. I have a doubt that stored procedure doesn't like my @DLSStart variable. please advice...

Thanks
Deven

[ck9663]

Group your conditions together by using parenthesis.

It's similar to

2 * 3 + 5 = 11

vs

2 * (3 + 5) = 16

Group your OR together and leave the AND outside...

update NexGen_New_Losses_Deven
set F37_CLAIM_ENTERED_S_DT_conv =

CASE
WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
AND F37_CLAIM_ENTERED_S_DT <= @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -5 ,F37_CLAIM_ENTERED_S_DT ) --WINTER
WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
AND F37_CLAIM_ENTERED_S_DT > @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -4 ,F37_CLAIM_ENTERED_S_DT )

ELSE NULL

END

Happy Coding!

-- CK

[Deven Oza]

Oh my God!!!! It's working now, yes it is working... Thanks a lot!!!!
Happy Coding :-)

Group your conditions together by using parenthesis.

It's similar to

2 * 3 + 5 = 11

vs

2 * (3 + 5) = 16

Group your OR together and leave the AND outside...

update NexGen_New_Losses_Deven
set F37_CLAIM_ENTERED_S_DT_conv =

CASE
WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
AND F37_CLAIM_ENTERED_S_DT <= @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -5 ,F37_CLAIM_ENTERED_S_DT ) --WINTER
WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
AND F37_CLAIM_ENTERED_S_DT > @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -4 ,F37_CLAIM_ENTERED_S_DT )

ELSE NULL

END

Happy Coding!

-- CK