Dear Samantha:
The assumption that "First" means oldest, or anything else in particular, is
a constant source of misunderstanding and error.
"First" means the first thing it finds. After some deletions in a table and
some new rows added, what is First may change. It doesn't mean "earliest"
like you may have expected. It means something perhaps a bit close to
"random".
If by this you hope to be calculating the QuantityRequired -
QuantityCompleted from those columns in the oldest record, specifically the
one with the minimum value of RequiredDate, then filtering on the
RequiredDate would be the thing to do.
I'll try to demonstrate this in a query:
SELECT PartNumber, RequiredDate,
QuantityRequired - QuantityCompleted AS QtyNet
FROM dbo_WOHeader T
WHERE RequiredDate =
(SELECT MIN(T1.RequiredDate)
FROM dbo_WOHeader T1
WHERE T1.PartNumber = T.PartNumber
AND T1.QuantityRequired - T1.QuantityCompleted > 0
AND ClosedFlag = 0
AND ReleasedDate IS NOT NULL)
WHERE T1.QuantityRequired - T1.QuantityCompleted > 0
AND ClosedFlag = 0
AND ReleasedDate IS NOT NULL
ORDER BY PartNumber
This is moderately complex and, as it is based on my best guess of what you
want, you should test it pretty carefully before relying on it.
Notice that the filters for ClosedFlag and ReleasedDate are repeated. If
there are multiple rows of data for the same PartNumber, and for the same
earliest RequiredDate, then cutting them down (hopefully to just 1) would be
a good thing. This is essential when finding the earliest RequiredDate
(that's what the "subquery" inside the parens from lines 5 - 10 does) while
ignoring any rows not meeting those criteria.
On the chance that the PartNumber / RequiredDate might not be unique when
taken together, especially with respect to that earliest date, filtering the
outer query the same way cuts down on the probability of multiple rows. It
is known from the subquery that there will be one. If there are more,
perhaps you want to ignore those without ClosedFlag = 0 AND ReleasedDate NOT
NULL. This does eliminate those, but could be a possibility of multiple
rows for that Part and Date that meet the other criteria. The query will
then return all such rows. Uniqueness of PartNumber and RequiredDate is not
guaranteed by this query, although it could be guaranteed by a unique index
if that's desirable.
I'm saying this: "watch out. a part could show up twice." Were you
expecting that possibility, or have you prevented it with a unique index?
Or could it be a surprise. Perhaps not the kind you would like.
Indeed, as I cannot test it against your database, I can't even tell if it
will work. But, I like the challenge of writing other people's queries all
day long without really knowing what I'm doing. It's fun! Was it
profitable for you? I know I didn't make a dime. : )
Tom Ellison
Samantha said:
I need to create a query from a WorkOrderHeader table to show the first
order
date and its quantity.
What I have so far:
SELECT dbo_WOHeader.PartNumber, Min(dbo_WOHeader.RequiredDate) AS
MinOfRequiredDate, First([QuantityRequired]-[QuantityCompleted]) AS QtyNet
FROM dbo_WOHeader
WHERE ((([QuantityRequired]-[QuantityCompleted])>0) AND
((dbo_WOHeader.ClosedFlag)=0) AND (Not (dbo_WOHeader.ReleasedDate) Is
Null))
GROUP BY dbo_WOHeader.PartNumber
ORDER BY dbo_WOHeader.PartNumber;
MinofRequiredDate --> what I need to be the first order date
QtyNet --> Order quantity of first order date
The problem is with the QtyNet (the first order quantity). The query does
not always display the first order quantity. Is there a better way to do
this? Any advise is very much appreciated.
