March 1, 2009 at 8:04 pm
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')
March 1, 2009 at 8:26 pm
You need a function to split your comma separated variable into a table variable.
CREATE FUNCTION [dbo].[SplitList]
(
@List varchar(8000),
@splitter varchar(2)
)
RETURNS
@ParsedList table
(
num int identity(1,1),
item varchar(1024)
)
AS
BEGIN
SET @List = @splitter + LTRIM(RTRIM(@List)) + @splitter
INSERT INTO @ParsedList (item)
SELECT SUBSTRING(@List,N+1,CHARINDEX(@splitter,@List,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@List) AND SUBSTRING(@List,N,1) = @splitter
RETURN
END
That function requires a tally table to be in your Db. See the following article for information on setting up a tally table.
http://www.sqlservercentral.com/articles/TSQL/62867/
Of course there are other ways to split a list....
Once you have your splitList function you can use it as below:
SELECT
DATEPART(m, AdjustDateTime) MonthNum,
DATENAME(m, AdjustDateTime) MonthName,
DATEPART(yyyy, AdjustDateTime) YearNum,
CASE WHEN comments IN (SELECT item from splitlist( @AdjustmentName , ',')) THEN PtsAdjusted END AS PointPartnershipPtsAdjusted
FROM #Adjustment
Are they the results you are after?
March 1, 2009 at 9:12 pm
Does the real #PointPartners have only 3 columns you want to join on? If not, how many?
This should be done with a UNION ALL rather than going through the overhead of string contatenation.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2009 at 9:16 pm
I understand what you are doing but I already have the items I need in a table. I put them 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 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.
March 1, 2009 at 9:25 pm
Jeff,
The real table has more than three columns to it, but the only one that I'm interested in is the AdjustmentName column (Where they begin with pp_). We want to sum our totals for all records in the adjustment table where the comments field matches an entry in the AdjustmentName column.
March 1, 2009 at 9:42 pm
Ben-gan had a great series on this in the last few SQLmags that maybe you should check out.
March 1, 2009 at 9:46 pm
I really don't understand why you even need the #PointPartners table. Since all you are doing is filtering that table where the AdjustmentName is like 'PP_%' - and, you have that value in the Adjustments table, why not do the filtering on the Adjustment table?
/*Query for the results.*/
SELECT
DATEPART(m, AdjustDateTime) MonthNum,
DATENAME(m, AdjustDateTime) MonthName,
DATEPART(yyyy, AdjustDateTime) YearNum,
SUM(PtsAdjusted) AS PointPartnershipPtsAdjusted
FROM #Adjustment
WHERE Comments Like 'PP_%'
GROUP BY
DATEPART(m, AdjustDateTime),
DATENAME(m, AdjustDateTime),
DATEPART(yyyy, AdjustDateTime)
I have to be missing something simple here...another option would be to join the tables:
/*Query for the results.*/
SELECT
DATEPART(m, AdjustDateTime) MonthNum,
DATENAME(m, AdjustDateTime) MonthName,
DATEPART(yyyy, AdjustDateTime) YearNum,
SUM(PtsAdjusted) AS PointPartnershipPtsAdjusted
FROM #Adjustment a
JOIN #PointPartners p ON p.AdjustmentName = a.Comments
GROUP BY
DATEPART(m, AdjustDateTime),
DATENAME(m, AdjustDateTime),
DATEPART(yyyy, AdjustDateTime)
And finally, if I am really missing something obvious - you could use CHARINDEX (although that will not perform as well because you will not be able to use any indexes):
/*Query for the results.*/
SELECT
DATEPART(m, AdjustDateTime) MonthNum,
DATENAME(m, AdjustDateTime) MonthName,
DATEPART(yyyy, AdjustDateTime) YearNum,
SUM(PtsAdjusted) AS PointPartnershipPtsAdjusted
FROM #Adjustment
WHERE CHARINDEX(',' + comments + ',', ',' + @AdjustmentName + ',') > 0
GROUP BY
DATEPART(m, AdjustDateTime),
DATENAME(m, AdjustDateTime),
DATEPART(yyyy, AdjustDateTime)
Note: with the CHARINDEX function you need to make sure the string you build begins and ends with the delimiter you are using or it won't match all items in the list.
One of those should work, unless I am missing something...;)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 1, 2009 at 11:02 pm
Jeffrey,
You aren't missing anything. I just didn't explain the whole picture well enough. The reason it won't work to do filtering on the adjustments table is because that field also has additional entries that begin with PP_ that we don't want included in the aggregate because they aren't valid for the point partners.
Sorry about leaving out that small piece of data. Kind of important.
March 1, 2009 at 11:17 pm
Lee, I'm not finding the series you are referring to. Could you provide a link? I'd like to check it out.
I also think that I have something that may work. I'll have to check it in the real system tomorrow and verify results, but it seems to give me what I want based on the test tables I've documented here.
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)
March 1, 2009 at 11:43 pm
ehlinger (3/1/2009)
Jeffrey,You aren't missing anything. I just didn't explain the whole picture well enough. The reason it won't work to do filtering on the adjustments table is because that field also has additional entries that begin with PP_ that we don't want included in the aggregate because they aren't valid for the point partners.
Sorry about leaving out that small piece of data. Kind of important.
Well, that would eliminate the first one - but the other two are still valid options. Since you have a list of valid entries, you can join to that list (or use CHARINDEX).
Is there some other reason why those two options won't work?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 1, 2009 at 11:52 pm
I think a variation of your second suggestion will work. Basically, from that suggestion I was able to get the solution I had previously posted.
Kind of embarassing. It is a rather simple solution and I think I was just too close to it at first and had developed a little tunnel vision on what "I thought" was the way to go.
Thanks for your help. 😀
March 2, 2009 at 12:12 am
Glad I could help and you were able to get something to work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply