Set a Variable Inside a Case Statement

• Jason Leadbetter

  SSC-Addicted

  Points: 423

  February 23, 2010 at 8:55 pm

  #216004

  I need to set a variable inside a Case that is inside a Select. I am using this is for counting purposes.

  Example:

  SELECT DateCreatedAtMidnight,

  SaleInvoiceID,

  InvoiceIDByStore,

  GlobalProductID,

  ProductIdentifier,

  CategoryNumber,

  Description,

  SerialNumber,

  Priority,

  Quantity,

  UnitPrice,

  ContractNumber,

  QuantityRefunded,

  AssociationNumber,

  CASE

  WHEN CategoryNumber = '101010111414' THEN (@PhoneGroup + 1)

  WHEN CategoryNumber = '101010111414' THEN SET @PhoneGroup = (@PhoneGroup + 1) --< or something like this[/color]

  ELSE @PhoneGroup

  END AS GroupID

  FROM myTable

  Thanks,

  Jason

• Kingston Dhasian

  SSCoach

  Points: 19794

  February 23, 2010 at 10:45 pm

  #1124090

  See if this helps..

  IF OBJECT_ID( 'tempdb..#tmpMyTable' ) IS NOT NULL

  DROP TABLE #tmpMyTable

  DECLARE @iGroupID INT

  SET @iGroupID = 1

  SELECT *, NULL AS GroupID

  INTO #tmpMyTable

  UPDATE #tmpMyTable

  SET @iGroupID = GroupID = CASE WHEN CategoryNumber = '101010111414' THEN @iGroupID + 1 ELSE @iGroupID END

  SELECT *

  FROM #tmpMyTable

  IF OBJECT_ID( 'tempdb..#tmpMyTable' ) IS NOT NULL

  DROP TABLE #tmpMyTable

  Also have a look at the following link to know about the method used

  http://www.sqlservercentral.com/articles/T-SQL/68467/

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Jason Leadbetter

  SSC-Addicted

  Points: 423

  February 23, 2010 at 11:35 pm

  #1124111

  Kingston!

  You are the King! Sorry...couldn't help myself. That worked like a charm.

  Thank you so very much.

  Jason

• Kingston Dhasian

  SSCoach

  Points: 19794

  February 24, 2010 at 12:42 am

  #1124136

  Glad that i could help you:-). But make sure you read the link i had provided. The article has a detailed explanation of the method and the situations when this may cause problems.

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• harini_vaidyanath

  Grasshopper

  Points: 11

  December 31, 2018 at 1:01 am

  #2017630

  Hello Jason and Kingston,
  I am working on a similar problem and am looking for your guidance. I am trying to create two variables and use them inside a case statement. 
  Here's an example of what I'm trying to do. I will highlight the problem using a # and that's where I will appreciate your help.

  drop table UX_Omni_visit_1219_IS

  SELECT visitid, hits, bounces, cartaddition, NULL AS interaction_score
  INTO UX_Omni_visit_1219_IS
  from UX_Omni_visit_1219
  where visitdate between '2018-10-01' and '2018-10-07'

  declare @median int
  declare @interaction_score int

  SET @median = (
  (SELECT MAX(hits) FROM
   (SELECT TOP 50 PERCENT hits from UX_Omni_visit_1219
  where visitdate between '2018-10-01' and '2018-10-07' and cartaddition > 0 ORDER BY hits) AS BottomHalf)
  +
  (SELECT MIN(hits) FROM
   (SELECT TOP 50 PERCENT hits from UX_Omni_visit_1219
  where visitdate between '2018-10-01' and '2018-10-07' and cartaddition > 0 ORDER BY hits) AS TopHalf)
  ) / 2

  UPDATE UX_Omni_visit_1219_IS
  SET @interaction_score = interaction_score = CASE when cartaddition > 0 then 1
                WHEN hits> @median THEN 0.9
                when bounces > 0 then 0
                else 0.9*(hits/@median)
                END

  # The @median in the case statements is not being used since I get only two values for interaction_score namely 0 and 1 once the query runs. I'm not sure why, would you have any thoughts?

• Jeff Moden

  SSC Guru

  Points: 994542

  December 31, 2018 at 8:29 am

  #2017650

  harini_vaidyanath - Monday, December 31, 2018 1:01 AM

  Hello Jason and Kingston,
  I am working on a similar problem and am looking for your guidance. I am trying to create two variables and use them inside a case statement. 
  Here's an example of what I'm trying to do. I will highlight the problem using a # and that's where I will appreciate your help.

  drop table UX_Omni_visit_1219_IS

  SELECT visitid, hits, bounces, cartaddition, NULL AS interaction_score
  INTO UX_Omni_visit_1219_IS
  from UX_Omni_visit_1219
  where visitdate between '2018-10-01' and '2018-10-07'

  declare @median int
  declare @interaction_score int

  SET @median = (
  (SELECT MAX(hits) FROM
   (SELECT TOP 50 PERCENT hits from UX_Omni_visit_1219
  where visitdate between '2018-10-01' and '2018-10-07' and cartaddition > 0 ORDER BY hits) AS BottomHalf)
  +
  (SELECT MIN(hits) FROM
   (SELECT TOP 50 PERCENT hits from UX_Omni_visit_1219
  where visitdate between '2018-10-01' and '2018-10-07' and cartaddition > 0 ORDER BY hits) AS TopHalf)
  ) / 2

  UPDATE UX_Omni_visit_1219_IS
  SET @interaction_score = interaction_score = CASE when cartaddition > 0 then 1
                WHEN hits> @median THEN 0.9
                when bounces > 0 then 0
                else 0.9*(hits/@median)
                END

  # The @median in the case statements is not being used since I get only two values for interaction_score namely 0 and 1 once the query runs. I'm not sure why, would you have any thoughts?

  This is a 2005 forum.  Are you actually using 2005 or something more recent?  If more recent, then which version?

  --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.
  "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
  "Change is inevitable... change for the better is not."
  When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉
  

  ---

  Helpful Links:
  How to post code problems
  Create a Tally Function (fnTally)