InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:58:24 GMT20http://www.sqlservercentral.com/Forums/Topic1357413-391-1.aspxHow many statuses do you have? If it's 10 or less, your performance may be increased if you switch that WHERE clause to say something like Status IN ('Active','Expired',...'n').Wed, 19 Sep 2012 04:41:41 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1357413-391-1.aspxI did end up figuring this out by the following:select b.Name, a.Category, COUNT(a.OrderNumber) as 'Total Orders', ((sum( CASE WHEN b.UserID = a.EnteredUserID and c.OrdSourceID <> 'PROTOCOL' THEN 1 ELSE 0 END) * 100 / nullif(COUNT(a.OrderNumber),0))) as 'Phys Entered %',((SUM( CASE WHEN b.UserID <> a.EnteredUserID and c.OrdSourceID <> 'PROTOCOL' THEN 1 ELSE 0 END) * 100 / nullif(COUNT(a.OrderNumber),0))) as 'Other Entered %',((SUM( CASE WHEN c.OrdSourceID = 'PROTOCOL' THEN 1 ELSE 0 END) * 100 / nullif(COUNT(a.OrderNumber),0))) as 'Revised Order %'from OeOrders aINNER JOIN OeOrders2 c ON a.OrderID = c.OrderID LEFT OUTER JOIN DMisUsers b ON a.ProviderID = b.ProviderIDwhere (a.OrderDateTime between '2012-01-01 00:00:00:000' AND '2012-09-09 23:59:59:999')and a.Status <> 'CANC'and b.Name is not nullgroup by b.Name, a.Categoryorder by b.Name, a.CategoryTue, 18 Sep 2012 12:28:29 GMTchristina.honnerthttp://www.sqlservercentral.com/Forums/Topic1357413-391-1.aspxJust a warning, Christina, if your data is set up the way I think it is, t.brown's solution will not immediately give you the answer you're looking for. You will definitely have to go tweaking in order to find the best way to do this.However, if you're able to give us table DDL and an insert statement with sample data, and let us know what code you've tried to make this work, we'll help you tweak until you get it right.Tue, 11 Sep 2012 08:16:58 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1357413-391-1.aspxI think you're going to need COUNTs and GROUP BYSELECT Name, Category, COUNT(Category) AS CountCatFROM ..yourTable..GROUP BY Name, Categorythen add your percentages in by getting the MAX as a subquery (I'm ready to be corrected here)SELECT Name, Category, ( 100 * COUNT(Category) / (SELECT COUNT(*) FROM ..yourTable.. ) ) AS PCNTFROM ..yourTable..GROUP BY Name, CategoryHopefully this will give you the framework of an approach to this problem.Tue, 11 Sep 2012 08:01:09 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1357413-391-1.aspx[quote][b]christina.honnert (9/11/2012)[/b][hr]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-01Joe Smith MED W jsmith jsmith 2012-02Joe Smith LAB V jsmith jsmith 2012-03Bob White MED PROTOCOL bob bob 2012-04Bob White RAD W bob jsmith 2012-05Bob White CT V bob katie 2012-06Bob 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 & CategoryProtocol % = # of orders where Source = ‘PROTOCOL’ / Total Orders[/quote]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.Tue, 11 Sep 2012 07:52:07 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1357413-391-1.aspxI’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-01Joe Smith MED W jsmith jsmith 2012-02Joe Smith LAB V jsmith jsmith 2012-03Bob White MED PROTOCOL bob bob 2012-04Bob White RAD W bob jsmith 2012-05Bob White CT V bob katie 2012-06Bob 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 & CategoryProtocol % = # of orders where Source = ‘PROTOCOL’ / Total OrdersTue, 11 Sep 2012 07:45:48 GMTchristina.honnert