January 13, 2009 at 3:50 am
I have a table that I need to divide up into groups so that I can compute the set:run ratios by group of operations rather than totalling for an entire order, the required output needs to look like:
Order OpNo Operation GroupID
123456 10 SETG 1
123456 20 GEXL 1
123456 30 GEXL 1
123456 40 SETG 2
123456 50 GGBL 2
987654 10 SETG 3
987654 20 GEXL 3
The bold field is the one I need to auto-generate.
Essentially we need one ID for each SETG op plus the ops following it in that order until either the order number changes or another SETG appears against the original order number. That way I can sum booked times grouped by order numbers, operations and GroupIDs.
I'm sure I've seen something like this done, but I can't remember how, any advice would be most welcome.
January 13, 2009 at 5:04 am
Try something like:
SELECT T1.[Order], T1.OpNo, T1.Operation
    ,DENSE_RANK() OVER (ORDER BY T1.[Order], MAX(T2.OpNo)) AS GroupID
FROM YourTable T1
    JOIN YourTable T2
        ON T2.Operation = 'SETG'
            AND T1.[Order] = T2.[Order]
            AND T1.OpNo >= T2.OpNo
GROUP BY T1.[Order], T1.OpNo, T1.Operation
January 13, 2009 at 5:17 am
Ken McKelvey (1/13/2009)
Try something like:
SELECT T1.[Order], T1.OpNo, T1.Operation    ,DENSE_RANK() OVER (ORDER BY T1.[Order], MAX(T2.OpNo)) AS GroupID
FROM YourTable T1
    JOIN YourTable T2
        ON T2.Operation = 'SETG'
            AND T1.[Order] = T2.[Order]
            AND T1.OpNo >= T2.OpNo
GROUP BY T1.[Order], T1.OpNo, T1.Operation
Can Jeff Moden please answer this with 'Triangular join!' and show how slow this can be when used on some large tables? In any join, only = should be used. heavily slows down query execution.
This is the article that should set you on track: http://www.sqlservercentral.com/articles/T-SQL/61539/
Don't have the time right now to do it myself...
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 13, 2009 at 7:33 am
Many thanks, that looks very promising. I've just suffered a severe bout of Powerpoint poisoning so I'll take a look at it properly tomorrow 🙂
January 13, 2009 at 8:44 am
After a thick slice of quality coffee the PP symptoms abated enough to try the method suggested.
For our table sizes it works very well and quickly, many thanks for the assistance.
January 13, 2009 at 9:16 am
r.hensbergen (1/13/2009)
In any join, only = should be used.
This is not what Jeff's article says.
In this case the main join criterion is [Order]. In the triangular part of the join there are unlikely to be too many OpNo's with SETG per order so non-relational fiddles, like using a temp table with a clustered index, are unlikely to be worth the effort.
I dislike the concept of 'Best Practice' as it is often not fully understood and mindlessly applied.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply