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.

 > Alternative to a giant case statement?

 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
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?
 Pat Phelan 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.
 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
Quote:
 Originally Posted by Pat Phelan 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...
 clawlan 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
 clawlan 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.
 Pat Phelan 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.
 Celko 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;
 Pat Phelan 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.
 Celko 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.
 Pat Phelan 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.
 Celko 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.