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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply