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

Help with Invoice design anybody!

Hi,
This issue is driving me nuts (well.. more nuts).
Our invoices have lines which run from the top of the page to the bottom.
How would we achieve this in reporting services assuming we are using a
table to show the items on the invoice?
thanks
MattYou can apply Border to the the text boxs in the table...for line running
from top to bottom..apply solid border on left and rigth of the text box.
Also u can use expressions to manage when u want the borders to be visible ...
I hope thats what u r looking for ...
"Matt" wrote:
> Hi,
> This issue is driving me nuts (well.. more nuts).
> Our invoices have lines which run from the top of the page to the bottom.
> How would we achieve this in reporting services assuming we are using a
> table to show the items on the invoice?
> thanks
> Matt
>
>|||Hi Sunny,
thanks for the reply, actually I need the lines to run from the top of the
page to the bottom of the page - unfortunately, they stop when the rows in
the table stop.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:C618BC15-C56A-47E6-82DE-6A5AB27443B1@.microsoft.com...
> You can apply Border to the the text boxs in the table...for line running
> from top to bottom..apply solid border on left and rigth of the text box.
> Also u can use expressions to manage when u want the borders to be visible
...
> I hope thats what u r looking for ...
>
> "Matt" wrote:
> > Hi,
> >
> > This issue is driving me nuts (well.. more nuts).
> >
> > Our invoices have lines which run from the top of the page to the
bottom.
> > How would we achieve this in reporting services assuming we are using a
> > table to show the items on the invoice?
> >
> > thanks
> >
> > Matt
> >
> >
> >|||Greetings,
Matt wrote:
> Hi,
> This issue is driving me nuts (well.. more nuts).
> Our invoices have lines which run from the top of the page to the bottom.
> How would we achieve this in reporting services assuming we are using a
> table to show the items on the invoice?
> thanks
> Matt
I would suggest you add the line(s) as a background image to the report.
--
Regards,
Neale NOON|||I had to do this very thing for both quotes and invoices; although it's been
a while, I seem to remember I placed rectangle and vertical line elements
down the length of the page, carefully ensuring that the vertical lines
lined up with the columns of the table. For a reason I forget, I also had
to send the table to the "back" of the page, behind the rectangle and line
elements.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:eKU060TpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Hi Sunny,
> thanks for the reply, actually I need the lines to run from the top of the
> page to the bottom of the page - unfortunately, they stop when the rows in
> the table stop.
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:C618BC15-C56A-47E6-82DE-6A5AB27443B1@.microsoft.com...
> > You can apply Border to the the text boxs in the table...for line
running
> > from top to bottom..apply solid border on left and rigth of the text
box.
> > Also u can use expressions to manage when u want the borders to be
visible
> ...
> > I hope thats what u r looking for ...
> >
> >
> > "Matt" wrote:
> >
> > > Hi,
> > >
> > > This issue is driving me nuts (well.. more nuts).
> > >
> > > Our invoices have lines which run from the top of the page to the
> bottom.
> > > How would we achieve this in reporting services assuming we are using
a
> > > table to show the items on the invoice?
> > >
> > > thanks
> > >
> > > Matt
> > >
> > >
> > >
>|||Thanks Greg,
was the Table contained within the rectangle element?
"Greg Clark" <gclarkmail@.yahoo.com> wrote in message
news:O1WTFCVpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I had to do this very thing for both quotes and invoices; although it's
been
> a while, I seem to remember I placed rectangle and vertical line elements
> down the length of the page, carefully ensuring that the vertical lines
> lined up with the columns of the table. For a reason I forget, I also had
> to send the table to the "back" of the page, behind the rectangle and line
> elements.
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:eKU060TpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > Hi Sunny,
> >
> > thanks for the reply, actually I need the lines to run from the top of
the
> > page to the bottom of the page - unfortunately, they stop when the rows
in
> > the table stop.
> >
> > "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> > news:C618BC15-C56A-47E6-82DE-6A5AB27443B1@.microsoft.com...
> > > You can apply Border to the the text boxs in the table...for line
> running
> > > from top to bottom..apply solid border on left and rigth of the text
> box.
> > > Also u can use expressions to manage when u want the borders to be
> visible
> > ...
> > > I hope thats what u r looking for ...
> > >
> > >
> > > "Matt" wrote:
> > >
> > > > Hi,
> > > >
> > > > This issue is driving me nuts (well.. more nuts).
> > > >
> > > > Our invoices have lines which run from the top of the page to the
> > bottom.
> > > > How would we achieve this in reporting services assuming we are
using
> a
> > > > table to show the items on the invoice?
> > > >
> > > > thanks
> > > >
> > > > Matt
> > > >
> > > >
> > > >
> >
> >
>|||Thanks for the suggestion. This might work if each page was identical.
Unfortunately, the first page has additional detail including address
information etc.
Further, I guess if you needed to resise/add/remove a column you would have
to re-create the image.
"noonie" <noonie1155@.hotmail.com> wrote in message
news:OqA2JEUpEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> Matt wrote:
> > Hi,
> >
> > This issue is driving me nuts (well.. more nuts).
> >
> > Our invoices have lines which run from the top of the page to the
bottom.
> > How would we achieve this in reporting services assuming we are using a
> > table to show the items on the invoice?
> >
> > thanks
> >
> > Matt
> I would suggest you add the line(s) as a background image to the report.
> --
> Regards,
> Neale NOON