Help with CASE Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with CASE Statement

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-13 : 09:24:29

This was working fine until I needed to add an additional CASE @Level statement. I need to run a different select statement based on the level number passed to it. Thanks

CREATE PROCEDURE p_CashSample2
@Level Int = NULL,
@RecordCount Int = NULL OUTPUT
AS
SELECT @RecordCount = COUNT(*)
FROM PopulationBondsCash_Temp

CASE @Level
WHEN 1 THEN
-- *** Level 1 ***
SELECT TOP (CASE WHEN @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @RecordCount BETWEEN 9 AND 15 THEN 2
ELSE 125
END)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID()

WHEN 2 THEN

-- *** Level 2 ***
SELECT TOP (CASE WHEN @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @RecordCount BETWEEN 9 AND 15 THEN 3
ELSE 1250
END)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID()

WHEN 3 THEN
-- *** Level 3 ***
SELECT TOP (CASE WHEN @RecordCount BETWEEN 2 AND 8 THEN 3
WHEN @RecordCount BETWEEN 9 AND 15 THEN 5
ELSE 2000
END)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID()

END

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-10-13 : 10:44:23

[code]
SET @RecordCount = (SELECT COUNT(*) FROM PopulationBondsCash_Temp);

SELECT TOP
(
CASE
WHEN @Level = 1 AND @RecordCount BETWEEN 2 AND 15 THEN 2
WHEN @Level = 1 THEN 125
WHEN @Level = 2 AND @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND @RecordCount BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 THEN 1250
WHEN @Level = 3 AND @RecordCount BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND @RecordCount BETWEEN 9 AND 15 THEN 5
ELSE 2000
END
)
*
FROM PopulationBondsCash_Temp
ORDER BY NEWID();
[/code]

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-13 : 14:50:49

That's much better. Thanks

I need to add one more layer of complexity if you don't mind.

Instead of basing the TOP value based on the recordcount on the total number of records in the whhole table, I need to base the TOP value based on the number of records per UserID.

Something like

SET @RecordCount = (SELECT COUNT(*) FROM PopulationBondsCash_Temp)
FROM
PopulationBondsCash_Temp
GROUP BY UserID

SELECT TOP
(
CASE
--Level 1
WHEN @Level = 1 AND @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @Level = 1 AND @RecordCount BETWEEN 9 AND 15 THEN 2
WHEN @Level = 1 AND @RecordCount BETWEEN 16 AND 25 THEN 3
--Level 2
WHEN @Level = 2 AND @RecordCount BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND @RecordCount BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 AND @RecordCount BETWEEN 16 AND 25 THEN 5
--Level 3
WHEN @Level = 3 AND @RecordCount BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND @RecordCount BETWEEN 9 AND 15 THEN 5
WHEN @Level = 3 AND @RecordCount BETWEEN 16 AND 25 THEN 8
END
)
--ActivityDate, AlienFileLocation, UserID, BondAmount, BondNumber, COUNT(UserID) OVER (Partition by UserID) AS UserCount
FROM
PopulationBondsCash_Temp
ORDER BY
NEWID()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 01:48:53

[code]
SELECT TOP
(
CASE
--Level 1
WHEN @Level = 1 AND Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 1 AND Cnt BETWEEN 9 AND 15 THEN 2
WHEN @Level = 1 AND Cnt BETWEEN 16 AND 25 THEN 3
--Level 2
WHEN @Level = 2 AND Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND Cnt BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 AND Cnt BETWEEN 16 AND 25 THEN 5
--Level 3
WHEN @Level = 3 AND Cnt BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND Cnt BETWEEN 9 AND 15 THEN 5
WHEN @Level = 3 AND Cnt BETWEEN 16 AND 25 THEN 8
END
)
--ActivityDate, AlienFileLocation, UserID, BondAmount, BondNumber, COUNT(UserID) OVER (Partition by UserID) AS UserCount
FROM
(SELECT COUNT(1) OVER (PARTITION BY UserID) AS Cnt,* FROM
PopulationBondsCash_Temp)t
ORDER BY
NEWID()
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-14 : 09:56:26

Hi

Thanks for this. I'm getting the error

The reference to column "Cnt" is not allowed in the argument of the TOP clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.

ALTER PROCEDURE [dbo].[p_CashSample]
@Level Int = NULL
AS

SELECT TOP
(
CASE
--Level 1
WHEN @Level = 1 AND Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 1 AND Cnt BETWEEN 9 AND 15 THEN 2
WHEN @Level = 1 AND Cnt BETWEEN 16 AND 25 THEN 3
--Level 2
WHEN @Level = 2 AND Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND Cnt BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 AND Cnt BETWEEN 16 AND 25 THEN 5
--Level 3
WHEN @Level = 3 AND Cnt BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND Cnt BETWEEN 9 AND 15 THEN 5
WHEN @Level = 3 AND Cnt BETWEEN 16 AND 25 THEN 8
END
)
ActivityDate, AlienFileLocation, UserID, BondAmount, BondNumber
FROM
(SELECT COUNT(1) OVER (PARTITION BY UserID) AS Cnt,* FROM
PopulationBondsCash_Temp)t
ORDER BY
NEWID()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:39:53

[code]
;With CTE
AS
(
SELECT COUNT(1) OVER (PARTITION BY UserID) AS Cnt,* FROM
PopulationBondsCash_Temp
)

SELECT t.*
FROM (SELECT DISTINCT UserId,Cnt FROM CTE)c
CROSS APPLY
(
SELECT TOP
(
CASE
--Level 1
WHEN @Level = 1 AND c.Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 1 AND c.Cnt BETWEEN 9 AND 15 THEN 2
WHEN @Level = 1 AND c.Cnt BETWEEN 16 AND 25 THEN 3
--Level 2
WHEN @Level = 2 AND Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND Cnt BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 AND Cnt BETWEEN 16 AND 25 THEN 5
--Level 3
WHEN @Level = 3 AND c.Cnt BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND c.Cnt BETWEEN 9 AND 15 THEN 5
WHEN @Level = 3 AND c.Cnt BETWEEN 16 AND 25 THEN 8
END
) *
FROM
CTE
)t
ORDER BY
NEWID()
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-14 : 13:23:53

Sorry - I must be doing something dumb - Error message: "'CTE' is not a recognized option."

ALTER PROCEDURE [dbo].[p_CashSample]
@Level Int = NULL

With CTE
AS
(
SELECT COUNT(1) OVER (PARTITION BY UserID) AS Cnt,* FROM
PopulationBondsCash_Temp
)

SELECT t.*
FROM (SELECT DISTINCT UserId,Cnt FROM CTE)c
CROSS APPLY
(
SELECT TOP
(
CASE
--Level 1
WHEN @Level = 1 AND c.Cnt BETWEEN 2 AND 8 THEN 2
WHEN @Level = 1 AND c.Cnt BETWEEN 9 AND 15 THEN 2
WHEN @Level = 1 AND c.Cnt BETWEEN 16 AND 25 THEN 3

END
) *
FROM
CTE
)t
ORDER BY
NEWID()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 13:35:35

are you using sql 2005 and above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-14 : 14:20:02

Yes, SQL Server 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 14:23:15

then it should work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-14 : 14:40:49

Sorry - I just needed to do this

ALTER PROCEDURE [dbo].[p_CashSample]
@Level Int = NULL
AS
With CTE
AS
(
....

Thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-15 : 02:11:35

oh...you missed AS...didnt spot that as I was looking into CTE code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources