Post reply

Date Sequence

  • January 10, 2006 at 2:31 pm

    #91169

    I have a table of orders like so

    Order.Machine

    Order.OrderNum

    Order.ProductionDate

    I would like a view that has the following fields

    Order.Machine

    Order.OrderNum

    Order.PreviousOrderNum

    PreviousOrderNum is the OrderNum that was Produced on the same machine before the current order based on ProductionDate

    This seems simple but I am having a mental block today

    Thanks

  • January 10, 2006 at 2:50 pm

    #613940

    I think this is what your looking for.

    SELECT

        o1.Machine,

        o1.OrderNum,

        (   SELECT TOP 1

                o2.OrderNum

            FROM

                [Order] o2

            WHERE

                O2.Machine = o1.Machine AND o2.ProductionDate < o1.ProductionDate

            ORDER BY

                o2.ProductionDate DESC

        ) AS 'PreviousOrderNum'

    FROM

        [Order] o1

    Mike

  • January 11, 2006 at 6:39 am

    #614039

    Thanks Mike

    That is what I was looking for.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply