On 2 Nov, 00:03, goo...@voyagercomponents.com wrote:
> Or, at least it seems complex to me.
> I need help on my "Dead Stock" report. It seemed pretty simple, but
> I'm discovering it is MUCH more complex than I had anticipated.
> I'm not quite sure which info you would and wouldn't want, so I'm just
> going to lay it ALL out.
>
> We need to list all Stocklines which are older than 01/01/2004, and
> under Part Numbers which have neither been Quoted nor Sold after
> 01/01/2000.
>
> This involves these Tables.Fields:
>
> Part.PartId
> Part.Number
>
> Stockline.PartId
> Stockline.StockId
> Stockline.DateEntered
> Stockline.Location
> Stockline.Quantity
> Stockline.Manufacturer
> Stockline.DateCode
> Stockline.ConsignmentCode
>
> Quote.QuoteId
> Quote.Date
>
> QuoteItem.QuoteId
> QuoteItem.PartId
>
> SalesOrder.SalesOrderId
> SalesOrder.Date
>
> SOItem.SalesOrderId
> SOItem.PartId
>
> This is what I have so far:
>
> SELECT Stockline.PartId, Stockline.StockId, Stockline.PartId,
> Stockline.DateEntered, Stockline.Location, Stockline.Quantity,
> Stockline.Manufacturer, Stockline.DateCode, Stockline.ConsignmentCode
> FROM Stockline
> LEFT JOIN SOItem
> ON Stockline.PartId=SOItem.PartID
> LEFT JOIN QuoteItem
> ON Stockline.PartId=QuoteItem.PartID
> WHERE SOItem.PartID is NULL
> AND QuoteItem.PartID is NULL
> AND Stockline.DateEntered<2004-01-01
>
> This completes nicley, without any errors, but I can't figure out how
> to finish it off.
> I need to include the Part.Number which coincides to each
> Stockline.PartId, and limit it to items where the SalesOrder.Date and
> Quote.Date are either <2000-01-01 or NULL
>
> PLEASE HELP.
> -Mo
>
> Additionally, I want to make sure that the LEFT JOIN's are achieving
> thier intended purposes.
> Would the second LEFT JOIN join to the results of the first JEFT JOIN,
> or the Stockline table?

What would be really helpful is some phpMyAdmin exports of the table
schemas and a few items of data in the associated INSERT statement
accompanied by the expected results from that data.