October 18, 2004 at 3:03 pm
I have data that contains a sequence. Such as:
| store_number | aisle | side | section | shelf | seq |
| 30175 | 11 | 2 | 4 | 6 | 3 |
| 30175 | 11 | 2 | 4 | 6 | 4 |
| 30175 | 11 | 2 | 4 | 6 | 5 |
| 30941 | 102 | 1 | 1 | 1 | 6 |
| 30941 | 102 | 1 | 1 | 1 | 7 |
| 31127 | 103 | 1 | 2 | 7 | 7 |
| 31127 | 103 | 1 | 2 | 7 | 8 |
| 31127 | 103 | 1 | 2 | 7 | 9 |
| 31127 | 103 | 1 | 2 | 7 | 12 |
I am trying to write code that will tell me when a store has Contiguous Sequences on the same shelf. The top 2 stores should be returned. Store number 31127 should not. Any help would be greatly appreciated. I assume there is some way to do this with math.
October 18, 2004 at 3:41 pm
Assuming that the seq will not be reused for a given shelf, I believe what you are after can be done with three subselects in the WHERE clause. It won't perform the greatest, but should function. Add a condition such that the MAX(seq) minus the MIN(seq) equals the COUNT(*).
SELECT * FROM MyTable A
WHERE ( SELECT MAX(seq) FROM MyTable B WHERE B.store_number = A.store_number AND B.aisle = A.aisle AND B.side = A.side AND B.section = A.section AND B.shelf = A.shelf)
- ( SELECT MIN(seq) FROM MyTable B WHERE B.store_number = A.store_number AND B.aisle = A.aisle AND B.side = A.side AND B.section = A.section AND B.shelf = A.shelf)
= ( SELECT COUNT(*) FROM MyTable B WHERE B.store_number = A.store_number AND B.aisle = A.aisle AND B.side = A.side AND B.section = A.section AND B.shelf = A.shelf)
October 19, 2004 at 6:47 am
Assuming that the entire shelf must be made up of consecutive seq numbers then the following query should give you what you want:
SELECT store_number, aisle, side, [section], shelf,
MIN(seq) AS 'MIN_seq', MAX(seq) AS 'MAX_seq', COUNT(DISTINCT seq) AS 'seq_COUNT'
FROM YOUR_TABLE_NAME
GROUP BY store_number, aisle, side, [section], shelf
HAVING (MAX(seq) - MIN(seq)) + 1 = COUNT(DISTINCT seq)
October 19, 2004 at 8:23 am
The Shelf does not always contain a pefect sequence (1-5). No shelf will have a duplicate sewquence. This gets me some information.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply