Help including Max Value for selected day with query 1

[delphi6BDE]

Hello,

Is there a way to change this query to also include the maximum ordered regardless of account for the selected date? I need to be able to reference the maximum value for the selected date in order to see if the work can be done for the day. For example, if a client ordered 10,000 pieces, and another ordered 50,000 pieces, but the work must be done together, the work cannot start if the second customer's product is not there. (hope that makes sense) I will be using the results in a delphi app I'm writing, and the max value ordered for the day will be used to populate a progress bar within a grid to show the user how much work can be performed before the missing product arrives (if not 100%)

My query right now:

SELECT
tblAccounts.strName,
tblSpecs.strPhrase,
tblSpecs.strCode,
tblSpecs.strVer,
Sum(tblHistory.lngQty) AS Ordered,
tblEntity.strEntity,
tblSpecs.dtmDist
FROM
tblAccounts
INNER JOIN
((tblSpecs
INNER JOIN tblEntity ON
tblSpecs.entityID = tblEntity.entityID)
INNER JOIN tblHistory ON
(tblSpecs.specsID = tblHistory.specsID) AND
(tblEntity.entityID = tblHistory.entityID)) ON
tblAccounts.strAccount = tblSpecs.strAccount
WHERE
(((tblSpecs.ysnCancelled)=0) AND
((tblSpecs.ysnReceived)=0))
GROUP BY
tblAccounts.strName,
tblSpecs.strPhrase,
tblSpecs.strCode,
tblSpecs.strVer,
tblEntity.strEntity,
tblSpecs.dtmDist
HAVING
(((tblEntity.strEntity)="entity") AND
((tblSpecs.dtmDist)=#12/28/2011#));

 

[BigRed1212]

Are you saying that in addition to
Sum(tblHistory.lngQty) AS sum_Ordered

you want
Max(tblHistory.lngQty) AS max_Ordered

?

 

[delphi6BDE]

Sort of... I want to show the maximum ordered for any given day regardless of which client ordered it.

Example
- Client A orders 50,000 pieces
- Client B orders 25,000 pieces
- Client C orders 30,000 pieces

If all these pieces are put together and distributed at the same time, there would be no way we could get any work done if client A hasn't given us his pieces to distribute

I want to see
strName | strPhrase | strCode | strVersion | Ordered | strEntity | dtmDist | MaxOrdered |
Client A | MyOrder | | | 50000 | entity | 12/28/2011 | 50000 |
Client B | OurOrder | | | 25000 | entity | 12/28/2011 | 50000 |
Client C | YourOrder | | | 30000 | entity | 12/28/2011 | 50000 |
etc...

This way I can divide each clients order into the maximum ordered for the day and populate my grid with the progress bar showing the 'severity' of the missing client. In the above example, nothing could be started because client A dropped the ball and didn't provide us with their product. However, if Client A did send his product, but we were waiting on Client B's product, we could still prepare 50% of the days work which would buy client B some time to get his ducks in a row and give us his product.

I'm able to do this by creating 2 separate queries, and then querying those two queries, but in Delphi I cannot reference this joined query leaving me with no choice but to try and get this done in a single query, even if this means a sub query to get the max ordered. But I just can't seem to get it right.

 

[delphi6BDE]

If it helps anybody to help me, this is the query I would pull to get the maximum booked for the day. Having specsID in the group by assures me I will only get 1 result.

SELECT TOP 1
Sum(tblHistory.lngQty) AS MaxOrdered,
tblSpecs.specsID,
tblSpecs.dtmDist
FROM
(tblAccounts
INNER JOIN (tblSpecs INNER JOIN tblEntity ON tblSpecs.entityID = tblEntity.entityID) ON
tblAccounts.strAccount = tblSpecs.strAccount)
INNER JOIN tblHistory ON
tblSpecs.specsID = tblHistory.specsID
GROUP BY
tblSpecs.specsID,
tblSpecs.dtmDist,
tblAccounts.strName,
tblSpecs.strVer
HAVING
(((tblSpecs.dtmDist)=#12/28/2011#))
ORDER BY
Sum(tblHistory.lngQty) DESC ,
tblSpecs.specsID DESC;


Is there any way to join the two queries in a single query that I can use through my ADO connection in Delphi? My program is almost done; this just gives me that extra little tool that the users would grow to love

 

[PHV]

Something like this ?

SELECT 
    tblAccounts.strName, 
    tblSpecs.strPhrase, 
    tblSpecs.strCode, 
    tblSpecs.strVer, 
    Sum(tblHistory.lngQty) AS Ordered, 
    tblEntity.strEntity, 
    tblSpecs.dtmDist,
    (SELECT TOP 1 Sum(tblHistory.lngQty)
       FROM (tblAccounts 
      INNER JOIN (tblSpecs INNER JOIN tblEntity ON tblSpecs.entityID = tblEntity.entityID)
         ON tblAccounts.strAccount = tblSpecs.strAccount) 
      INNER JOIN tblHistory ON tblSpecs.specsID = tblHistory.specsID
      WHERE tblSpecs.dtmDist=#2011-12-28#
      GROUP BY 
        tblSpecs.specsID, 
        tblAccounts.strName, 
        tblSpecs.strVer
      ORDER BY 1 DESC
    ) AS MaxOrdered
FROM 
    tblAccounts 
        INNER JOIN 
            ((tblSpecs 
                INNER JOIN tblEntity ON 
                    tblSpecs.entityID = tblEntity.entityID) 
                INNER JOIN tblHistory ON 
                    (tblSpecs.specsID = tblHistory.specsID) AND 
                    (tblEntity.entityID = tblHistory.entityID)) ON 
                    tblAccounts.strAccount = tblSpecs.strAccount
WHERE 
    tblSpecs.ysnCancelled=0 AND 
    tblSpecs.ysnReceived=0 AND
    tblEntity.strEntity="entity" AND 
    tblSpecs.dtmDist=#2011-12-28#
GROUP BY 
    tblAccounts.strName, 
    tblSpecs.strPhrase, 
    tblSpecs.strCode, 
    tblSpecs.strVer, 
    tblEntity.strEntity, 
    tblSpecs.dtmDist

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[delphi6BDE]

Thank you but unfortunately it does not. I get the error message 'At most one record can be returned by this subquery'

 

[delphi6BDE]

Forget it.. I got it. The subquery was returning more than one result and fixing your subquery to sort by specsID descending as well did the trick.

Thank you very much!! Development can now continue.