Post reply

Minimum of detail table

  • March 2, 2007 at 10:10 am

    #116004

    I am brain dead this morning and not able to focus (hey it's Friday). This may just be a simple query.

    Here is what I have - 2 tables Orders and OrderLineItems. For each row in Orders I need the earliest required date from the OrdersLineItem table. If there are 2 rows which have the same required date for an order, any row would suffice

    DECLARE @tblOrders TABLE (ID int, Customer varchar(50) )

    DECLARE @tblLineItems TABLE(OrderID int, LineNumber smallint, Product varchar(50), ReqdBy datetime, Qty int)

    INSERT INTO @tblOrders (ID, Customer) VALUES (1,'A-1 Customer')

    INSERT INTO @tblOrders (ID, Customer) VALUES (2,'A-2 Customer')

    INSERT INTO @tblLineItems(OrderID, LineNumber, Product, ReqdBy, Qty)

    SELECT 1, 1, 'Prod A', '2007-04-01', 10

    UNION

    SELECT 1, 2, 'Prod B', '2007-04-05', 20

    UNION

    SELECT 1, 3, 'Prod C', '2007-03-15', 30

    UNION

    SELECT 1, 4, 'Prod D', '2007-04-30', 40

    UNION

    SELECT 1, 5, 'Prod E', '2007-03-15', 50

    UNION

    SELECT 2, 1, 'Prod AA', '2007-03-01', 230

    UNION

    SELECT 2, 2, 'Prod BB', '2007-03-12', 340

    UNION

    SELECT 2, 3, 'Prod CC', '2007-02-27', 450

     

    SELECT * FROM @tblOrders

    SELECT * FROM @tblLineItems

    The output should be

    OrderID  LineNumber Product   ReqdBy     Qty

    1           3              Prod C   3/15/2007   30

    (OR)    

    1           5               Prod E   3/15/2007  50

    (AND)    

    2           3               Prod CC 2/27/2007  450

     

  • March 2, 2007 at 10:30 am

    #692893

    I would use 2 derived tables, 1st gets the earliest date per order, and 2nd finds the lowest line number to use as a tie-breaker if there are 2 or more lines with the same date:

      Select L2.*

      From @tblLineItems As L2

      Inner Join

      (

        Select L1.OrderID, Min(LineNumber) As LowestLineNumber

        From @tblLineItems As L1

        Inner Join (

          Select OrderID, Min(ReqdBy) As EarliestDate

          From @tblLineItems

          Group By OrderID

        ) dtEarliest

        On (L1.OrderID = dtEarliest.OrderID And

            L1.ReqdBy = dtEarliest.EarliestDate)

        Group By L1.OrderID

      ) dtTieBreaker

      On (L2.OrderID = dtTieBreaker.OrderID And

          L2.LineNumber = dtTieBreaker.LowestLineNumber)

  • March 2, 2007 at 10:42 am

    #692896

    select tblLineItems.orderid,tblLineItems.linenumber,tblLineItems.product,

    tblLineItems.reqdby,qty

    from (

     --for the minimum date, get the minimum linenumber

     --use that to join on to pull one row

     select LI.orderid,min(LI.linenumber) linenumber

     from (

      --Get the orderid for the minimum reqdby date

      select orderid,min(reqdby) reqdby from tblLineItems

      group by orderid

    &nbsp X

     inner join tblLineItems LI on X.orderid = LI.orderid

      and X.reqdby = LI.reqdby

     group by LI.orderid

    ) Y

    inner join tblLineItems on Y.orderid=tblLineItems.orderid

     and Y.linenumber = tblLineItems.linenumber

    group by tblLineItems.linenumber,tblLineItems.product,qty

    Hope this gets you to what you're looking for.

    Kim

  • March 2, 2007 at 10:43 am

    #692898

    Oops, I see PW beat me to it

  • March 2, 2007 at 2:10 pm

    #692935

    Thanks for query. It worked perfectly.

  • March 6, 2007 at 3:45 am

    #693268

    Hi ,

    I know U  r problem is know solved.. Take a look this one to solve ur problem...

    select * from  tblLineItems a

    where  reqdby in (select min(reqdby) from tblLineItems group by orderid)

    and orderid in(select orderid from tblLineItems group by orderid)

    Regards ,

    AMIT GUPTA...

     

     

  • March 6, 2007 at 8:56 am

    #693335

    The requirement is to return 1 record per orderid. What you've posted does not accomplish this and fails to meet the requirement.

Viewing 7 posts - 1 through 6 (of 6 total)

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