Sorting a Schedule

Mark_C · #1 (**permalink**) 10-17-11, 11:35

I have a schedule table with this structure:

Sched_Date (date)
Sched_Shift (char - A or B or C)
Machine (char)
Product

The sched date is just a date - their is no time element.

I need to be able to pull the next 3 scheduled shifts (A, B, C is the correct sort order) for each machine where the sched_date is greater than or equal to today, but if it's today, only shift C counts - A and B are already past.

Not all machines run every day. And even when they do run, they may not run all three shifts in a day.

So if I ran it today, I want to see something like

Machine Date Shift
A 10/17/2011 C
A 10/18/2011 A
A 10/18/2011 B
B 10/17/2011 C
B 10/18/2011 A
B 10/19/2011 A
C 10/18/2011 B
C 10/18/2011 C
C 10/19/2011 B

Any ideas on how to approach this?

stolze · #2 (**permalink**) 10-18-11, 13:48

Code:

SELECT ...
FROM ...
WHERE sched_date > CURRENT DATE OR
       ( sched_date = CURRENT DATE AND sched_shift = 'C' )
ORDER BY sched_date ASC, sched_shift DESC

What is missing in this is the mechanism to pick only the first 3 rows for each machine. Depending on your specific DBMS, this may look differently. In standard SQL, you could identify the first 3 using a couple joins and MAX() aggregation function. In DB2, you could do something like this:

Code:

SELECT *
FROM ( SELECT  ..., ROW_NUMBER OVER ( ORDER BY sched_date ASC, sched_shift DESC) AS rn
       FROM ...
       WHERE sched_date > CURRENT DATE OR
              ( sched_date = CURRENT DATE AND sched_shift = 'C' ) ) AS t
WHERE rn <= 3
ORDER BY sched_date ASC, sched_shift DESC

Mark_C · #3 (**permalink**) 10-18-11, 14:32

AHA! I was severely overcomplicating things. That gets me going in the right direction.

Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech