By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Stored Procedure... pls help

P: 53
Create PROCEDURE NextGen_ConvertDate_Deven AS
declare @DLSStart smalldatetime

set @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate()))))
/* please Note this funcution (fn_GetDaylightSavingsTimeStart) returns values is 2008-03-09 02:00:00 */


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
------------------------------------
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
Oct 23 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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...

Expand|Select|Wrap|Line Numbers
  1. update NexGen_New_Losses_Deven
  2. set F37_CLAIM_ENTERED_S_DT_conv =
  3.  
  4. CASE
  5. WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
  6. OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
  7. OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
  8. AND F37_CLAIM_ENTERED_S_DT <= @DLSStart -- 2008-03-09 02:00:00
  9. THEN dateadd (hour, -5 ,F37_CLAIM_ENTERED_S_DT ) --WINTER
  10. WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
  11. OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
  12. OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
  13. AND F37_CLAIM_ENTERED_S_DT > @DLSStart -- 2008-03-09 02:00:00
  14. THEN dateadd (hour, -4 ,F37_CLAIM_ENTERED_S_DT )
  15.  
  16. ELSE NULL
  17.  
  18. END
  19.  

Happy Coding!

-- CK
Oct 24 '08 #2

P: 53
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...

Expand|Select|Wrap|Line Numbers
  1. update NexGen_New_Losses_Deven
  2. set F37_CLAIM_ENTERED_S_DT_conv =
  3.  
  4. CASE
  5. WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
  6. OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
  7. OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
  8. AND F37_CLAIM_ENTERED_S_DT <= @DLSStart -- 2008-03-09 02:00:00
  9. THEN dateadd (hour, -5 ,F37_CLAIM_ENTERED_S_DT ) --WINTER
  10. WHEN (XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
  11. OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
  12. OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS')
  13. AND F37_CLAIM_ENTERED_S_DT > @DLSStart -- 2008-03-09 02:00:00
  14. THEN dateadd (hour, -4 ,F37_CLAIM_ENTERED_S_DT )
  15.  
  16. ELSE NULL
  17.  
  18. END
  19.  

Happy Coding!

-- CK
Oct 24 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.