If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Alternative to a giant case statement?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
Question Alternative to a giant case statement?

I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!

For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:

Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
Code:
SELECT Cast(revenue/5 AS INT), Count(*)
   FROM doohickey
   GROUP BY Cast(revenue/5)
   ORDER BY Cast(revenue/5)
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
Quote:
Originally Posted by Pat Phelan View Post
Code:
SELECT Cast(revenue/5 AS INT), Count(*)
   FROM doohickey
   GROUP BY Cast(revenue/5)
   ORDER BY Cast(revenue/5)
-PatP
So Cast(revenue/5 AS INT) would be the first bucket (0 to 4.99)?

This works, but then i still have to go in and type all the labels for each of the buckets. Also, they want the $5 increments only up to $400 orders, then from they want different increments after that. Hence the reason i am trying to get a WHILE loop to work so I can have it stop at the right numbers...
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
This is what I have created, and it works, except each SELECT statement is evaluated and returned before the next one. I need them all UNION'd some how though.

Code:
    DECLARE @counter INT
    SET @counter = 5

    DECLARE @lower money
    SET @lower = 0.00

    DECLARE @upper money
    SET @upper = 4.99
    print @upper
    
    WHILE @counter <405
    BEGIN
      SELECT count(*) as Orders, cast(@lower as varchar) + ' - ' + cast(@upper as varchar) as Bucket
      FROM AnalystTempDB.dbo.orders1
      WHERE demand between @lower and @upper
      SET @counter = @counter+5
      SET @lower = @lower+5
      SET @upper = @upper+5
    END
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
ah, i got it! i added an INSERT statement into the loop so it inserts each result into another table.
Reply With Quote
  #6 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
Try this on for size:
Code:
CREATE TABLE #foo (
   lowerlimit   MONEY
,  upperlimit   MONEY
,  bucketsize   MONEY
   );

INSERT INTO #foo (lowerlimit, upperlimit, bucketsize)
   VALUES (    0,    10,    1)
,          (  10,   100,   10)
,          ( 100,  1000,  100)
,          (1000, 10000, 1000);

CREATE TABLE #bar (
   amount       MONEY
   );

WITH cte (n)
AS (SELECT 0 AS n UNION SELECT 1
   UNION SELECT 2 UNION SELECT 3
   UNION SELECT 4 UNION SELECT 5
   UNION SELECT 6 UNION SELECT 7
   UNION SELECT 8 UNION SELECT 9)
   INSERT INTO #bar (amount)
   SELECT 1000 * thousands.n + 100 * hundreds.n + 10 * tens.n + dollars.n + 0.1 * dimes.n + 0.01 * pennies.n
      FROM       cte AS pennies
	  CROSS JOIN cte AS dimes
	  CROSS JOIN cte AS dollars
	  CROSS JOIN cte AS tens
	  CROSS JOIN cte AS hundreds
	  CROSS JOIN cte AS thousands
--    ORDER BY 1

-- SELECT amount FROM #bar WHERE amount < 6 ORDER BY amount
SELECT * FROM #foo

SELECT Count(*) AS itemcount
,  r.lowerlimit + r.bucketsize * Round((a.amount - r.lowerlimit) / r.bucketsize, 0, 1) AS range_begin
,  r.lowerlimit + r.bucketsize * Round((a.amount - r.lowerlimit) / r.bucketsize, 0, 1) + r.bucketsize - 0.01 AS range_end
   FROM #foo AS r
   JOIN #bar AS a
      ON (r.lowerlimit <= a.amount
	  AND a.amount     <  r.upperlimit)
   GROUP BY
   r.lowerlimit + r.bucketsize * Round((a.amount - r.lowerlimit) / r.bucketsize, 0, 1) + r.bucketsize - 0.01
,  r.lowerlimit + r.bucketsize * Round((a.amount - r.lowerlimit) / r.bucketsize, 0, 1)
   ORDER BY 2, 3
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 300
I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!

For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:

Code:
CASE
WHEN SUM(order_revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN SUM(order_revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN SUM(order_revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?

Why do you have a SUM() and ask for a count? Where is the rest of the DDL? So, hre is a guess:

CREATE TABLE Buckets
(bucket_name VARCHAR (25) NOT NULL PRIMARY KEY,
low_revenue DECIMAL(10,3) NOT NULL,
high_revenue DECIMAL(10,3) NOT NULL,
CHECK (low_revenue < high_revenue));

INSERT INTO Buckets
('Under $5.00', 0.000, 4.999),
('$5.00 up to $10.00', 5.000, 9.999),
('$10.00 up to $20.00', 10.000, 19.999),
etc.;

Notice the extra decimal place trick and how I define a bucket boundary.

WITH Bucketed_Revenue
AS
(SELECT O.order_nbr, B.bucket_name
FROM Buckets AS B, Orders AS O
WHERE O.order_revenue
BETWEEN B.low_revenue AND B.high_revenue)

SELECT bucket_name, COUNT(order_nbr)
FROM Bucketed_Revenue
GROUP BY bucket_name;
Reply With Quote
  #8 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
How do you handle the different bucket sizes within the ranges? There are $5 buckets between 0-400, but another size after that. My proposed solution allows easy ways to exclude ranges and to have buckets of arbitrary size within a given range.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 300
Not a problem.

Quote:
How do you handle the different bucket sizes within the ranges? There are $5 buckets between 0-400, but another size after that.
Not a problem. The buckets can be any size, including irregular steps. The only thing I have to watch is that there are no gaps between the buckets. That first range is only 80 rows. The rows are small and not updated often, so the fill factor loads a lot per data page. Cluster an index on the range starting value and performance should be good. A table with, say, 10K rows should fit into main storage if you really need it.

This kind of reporting tends to use irregular steps as the values increase -- log scales usually. The finally step will have an insanely large ending value, so the whole set can map into buckets.
Reply With Quote
  #10 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
Try out my solution... The cte is a workaround for a numbers table, and the final SELECT is the real workhorse in my proposed solution.

For this particular problem, I think it may be the optimal solution at least in terms of geek time needed for any given bucket breakdown.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 300
I like the CTE for the steps; but I want to put it in a base table with indexing and clustering. Easy enough. Then you can insert any special rows.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 154
thanks Pat, Celko. you have been a huge help.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On