Set based answer
-
Hello,
I have something to accomplish on SQL Server 2000. Say i have the following table that traces how often something is transferred:
A | B
1 | 2
2 | 3
5 | 7
7 | 8
8 | 9
9 | 10
Basically, the item starting out labeled as 1 in column A has been transferred twice ( 1 -> 2, 2 -> 3), and the one labeled as 5 has been transferred four times ( 5 -> 7, 7 -> 8, 8 -> 9, 9 -> 10). Id like to be able to generate a report something like the following:
Item #times transferred
1 2
5 4
I'm not interested in the intermediary states ( where A is 7, 8, or 9), and there won't be any times where there is a record in column A without a corresponding record in column B.
Any ideas if/how this can be done in a set-based query instead of a cursor (or loop)?
TIA, Chris.
-
SELECT [A],
INTO [#Temp1]
FROM
(
SELECT 1 AS [A], 2 AS
UNION
SELECT 2 AS [A], 3 AS
UNION
SELECT 5 AS [A], 7 AS
UNION
SELECT 7 AS [A], 8 AS
UNION
SELECT 8 AS [A], 9 AS
UNION
SELECT 9 AS [A], 10 AS
) [Vals]
SELECT
[A],
,
1 AS [Transfer_Count]
INTO [#Temp2]
FROM[#Temp1] [Temp1_1]
WHERENOT EXISTS
(
SELECT *
FROM[#Temp1] [Temp1_2]
WHERE[Temp1_1].[A] = [Temp1_2].
)
WHILE @@ROWCOUNT > 0
BEGIN
UPDATE [#Temp2]
SET
= #Temp1.,
[Transfer_Count] = [Transfer_Count] + 1
FROM[#Temp2]
INNER JOIN[#Temp1]
ON[#Temp1].[A] = [#Temp2].
END
SELECT * FROM #Temp2
DROP TABLE #Temp1
DROP TABLE #Temp2
-
Again the premise is not to loop
the above is "looping"I don't think there is an efficient way of solving this problem SET-BASED superior to a cursor.
Cheers,
* Noel -
Loop != Cursor
If you wanted to do this in a single select query, you would have to anticipate the maximum "times transferred" and use that many left joins - 1, which isn't practical.
If you want to join to this result set, you may want to look at making a function that returns a table.
-
Kevin's solution works for me. I was stuck thinking about CUBE or ROLLUP or something else to avoid a cursor. Updates on a temp table didn't occur to me.
Thanks!
-
It was a fun one to bend my mind around to clear my head of my current problems. Take it easy.
-
ChrisMoix,
how do you know that item transferred from 7 to 8 is the same item transferred from 5 to 7?
How you gonna distinguish 2 deliveries from 7 to 8 happened in, say, 2 weeks one after another? Which one you're gonna count?
If you would have some ItemID in this table you'd avoid any kind of looping at all.
Just
SELECT COUNT(*), ItemID
...
GROUP BY ItemID
and that's it.
_____________
Code for TallyGenerator -
You just nailed it. It is a very poor design. But those are the requests we get most of the time.
* Noel -
This is of course assuming there are no possible cycles in the table:
ie the route was: 1 -> 2 -> 3 -> 2 -> 4
Otherwise you're going to get an infinite loop.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply