November 11, 2010 at 10:55 am
I have the following (quite simplified) tables @BATCHES and @TRANSFERS. @BATCHES records the batch number and creation datetime of a batch, and the @TRANSFERS table records movement of batches through a facility. Once a batch is created, any movement of that batch within the facility generates a new batch number (in the @BATCHES table) and that movement is captured in the @TRANSFERS table.
DECLARE @BATCHES TABLE (
BATCH_NUM int NOT NULL,
BATCH_TIME datetime NOT NULL)
INSERT INTO @BATCHES (BATCH_NUM, BATCH_TIME)
SELECT 1, '20101108 08:37:00.000' UNION ALL
SELECT 2, '20101108 09:26:12.000' UNION ALL
SELECT 3, '20101108 12:35:00.000' UNION ALL
SELECT 4, '20101108 13:55:00.000' UNION ALL
SELECT 5, '20101108 14:03:00.000' UNION ALL
SELECT 6, '20101108 15:20:35.000' UNION ALL
SELECT 7, '20101109 02:15:28.000' UNION ALL
SELECT 8, '20101109 03:09:53.000' UNION ALL
SELECT 9, '20101109 06:25:30.000' UNION ALL
SELECT 10,'20101110 03:25:05.000' UNION ALL
SELECT 11,'20101110 05:14:22.000' UNION ALL
SELECT 12,'20101110 13:18:42.000' UNION ALL
SELECT 13,'20101110 14:22:57.000' ;
SELECT * FROM @BATCHES
DECLARE @TRANSFERS TABLE(
TRANSFER_NUM int IDENTITY(0,1) NOT NULL,
FROM_BATCH int NOT NULL,
TRANSFER_TIME datetime NOT NULL,
TO_BATCH int NOT NULL)
INSERT INTO @TRANSFERS(FROM_BATCH, TRANSFER_TIME, TO_BATCH)
SELECT 1, '20101108 10:15:00.000', 2 UNION ALL
SELECT 2, '20101108 11:15:00.000', 3 UNION ALL
SELECT 4, '20101109 14:22:00.000', 7 UNION ALL
SELECT 5, '20101110 03:27:15.000', 10
SELECT * FROM @TRANSFERS
Note that Batch 1 was transferred to Batch 2, then later Batch 2 was transferred to Batch 3. Also, Batch 4 was transferred to Batch 7. There are also records in the batches table that have not yet been transferred to another batch (Batch 6 for example).
I need a stored procedure that returns all batch numbers involved in transfers from an originating batch number (passed in as a parameter). For example, given the data above and passing in batch 1 as the parameter, the proc should return a recordset like so:
3
2
1
If I provided 4 as the parameter, the proc should return
7
4
The batch numbers are returned in descending XFER_TIME order. If no record for a batch exists in the @TRANSFERS table the proc should return the batch number of interest, or null if no record is located in the @BATCHES table.
I’ve tried a couple of approaches using cte and cursor, but am roadblocking on how to assess the contents of two columns with each iteration.
Find Batch1 in the FROM_BATCH.
If found, get the TO_BATCH value.
Now search the FROM_BATCH column for the previous TO_BATCH value, recording relevant Batch numbers in a temp table or whatever - repeat until nothing more is found.
Return all the batch nums found in descending XFER_TIME order.
Any clues, hints, tips, or solutions are most welcome.
Thanks!
Steve
November 11, 2010 at 11:01 am
Check out the recursive CTE Microsoft has in MSDN. It's for a hierarchy crawl, which is what this is (think of the second batch ID as a child ID). If you just plug "recursive CTE T-SQL" into Bing/Google, it'll find it for you.
Then, in the CTE, just use a join of these two tables instead of a single table with ID and parent ID. It just adds a join to the recursive CTE.
That'll get you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2010 at 11:27 am
I've looked at these examples but haven't figured out how to join the tables to meet my needs. I need to join @BATCHES to @TRANSFERS, but how? @BATCHES has the one BATCH_NUM column, while @TRANSFERS has both FROM_BATCH and TO_BATCH. Ever have one of those days when you really need to deliver but the intellect is lagging? This is me, today.
Thanks, though.
Steve
November 11, 2010 at 11:37 am
Something like this:
DECLARE @BATCHES TABLE (
BATCH_NUM int NOT NULL,
BATCH_TIME datetime NOT NULL)
INSERT INTO @BATCHES (BATCH_NUM, BATCH_TIME)
SELECT 1, '20101108 08:37:00.000' UNION ALL
SELECT 2, '20101108 09:26:12.000' UNION ALL
SELECT 3, '20101108 12:35:00.000' UNION ALL
SELECT 4, '20101108 13:55:00.000' UNION ALL
SELECT 5, '20101108 14:03:00.000' UNION ALL
SELECT 6, '20101108 15:20:35.000' UNION ALL
SELECT 7, '20101109 02:15:28.000' UNION ALL
SELECT 8, '20101109 03:09:53.000' UNION ALL
SELECT 9, '20101109 06:25:30.000' UNION ALL
SELECT 10,'20101110 03:25:05.000' UNION ALL
SELECT 11,'20101110 05:14:22.000' UNION ALL
SELECT 12,'20101110 13:18:42.000' UNION ALL
SELECT 13,'20101110 14:22:57.000' ;
--SELECT * FROM @BATCHES
DECLARE @TRANSFERS TABLE(
TRANSFER_NUM int IDENTITY(0,1) NOT NULL,
FROM_BATCH int NOT NULL,
TRANSFER_TIME datetime NOT NULL,
TO_BATCH int NOT NULL)
INSERT INTO @TRANSFERS(FROM_BATCH, TRANSFER_TIME, TO_BATCH)
SELECT 1, '20101108 10:15:00.000', 2 UNION ALL
SELECT 2, '20101108 11:15:00.000', 3 UNION ALL
SELECT 4, '20101109 14:22:00.000', 7 UNION ALL
SELECT 5, '20101110 03:27:15.000', 10
--SELECT * FROM @TRANSFERS
DECLARE @InputParam INT;
SELECT @InputParam = 4;
;WITH CTE AS
(SELECT Batch_Num, NULL AS Parent_Batch
FROM @BATCHES
WHERE BATCH_NUM = @InputParam
UNION ALL
SELECT To_Batch, From_Batch
FROM @TRANSFERS AS T
INNER JOIN CTE
ON T.From_Batch = CTE.Batch_Num)
SELECT Batch_Num
FROM CTE
ORDER BY Batch_Num DESC;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2010 at 11:45 am
INNER JOIN CTE
ON T.From_Batch = CTE.Batch_Num
Now it makes sense - your example cleared the mental debris. Many many thanks for taking the time to help.
Steve
November 11, 2010 at 12:07 pm
Thanks Gus, you just helped me understand a little bit more of it too. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 12:21 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply