On Nov 2, 7:37 am, Mo <goo...@voyagercomponents.com> wrote:
> On Nov 2, 2:00 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Forgive my nievity, but I never looked into common terminology.
> By 'table schemas', do you mean you want all the fields, and a few
> records for each table?
> And, please clarify what you are loking for in relation to the INSERT
> statement. I don't have one in my query, so I'm not sure what you
> need.
> -Mo- Hide quoted text -
>
> - Show quoted text -

Just taking a guess here.
Is this what you were looking for?
(I threw together this comma seperated example. If you need more, or
something else, please advise.)

Part
PartId, Number
0001, PN123
0002, PN456
0003, PN789
0004, PN555

Stockline
PartId, StockId, DateEntered, Location, Quantity, Manufacturer,
DateCode, ConsignmentCode
0001, 101, 2003-10-10, Loc01, 250, Brand1, 0206, Cons12
0001, 121, 2006-05-05, Loc20, 500, Brand6, 0115, Cons01
0002, 180, 2004-03-06, Loc15, 100, Brand2, 0404, Cons12
0003, 216, 2001-11-02, Loc02, 525, Brand1, 9902, Cons06
0004, 514, 2007-06-16, Loc01, 610, Brand8, 0650, Cons04
0004, 410, 2002-12-18, Loc20, 305, Brand8, 9612, Cons03

Quote
QuoteId, Date
400205, 1997-05-15
400470, 2005-08-19

QuoteItem
QuoteId, PartId
400205, 0001
400470, 0001
400470, 0003

SalesOrder
SalesOrderId, Date
100605, 1998-12-30
100850, 2002-07-15

SOItem
SalesOrderId, PartId
100605, 0001
100850, 0002
100850, 0003


Desired results from the qry:
Part.Number, Stockline.PartId, Stockline.StockId,
Stockline.DateEntered, Stockline.Location, Stockline.Quantity,
Stockline.Manufacturer, Stockline.DateCode, Stockline.ConsignmentCode
PN123, 0001, 101, 2003-10-10, Loc01, 250, Brand1, 0206, Cons12
PN555, 0004, 410, 2002-12-18, Loc20, 305, Brand8, 9612, Cons03

As you can see, PN123 was quoted and sold, but that was only prior to
01/01/2000, and PN555 was never quoted or sold.

NOTE: Ultimately, if we've had inactive stock for a long time, and
recieved additional stock recently, it'd be nice to have those on the
report also.
That would mean including StockId #'s 121 and 514 in the results also
(even though they are newer than 01/01/2204) because they are under an
inactive part# which also has old stock.
It would be nice, but not necessary.

Thanks.
-Mo