christina.honnert (9/11/2012)
I’ve got the following data and I’m trying to get to the following results. I’m assuming I need to use a CASE statement, but was looking for some help and to find out the best way to optimize the query as well because I will be hitting hundreds of thousands of rows to get the summarized data.Name CategorySourceUserIDEnteredUserIDOrder #
Joe SmithMEDWjsmithbob 2012-01
Joe SmithMEDWjsmithjsmith 2012-02
Joe SmithLABVjsmithjsmith 2012-03
Bob WhiteMED PROTOCOL bobbob 2012-04
Bob WhiteRADWbobjsmith 2012-05
Bob WhiteCTVbobkatie 2012-06
Bob WhiteMED MD ORDER bobjsmith 2012-07
I want to get a total count of orders by Name then by Category like below.
NameCategoryTotal Orders% Phys Entered%Other Entered Protocol %
Joe SmithMED250%50%0%
Joe SmithLAB1100%0%0%
Bob WhiteMED20%50%50%
Bob WhiteRAD10%100%0%
Bob WhiteCT10%100%0%
Total Orders = # of orders by Name & Category
% Phys Entered = # of orders where user ID = EnteredUser ID AND Source <> ‘PROTOCOL’ / Total Orders by Name & Category
%Other Entered = # of orders where userID <> EnteredUserID AND Source <> ‘PROTOCOL’/ total Orders by Name & Category
Protocol % = # of orders where Source = ‘PROTOCOL’ / Total Orders
It is not too likely you are going to get much help with your query unless you post some more details. I see you are pretty new around here. You should take a look at the first link in my signature for best practices when posting questions.
I can't provide you much detail but I don't see how CASE is going to help in your situation. What you need to do is look at aggregate data. You will need to do some grouping and use some aggregate functions.
_______________________________________________________________
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/