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 Category Source UserID EnteredUserID Order #
Joe Smith MED W jsmith bob 2012-01
Joe Smith MED W jsmith jsmith 2012-02
Joe Smith LAB V jsmith jsmith 2012-03
Bob White MED PROTOCOL bob bob 2012-04
Bob White RAD W bob jsmith 2012-05
Bob White CT V bob katie 2012-06
Bob White MED MD ORDER bob jsmith 2012-07

I want to get a total count of orders by Name then by Category like below.
Name Category Total Orders % Phys Entered %Other Entered Protocol %
Joe Smith MED 2 50% 50% 0%
Joe Smith LAB 1 100% 0% 0%
Bob White MED 2 0% 50% 50%
Bob White RAD 1 0% 100% 0%
Bob White CT 1 0% 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