Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Sorting a Schedule

    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?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    AHA! I was severely overcomplicating things. That gets me going in the right direction.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •