Set a Variable Inside a Case Statement

Question

Post reply

Set a Variable Inside a Case Statement

Jason Leadbetter

SSC-Addicted

Points: 423
More actions
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

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

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

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 More actions · Answer 2

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: 19799 More actions · Answer 3

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 More actions · Answer 4

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: 1003863 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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