Using a variable with IN

Question

I have a query where I need to evaluate the contents of one field and return results based off the contents of a column in another table.

Because of what we are pulling I need to do that evaluation with in a case statement in the select statement.

The path that I've started down is to take the results I needed and put them into a variable in a comma delimited string and use the IN keyword. 

I need to sum the results and so using a subquery doesn't work and another approach I tried was using a derived table but that I couldn't seem to get that to work either.

For simplicity, I've put together some sample data and have a query that isn't the complete solution but provides generally what I'm trying to do.

Is there a way to use a variable within the IN keyword or maybe someone else has a different approach that may work.

Here is the query:

DECLARE @AdjustmentName VARCHAR(1000)

SET @AdjustmentName = ''

SELECT @AdjustmentName = COALESCE(@AdjustmentName + ', ','') + '''' + CAST(AdjustmentName AS VARCHAR) + '''' FROM #PointPartners WHERE AdjustmentName LIKE 'PP_%'

SELECT @AdjustmentName = SUBSTRING(@AdjustmentName, 2, Len(@AdjustmentName))

/*Query for the results.*/

SELECT

DATEPART(m, AdjustDateTime) MonthNum,

DATENAME(m, AdjustDateTime) MonthName,

DATEPART(yyyy, AdjustDateTime) YearNum,

SUM(CASE WHEN comments IN (@AdjustmentName) THEN PtsAdjusted END) AS PointPartnershipPtsAdjusted

FROM #Adjustment

GROUP BY

DATEPART(m, AdjustDateTime),

DATENAME(m, AdjustDateTime),

DATEPART(yyyy, AdjustDateTime)

Here is the table structure I used with some sample data:

IF OBJECT_ID('TempDB..#PointPartners') IS NOT NULL

DROP TABLE #PointPartners

CREATE TABLE #PointPartners

(

ID smallint IDENTITY(1,1) NOT NULL,

RedemptionName varchar(40),

AdjustmentName varchar(30),

PartnerCode varchar(30)

) ON [PRIMARY]

INSERT INTO #PointPartners VALUES ('Theisen''s Home Farm Auto', 'PP_Theisens', 'Theisen')

INSERT INTO #PointPartners VALUES ('Oky Doky Foods', 'PP_Oky_Doky', 'Oky')

INSERT INTO #PointPartners VALUES ('Floor Show', 'PP_Floor_Show', 'Floor')

INSERT INTO #PointPartners VALUES ('Ellis Appliance', 'PP_Ellis_Appliance', 'Ellis')

INSERT INTO #PointPartners VALUES ('Shamrock Jewelers', 'PP_Shamrock_Jewelers', 'Shamrock')

INSERT INTO #PointPartners VALUES ('Great Sounds Audio Video', 'PP_Great_Sounds', 'Great')

INSERT INTO #PointPartners VALUES ('Graham''s Style Shop', 'PP_Grahms', 'Grahm')

INSERT INTO #PointPartners VALUES ('Generic Comment', 'Positive Points Adjustment', 'Generic')

IF OBJECT_ID('TempDB..#Adjustment') IS NOT NULL

DROP TABLE #Adjustment

CREATE TABLE #Adjustment

(

Adjustment_ID int IDENTITY(1,1) NOT NULL,

AdjustDateTime datetime NOT NULL,

PtsAdjusted int NOT NULL,

Comments varchar(30)

) ON [PRIMARY]

INSERT INTO #Adjustment VALUES ('2/3/2009', 115, 'PP_Theisens')

INSERT INTO #Adjustment VALUES ('2/3/2009', 35, 'PP_Theisens')

INSERT INTO #Adjustment VALUES ('1/12/2009', 32, 'PP_Theisens')

INSERT INTO #Adjustment VALUES ('1/12/2009', 10, 'PP_Theisens')

INSERT INTO #Adjustment VALUES ('2/13/2009', 43, 'PP_Theisens')

INSERT INTO #Adjustment VALUES ('12/23/2008', 22, 'PP_Theisens')

INSERT INTO #Adjustment VALUES ('12/23/2008', 16, 'PP_Oky_Doky')

INSERT INTO #Adjustment VALUES ('2/3/2009', 10, 'PP_Oky_Doky')

INSERT INTO #Adjustment VALUES ('1/3/2009', 14, 'PP_Oky_Doky')

INSERT INTO #Adjustment VALUES ('12/31/2008', 10, 'PP_Oky_Doky')

INSERT INTO #Adjustment VALUES ('2/3/2009', 10, 'PP_Oky_Doky')

INSERT INTO #Adjustment VALUES ('1/5/2009', 6, 'PP_Oky_Doky')

INSERT INTO #Adjustment VALUES ('2/3/2009', 143, 'PP_Floor_Show')

INSERT INTO #Adjustment VALUES ('2/3/2009', 10, 'PP_Shamrock_Jewelers')

INSERT INTO #Adjustment VALUES ('2/18/2009', 426, 'PP_Floor_Show')

INSERT INTO #Adjustment VALUES ('2/18/2009', 1354, 'PP_Grahams')

INSERT INTO #Adjustment VALUES ('1/18/2009', 1073, 'PP_Ellis_Appliance')

INSERT INTO #Adjustment VALUES ('12/11/2008', 4556, 'PP_Ellis_Appliance')

INSERT INTO #Adjustment VALUES ('12/17/2008', 50000, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('12/17/2008', 500, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('1/16/2009', 127, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('1/17/2009', 81, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('2/17/2009', 8, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('2/17/2009', 90, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('2/3/2009', 12500, 'Pt Adj')

INSERT INTO #Adjustment VALUES ('1/10/2009', 25, 'Pt Adj')

Answer 1

You can not use variable IN. There are multiple approaches to solve your problem and in fact this problem is one of the most commonly asked on asp.net/forums website.

One simple approach would be to use some split function such as 
http://www.berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split
for example and then do something like

select * from myTable inner join dbo.ufn_alines(@myInCondition,',') Items on myTable.Field = Items.Item

That's the idea, let me know if you are comfortable with this approach.

Answer 2

 

I understand what you are doing but I put the items into the variable as a comma separated list so that I'd have them in a format that would work with the IN keyword.  I already have them split out in a table.

I ran into trouble using the table because I need to SUM the results within the CASE statement.  When using the original table that doesn't work because you can't perform an aggregate function on an expression containing subquery.

I tried getting around that by trying to use a derived table:

SUM(CASE WHEN comments IN (SELECT AdjustmentName FROM #PointPartners) AS SUB THEN PtsAdjusted END) AS PointPartnershipDollarsAdjusted

That, in theory, could work, but I can't seem to get the syntax right.  So that would be another viable approach if the original path I posted is a dead end.

Answer 3

I'm not sure about the whole purpose and idea of your query. Anyway, you may use LEFT JOIN, then your sum could be

SUM(CASE when myRightTable.PK IS NULL then 0 ELSE myField end) as SumAdjustments

Answer 4

Thanks for your help.  Given the example this works:

 

SELECT

DATEPART(m, AdjustDateTime) MonthNum,

DATENAME(m, AdjustDateTime) MonthName,

DATEPART(yyyy, AdjustDateTime) YearNum,

SUM(CASE WHEN p.ID IS NULL THEN 0 ELSE PtsAdjusted END) AS PointPartnershipPtsAdjusted

FROM #Adjustment a LEFT JOIN (SELECT ID, AdjustmentName FROM #PointPartners WHERE AdjustmentName Like 'PP_%') p ON a.comments = p.AdjustmentName

GROUP BY

DATEPART(m, AdjustDateTime),

DATENAME(m, AdjustDateTime),

DATEPART(yyyy, AdjustDateTime)

I'm going to try and incorporate it with the actual data and structure tomorrow to see if I get the expected results.  Thanks again.

Answer 5

You're welcome. BTW, you can use MONTH and YEAR functions instead of DATEPART. For me it's a bit easier to read, but it's just a personal preference.

Answer 6

Thanks for the pointer.  I like it.  Thanks again.