Set a Variable Inside a Case Statement

  • Jason Leadbetter

    SSC-Addicted

    Points: 423

    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

    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

    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

    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

    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: 993905

    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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply