I have a feeling I am over complicating this. I have items being loaded on to a shelf. I know when that item wants to get loaded and to which shelf. In a separate table I can see which items were on which shelf between certain intervals so for e.g. TABLEA - WAIT TABLE
| ITEM | STATUS | SHELF | WAIT_DT |
|---|---|---|---|
| ITEMA | WAITING | A | 02/12/21 11:20 |
| ITEMB | WAITING | A | 02/12/21 11:30 |
| ITEMB | WAITING | B | 02/12/21 11:30 |
ITEMA going to SHELF A and ITEMB can go to either SHELF A/B. In a separate table I can see when items were loaded
TABLEB - SHELF TABLE
| ITEM | STATUS | SHELF | LOAD_DATE | UNLOAD_DATE |
|---|---|---|---|---|
| ITEMC | UNLOADED | A | 02/12/21 11:15 | 02/12/21 11:25 |
| ITEMD | UNLOADED | A | 02/12/21 11:15 | 02/12/21 11:28 |
| ITEME | UNLOADED | A | 02/12/21 11:15 | 02/12/21 11:19 |
| ITEMF | UNLOADED | A | 02/12/21 11:15 | 02/12/21 11:31 |
| ITEMG | UNLOADED | B | 02/12/21 11:15 | 02/12/21 11:25 |
So I want to look at TABLEA (WAIT Table), see that data but get a count of how many items are currently on the SHELF they are waiting for from TABLEB.
| ITEM | STATUS | SHELF | WAIT_DT | SHELF_COUNT |
|---|---|---|---|---|
| ITEMA | WAITING | A | 02/12/21 11:20 | 3 |
| ITEMB | WAITING | A | 02/12/21 11:30 | 1 |
| ITEMB | WAITING | B | 02/12/21 11:30 | 0 |
In a proc I could use a cursor..for loop and get data for each row but I am trying to do this in a query at the moment. My initial thinking, can I join to a minutely breakdown of each hour 11:15, 11:16, 11:17 etc take a count at each point and join these to the WAIT_DT. That would give me what I need but I'm hoping there is a much easier route I am unaware of.
Any help is appreciated - thanks.
CodePudding user response：
simple subquery might solve your problem.
SELECT a.*,
(SELECT COUNT(*)
FROM tableB b
WHERE a.shelf = b.shelf
AND a.WAIT_DT >= b.LOAD_DATe AND a.WAIT_DT < B.UNLOAD_DATE) SHELF_COUNT
FROM tableA a;