Help with Query please

I have a requirement involving writing a report that at first appeared basic, but is driving me crazy.

I have to deliver a financial report that contains 4 aggregate columns - 2 averages, and 2 SUMs.

I am running into issues with my query when I add a particular WHERE clause.

I need to filter the report based on one of two conditions. Either an item has been shipped, or an item has been marked "ready" to ship.

There's an audit table that I am using as part of my join, because it contains the dates these statuses were marked. I need these dates because the report has a column that will designate what day of the week the status was marked.

The "shipped" items show up correctly, but the "ready to ship" items ALWAYS end up including those records that are between ready to ship and ship.

So, for example's sake, there is a "prepared" state. This state is between "ready to ship" and "shipped". My query returns shipped also, and I dont want those records, just those where the latest status is "ready to ship". As soon as its flagged "prepared" it should no longer appear.

I've been using MAX on the ID of the states (theyre stored in a separate table) but this is not working.

I know this is a vague explanation, but I am hoping someone will offer some insight into the correct SQL needed to complete this query.

Here is what I have so far. This inner most query returns the IDs that I want, distinctly. So I'm comfortable with this.

SELECT 'ConfReceived' as 'Status', cdq.UniversalOrderID FROM CustomerDoorQuotes cdq
INNER JOIN CustomerInformation ci
ON ci.CustomerID = cdq.CustomerID
INNER JOIN PipelineAudit pa
ON pa.UniversalOrderID = cdq.UniversalOrderID
LEFT JOIN ProductionScheduleStatuses pss
ON cdq.UniversalOrderID = pss.UniversalOrderID
GROUP BY ALL cdq.UniversalOrderID
HAVING MAX(PipelineStatusID) = 3
UNION

SELECT 'Shipped' as 'Status', cdq.UniversalOrderID FROM CustomerDoorQuotes cdq
INNER JOIN CustomerInformation ci
ON ci.CustomerID = cdq.CustomerID
INNER JOIN PipelineAudit pa
ON pa.UniversalOrderID = cdq.UniversalOrderID
LEFT JOIN ProductionScheduleStatuses pss
ON cdq.UniversalOrderID = pss.UniversalOrderID
GROUP BY ALL cdq.UniversalOrderID
HAVING MAX(PipelineStatusID) = 6

Its when I wrap another query around it that I get too many records. PipelineAudit is the offending table for sure. This is the table that holds all the statuses, and when my outer query sees the in between status of "prepared" it includes it in the result set.

If you r using crystal report for reporting purpose, then try to do avg and sum in there it will ease you work