December 14, 2010 at 6:34 pm
Hi
I have a view that returns help desk calls.
One of the categories I can group on is Service Line (eg Application, Network, Hardware, User etc) - this field is called Service Line 1.
Each Service Line can be broken down into subsections (eg SAP, Printers, LAN, WAN). We call this Service Line 2.
I'm trying to write a query which counts the number of calls per Service Line and returns the top 6. The query also needs to return the number of calls of the top 3 Service Line 2's for each of the top 6 Service Lines. IE, we're looking to see what service lines give us the top call numbers
So....result will be sonething like:
ServiceLine1 ServiceLine2 SupportCalls
Application SAP 800
Application SAP6 745
Application Other 700
Network LAN 552
Network WAN 450
Network ADSL 356
Hardware Printers 440
Hardware Desktop 420
Hardware OKI Printers 400
etc
Would also he helpful if I could put a unique row number against each row, as i'll be using the data to put into specific datafields in a diagram in SSRS.
Any ideas?
Thanks
December 14, 2010 at 7:40 pm
paul.beattie (12/14/2010)
Any ideas?
Yes.
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 17, 2010 at 8:22 pm
Hi Wayne
My apologies. I'm new to posting questions. Also I'm just a simple support person who has been assigned to write some reports, not a DBA; I don't know how to make tables etc. I have picked up basic SQL to write select statements which i use with SSRS reports.
I wrote the following to get the information I needed at a basic level. It retrieves info from a View. The report uses this query to count how many calls were logged against each service line and populate a drill-down report which shows groups call totals first by [Service Line Key 1], then drilling down to a second group which counts the calls to their sub-sections, [Service Line Key 2]
SELECT
[Service Line Key 1]
,[Service Line Key 2]
,[Call Logged Date]
,[Call Number]
,[Call Logged Date] As TitleDate
FROM SDM_Calls_Last_Year
WHERE
[Call Type] = @CallType
However what I need to produce is not a drill down showing everything, but a graphical representation (fishbone/Ishikawa) just showing the number of calls related to the top 6 [Service Line Key 1]’s, (which may change order/fall out of the table on a month to month basis), and underneath each of the [Service Line Key 1]'s, the top 3 [Service Line Key 2]’s for each. At the moment, I copy this data manually from the drill-down report into an Excel workbook. I've attached a pdf of the excel fishbone.
If I can get a SQL query to produce the 24 numbers I need, each in a separate field, I can design and populate a fishbone using textboxes in a SSRS List and everything is automated – yay. I've made some futile attampts as I'm really keen to learn how to do more difficult select statements, but would be embarrassed to show you. A result set such as the attached second pdf would be perfect.
Many thanks in advance for whatever assistance you can provide.
December 18, 2010 at 4:06 am
If you could provide a ready to use result set based on the query you have below, I think we can design the table on our side so you don't have to deal with the create statement.
But withou any sample data and expected result based on those samples there's little we can do.
Please take the time to read and follow the instructions given in the first article in my signature on how to post sample data (except the DDL creation).
Since you know how to design a SELECT statement I'm sure the guide will help you to come up with a INSERT INTO ... SELECT ... UNION ALL.
December 19, 2010 at 9:50 pm
Hi
I followed the instructions and made the following select statement which worked! So your instructions are obviously idiot-proof. I’ve copied the result set of my first Union All statement into the Word doc 'Sample Date' which is attached.
SELECT 'SELECT '
+ QUOTENAME([Service Line Key 1],'''')+','
+ QUOTENAME([Service Line Key 2],'''')+','
+ QUOTENAME([Call Logged Date],'''')+','
+ QUOTENAME([Call Number],'''')
+ ' UNION ALL' FROM SDM_Calls_Last_Year
Copying from the View’s schema, the fields are of the following type:
[Service Line Key 1] is Varchar(15),null
[Service Line Key 2] is VarChar(15),null
[Call Logged Date] is Datetime, null
[Call Number] is Int, null
The other items attached are an Excel worksheet showing the desired result set. In the Excel workbook. I’ve also included the fish diagram with the sample data in it – currently I manually enter it into Excel from a drill down report in SSRS which groups Service Line 1, then drills down to grouped Service Line 2 (attached is a pdf snapshot of that report). My hope is to design the fish diagram in a SSRS freeform list, thus automating the process. To do this I need the results to turn up in a static result set rather than a dynamic one.
I hope this is as requested. Let me know if I’ve got anything in an incorrect format.
December 20, 2010 at 2:42 am
Did you see this article ?
December 22, 2010 at 9:33 pm
Hi
I read the link and had a lot of fun with the code bringing back lots of interesting results but none unfortunately exactly as I needed it.
Problems are:
1. I need a count of the occurances before they are ranked. Played about with groupings etc and putting counts into the partition formula but couldn't get a correct count.
2. I need a static result set rather than a dynamic one as the results are going into a free form diagram in SSRS. With a static result set, with each SQL field only returning one result, I can link each SSRS expression box to a SQL field and have it display the number returned.
December 23, 2010 at 1:48 am
If i understand you correct , something like this ?
with cteCallCount
as
(
select ServiceLine1,ServiceLine2,COUNT(*) as CallCount
from SDM_Calls_Last_Year
group by ServiceLine1,ServiceLine2
),
cteCallServLine1Count
as
(
Select ServiceLine1,ServiceLine2,CallCount,
SUM(callcount) over (PARTITion by ServiceLine1) as ServiceLine1Count
from cteCallCount
)
,
cteCallRanking
as
(
Select ServiceLine1,ServiceLine2,CallCount, ServiceLine1Count,
DENSE_RANK() over (Order by ServiceLine1Count desc,ServiceLine1 asc) as Ranking /* Ignoring Possibility of ties here , serviceline1 is used to descrimintate */
from cteCallServLine1Count
),
CteServiceLineOneFilter
as
(
Select * ,ROW_NUMBER() over (partition by Serviceline1 order by Callcount desc) as Rown
from cteCallRanking
where Ranking<=6 /* This is your 'top 6 Service Lines' filter*/
)
Select *
from CteServiceLineOneFilter
where Rown <= 3 /* This is your 'top 3 Service Line 2' filter*/
December 23, 2010 at 7:20 pm
That's it. Exactly.
Thank-you so much. Only modifications I made were to add in some filters that are needed on our prod system and the data and results based on last months calls came out perfectly. Have spent the morning deconstructing the query so I understand it; I never could have written this at this stage. The cte statement seems an especially strong tool - spent an hour reading up on it and are going away to play more with it more.
Have developed a rough lay-out for the free-form fishbone diagram in SSRS and the field names, numbers and percentage calc's (Service Line 2 totals as a percent of their parent Service Line 1 - this is being calc.'d by SSRS) are slotting in beautifully. Are going to have to figure out how to add another cte statement to the query to get a grand total count so I can calculate percentages for the Service Line 1's to the total number of calls.
You've made my Christmas. Have a cool holiday.
January 16, 2011 at 3:36 pm
Hi guys
Just thought you would like to see the finished fishbone respresentation of the data your SQL cte query returned.
The graphic was created using MS Publisher for the roundy bits and SSRS for everything else. To produce the fishbone now from start to finish takes 10 seconds - a huge time improvement - and i've adapted the query so we can see all sorts of data/filters in a fishbone view.
By the way, the graphic looks way sharper printed directly from SSRS or as a tif file but pdf was the only choice i had to upload it to here.
Thank-you again hugely for your help.
Paul
January 17, 2011 at 1:36 am
Cool, thanks for the feedback.
Always nice to know that the problem got resolved.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply