Link to home
Create AccountLog in
Avatar of shyamaladevib
shyamaladevibFlag for United States of America

asked on

Convert this to case statement

Hi Experts,
I need help with conveting the following logic to case statements:
                      update CA_SUM_SpendTrendByAffiliateID
      set SpendTrendInd = 0
      where Period1Val = Period2Val
      
      update CA_SUM_SpendTrendByAffiliateID
      set SpendTrendInd = -1
      where Period1Val > Period2Val or (Period2Val is null and Period1Val is not null)
      
      update CA_SUM_SpendTrendByAffiliateID
      set SpendTrendInd = 1
      where Period1Val < Period2Val or (Period1Val is null and Period2Val is not null)
      
      update CA_SUM_SpendTrendByAffiliateID
      set SpendTrendInd = 0
      where SpendTrendInd is null
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
try

update CA_SUM_SpendTrendByAffiliateID
set SpendTrendInd  case  when (SpendTrendInd = Period1Val or Period1Val is Null)
  then 0
when (Period1Val > Period2Val or (Period2Val is null and Period1Val is not null))
then -1

when (Period1Val < Period2Val or (Period1Val is null and Period2Val is not null))
then 1
else SpendTrendInd
end

oops, didn't see yours Cyberkiwi.

mine is missing the =

sorry
set SpendTrendInd  = case...
Hope this helps. Make yourself the WHEN conditions and it should be in right order.
ex.
WHEN N < 10
WHEN N < 5 (This case should come first as it also true to the first condition)

Update CA_SUM_SpendTrendByAffiliateID
SET SpendTrendInd =
( CASE
      WHEN Period1Val = Period2Val THEN 0
      WHEN Period1Val > Period2Val or (Period2Val is null and Period1Val is not null) THEN -1
      WHEN Period1Val < Period2Val or (Period1Val is null and Period2Val is not null) THEN 1
      WHEN SpendTrendInd is null THEN 0
  END
)
WHERE <condition, if there is any common one to the all above>;
OOPs. forget about the version. It will support from SQL Server 2005 version onwards.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.