Minimum of detail table
-
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
-
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)
-
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
 
Xinner 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
-
Oops, I see PW beat me to it
-
Thanks for query. It worked perfectly.
-
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...
-
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