August 19, 2011 at 7:55 am
Hi there,
I am trying to extract data from a table and categorise it so that I can create a graph from the results.
Basically there are several problem codes that are assigned to calls in our call management system for one area. They are requesting a breakdown of all the calls under 4 categories, but 2 of the categories have multiple problem codes underneath them.
I started to use the CASE clause (as per the below) to do this, but it's not quite working as I want it to. Basically it's still splitting the items out by the problem code as I can't find a way to tell it to do this by category with the query:
select count (*) as 'Total', problemcode, category = CASE problemcode
WHEN 'WEB0-SU01' THEN 'Candidate Site Issues'
WHEN 'WEB0-SU02' THEN 'Recruiter Site Issues'
WHEN 'WEB0-CNDD-PPLF-MNDN' THEN 'Candidate'
WHEN 'WEB0-CNDD-RSTR' THEN 'Candidate'
WHEN 'WEB0-RCRT-DMN0-SRSD' THEN 'Recruiter'
WHEN 'WEB0-RCRT-DMN0-SRSL' THEN 'Recruiter'
WHEN 'WEB0-RCRT-DMN0-SRS1' THEN 'Recruiter'
END
from database1
where month (date_time) = '08'
and year (date_time) = '2011'
and costcenter like 'costcentre%'
group by problemcode
I want the numbers to be of the categories so there ends up with 4 values, not lots of values (the above is a small sample of the codes, there are about 50 of them).
I have only added the group by problemcode item because that's the only way to get the numbers for now.
Can anyone help with this and point me in the right direction?
Many thanks in advance for your help
August 19, 2011 at 8:06 am
You must also group by the case statement.
select count (*) as 'Total', problemcode, category = CASE problemcode
WHEN 'WEB0-SU01' THEN 'Candidate Site Issues'
WHEN 'WEB0-SU02' THEN 'Recruiter Site Issues'
WHEN 'WEB0-CNDD-PPLF-MNDN' THEN 'Candidate'
WHEN 'WEB0-CNDD-RSTR' THEN 'Candidate'
WHEN 'WEB0-RCRT-DMN0-SRSD' THEN 'Recruiter'
WHEN 'WEB0-RCRT-DMN0-SRSL' THEN 'Recruiter'
WHEN 'WEB0-RCRT-DMN0-SRS1' THEN 'Recruiter'
END
from database1
where month (date_time) = '08'
and year (date_time) = '2011'
and costcenter like 'costcentre%'
group by
CASE problemcode
WHEN 'WEB0-SU01' THEN 'Candidate Site Issues'
WHEN 'WEB0-SU02' THEN 'Recruiter Site Issues'
WHEN 'WEB0-CNDD-PPLF-MNDN' THEN 'Candidate'
WHEN 'WEB0-CNDD-RSTR' THEN 'Candidate'
WHEN 'WEB0-RCRT-DMN0-SRSD' THEN 'Recruiter'
WHEN 'WEB0-RCRT-DMN0-SRSL' THEN 'Recruiter'
WHEN 'WEB0-RCRT-DMN0-SRS1' THEN 'Recruiter'
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 19, 2011 at 9:08 am
Jason, you made it look so easy! Thanks for this.....
August 25, 2011 at 8:12 am
Sorry to bring this one up again.
This worked perfectly, however I have a list of NULL values and have tried all combinations of things with is not NULL to try and get rid of these in the output.
Can anyone offer any more advice? Thank you!
August 25, 2011 at 2:07 pm
Can you just exclude those in your where clause?
and MyFieldWithNull IS NOT NULL
Or did I miss the point of your question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 2:13 pm
I'm going to add to what Jason posted for you to use, with a couple of enhancements:
--Perform grouping in the CTE on the minimum # of columns needed to actually group the results
;WITH cte As
(
SELECT Count(*) As [Total],
ProblemCode
FROM [Database]
--Allows index seek:
WHERE Date_Time >= '2011-08-01' AND
Date_Time < '2011-09-01' AND
CostCenter LIKE 'costcentre%'
GROUP BY ProblemCode
)
--Now introduce the longer CASE statement to provide an additional column in the results
SELECT [Total], ProblemCode,
Category =
CASE
WHEN ProblemCode = 'WEB0-SU01'THEN 'Candidate Site Issues'
WHEN ProblemCode = 'WEB0-SU02'THEN 'Recruiter Site Issues'
--Allows for add'l problemcodes w/o modifying script
WHEN ProblemCode LIKE 'WEB0-CNDD%'THEN 'Candidate'
WHEN ProblemCode LIKE 'WEB0-RCRT%'THEN 'Recruiter'
END
FROM cte;
First, you want to GROUP BY a small number of columns, otherwise you wind up repeating large sections of code (like the long CASE statement), which I find to be a major source of coding errors -- if you change the CASE statement, you have to remember to change it in both places identically. By using a CTE, you only have to add the CASE clause once, and you keep it out of your GROUP BY, where it doesn't really belong.
Next, you're almost always better off not using arithmetic functions in your WHERE clause, b/c then you render an index on that column unusable. Google SARG-ability of WHERE clauses.
Finally, I elected to use the other form of CASE, so you don't have to type out all the forms of "WEB0-CNDD" and "WEB0-RCRT". This may be of dubious benefit, but I figured I'd suggest it.
Have I misunderstood? If you only want the results to be 1 row for each "Category", then maybe something like this? (Again, limiting the CASE statement to once....)
;WITH cte As
(
SELECT
Category =
CASE
WHEN ProblemCode = 'WEB0-SU01'THEN 'Candidate Site Issues'
WHEN ProblemCode = 'WEB0-SU02'THEN 'Recruiter Site Issues'
--Allows for add'l problemcodes w/o modifying script
WHEN ProblemCode LIKE 'WEB0-CNDD%'THEN 'Candidate'
WHEN ProblemCode LIKE 'WEB0-RCRT%'THEN 'Recruiter'
END
FROM [Database]
--Allows index seek:
WHERE Date_Time >= '2011-08-01' AND
Date_Time < '2011-09-01' AND
CostCenter LIKE 'costcentre%'
)
SELECT Count(*) As [Total], Category
FROM cte
GROUP BY Category;
Rich
Added this P.S.:
1. If you have no rows per category, the category won't show up at all
2. Are you familiar with common table expressions (CTEs)? If not, make sure you have no code between the preceding semi-colon and the start of the CTE declaration ("WITH cte As..."). It's a gotcha when starting out with them. Also, you can only query them once, and only in the statement immediately following. Think of them as a one-off, one-use, temp table.
August 29, 2011 at 6:39 pm
Have you considered using a mapping table?
Rather than hard coding reference data into a query store it in a table. That way you can reuse the same Category logic multiple times and it simplifies the select statement and keeps your reference data in the database.
First you need a Problem Category table:
CREATE TABLE [database].[schema].[ProblemCategory](
[ProblemCategoryCode] [int] IDENTITY(1,1) NOT NULL,
[ProblemCategoryName] [varchar](50) NOT NULL,
CONSTRAINT [PK_ProblemCategory] PRIMARY KEY CLUSTERED ([ProblemCategoryCode] ASC)
)
Then ideally (scenario A) you would add the Category Code column (FK) to your original table:
[ProblemCategoryCode] int NOT NULL
Or alternatively (scenario B) create the mapping table so:
CREATE TABLE [dbo].[ProblemCategoryMapping](
[ProblemCode] [varchar](20) NOT NULL,
[ProblemCategoryCode] int NOT NULL,
CONSTRAINT [PK_ProblemCategoryMapping] PRIMARY KEY CLUSTERED ([ProblemCode] ASC, [ProblemCategoryCode] ASC)
)
I've used an integer code for the categories, but to be more consistent with the varchar problem code you could make your category codes varchar as well and give meaningful codes.
Then load up the ProblemCategory table with your categories and provide values for your new column or your mapping table.
In scenario A the SQL becomes:
SELECT Count(OT.ProblemCode) As [Total],
PC.ProblemCategoryName
FROM [OriginalTable] OT
INNER JOIN [ProblemCategory] As PC ON OT.ProblemCategoryCode = PC.ProblemCategoryCode
-- Allows index seek:
WHERE OT.Date_Time >= '2011-08-01' AND
OT.Date_Time < '2011-09-01' AND
OT.CostCenter LIKE 'costcentre%'
GROUP BY PC.ProblemCategoryCode
Or, in scenario B:
SELECT Count(t1.ProblemCode) As [Total],
PC.ProblemCategoryName
FROM [OriginalTable] t1
INNER JOIN [ProblemCategoryMapping] As PCM ON t1.ProblemCode = PCM.ProblemCode
INNER JOIN [ProblemCategory] As PC ON PCM.ProblemCategoryCode = PC.ProblemCategoryCode
-- Allows index seek:
WHERE t1.Date_Time >= '2011-08-01' AND
t1.Date_Time < '2011-09-01' AND
t1.CostCenter LIKE 'costcentre%'
GROUP BY PC.ProblemCategoryCode
August 31, 2011 at 4:30 am
Thanks to all of you for your feedback/help it is appreciated!
I went with rmechaber in the end as I haven't played with cte's before and this looked challenging!
Unfortunately, my output result still had NULL values in it
TotalCategory
802NULL
6158Candidate
2149Candidate Site Issues
779Recruiter
42Recruiter Site Issues
I know I am an awkward customer, just thought I would be courteous and post back. I will carry on playing and keep you updated
August 31, 2011 at 6:45 am
karen.blake (8/31/2011)
Thanks to all of you for your feedback/help it is appreciated!I went with rmechaber in the end as I haven't played with cte's before and this looked challenging!
Unfortunately, my output result still had NULL values in it
TotalCategory
802NULL
6158Candidate
2149Candidate Site Issues
779Recruiter
42Recruiter Site Issues
I know I am an awkward customer, just thought I would be courteous and post back. I will carry on playing and keep you updated
Glad I could help.
I didn't address the issue of NULLs because Sean Lange did in an earlier reply to you here . Simply add a WHERE clause to exclude them.
I'm guessing, since you didn't post any data, that you have rows with a NULL (i.e., missing) ProblemCode. So the query doesn't know what to do with them other than to lump them all together. This query will find them for you:
SELECT *
FROM Database1
WHERE ProblemCode IS NULL;
If you don't want them included in the results, simply add this to your WHERE clause: AND ProblemCode IS NOT NULL. You cannot test for NULL by using an equals sign; this doesn't work: AND ProblemCode <> NULL.
I'm guessing you might need those results though, and you can lump them together in a more meaningful way by changing the CASE statement in the second code I posted above with this:
CASE
WHEN ProblemCode IS NULL THEN '!! MISSING CODE !!'
WHEN ProblemCode = 'WEB0-SU01' THEN 'Candidate Site Issues'
WHEN ProblemCode = 'WEB0-SU02' THEN 'Recruiter Site Issues'
-- Allows for add'l problemcodes w/o modifying script
WHEN ProblemCode LIKE 'WEB0-CNDD%' THEN 'Candidate'
WHEN ProblemCode LIKE 'WEB0-RCRT%' THEN 'Recruiter'
END
Note that this is another benefit of the second form of the CASE statement, where we don't initially specify the value being CASE'd. This is called the "searched CASE" form, and you can read about it here. It allows you to test for NULLs as just another condition it's acting on.
Post back if that's not what you're looking for.
Rich
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply