February 23, 2010 at 8:55 pm
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
February 23, 2010 at 10:45 pm
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/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 23, 2010 at 11:35 pm
Kingston!
You are the King! Sorry...couldn't help myself. That worked like a charm.
Thank you so very much.
Jason
February 24, 2010 at 12:42 am
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 31, 2018 at 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?
December 31, 2018 at 8:29 am
harini_vaidyanath - Monday, December 31, 2018 1:01 AMHello 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 intSET @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)
) / 2UPDATE 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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy