CODEWITHSUNDEEP
sql-serverloops
user1955215
user1955215

Reputation: 743

Looping through another table and using values as variables for SELECT query

Sample data from the Delays Table is shown below:

+----------+-------------+
| Customer | DaysDelayed |
+----------+-------------+
|   698125 |          13 |
|   698126 |          31 |
|   698127 |           2 |
|   698128 |          11 |
|   698129 |           5 |
|   698130 |           4 |
|   698131 |          42 |
|   698132 |          29 |
|   698133 |          29 |
+----------+-------------+

The output desired is:

+------------+-------+
|   Range    | Count |
+------------+-------+
| 0-7 days   |     3 |
| 8-20 days  |     2 |
| 21-30 days |     2 |
| 31-45 days |     2 |
+------------+-------+

For getting the output I have written 4 queries: 

SELECT '0-7 days', Count(*) 
FROM Delays 
WHERE DaysDelayed between 0 AND 7

SELECT '8-20 days', Count(*) 
FROM Delays 
WHERE DaysDelayed between 8 AND 20

and so on (the values for the label and the WHERE condition changing in each query).

The day ranges change from time to time and also the number of ranges can increase/decrease.

From a maintainability perspective, I want to store the Range values in a table as follows:

+---------+------------+----------+----------+
| RangeID | RangeName  | LowerEnd | UpperEnd |
+---------+------------+----------+----------+
|       1 | 0-7 days   |        0 |        7 |
|       2 | 8-20 days  |        8 |       20 |
|       3 | 21-30 days |       21 |       30 |
|       4 | 31-45 days |       31 |       45 |
+---------+------------+----------+----------+

and loop through this table with one SELECT query (instead of 4 queries at present).

Please help.

Upvotes: 1

Views: 479

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166356

Have you tried something like

SELECT  RangeName,
        COUNT(d.Customer) Cnt
FROM    Ranges r LEFT JOIN
        Delays d    ON  d.DaysDelayed BETWEEN r.LowerEnd AND r.UpperEnd
GROUP BY RangeName

SQL Fiddle DEMO

Upvotes: 1

diegoperini
diegoperini

Reputation: 1806

Use UNION operator. It merges rows of any number of given tables

Here is the documentation: http://dev.mysql.com/doc/refman/5.0/en/union.html

Upvotes: 0

Related Questions