Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    179

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    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.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    179
    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...

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    179
    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

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    179
    ah, i got it! i added an INSERT statement into the loop so it inserts each result into another table.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    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.

  7. #7
    Join Date
    Jan 2013
    Posts
    307
    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;

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    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.

  9. #9
    Join Date
    Jan 2013
    Posts
    307

    Not a problem.

    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.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    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.

  11. #11
    Join Date
    Jan 2013
    Posts
    307
    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.

  12. #12
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    179
    thanks Pat, Celko. you have been a huge help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •