Or, at least it seems complex to me.
I need 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 .
-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?

On 2 Nov, 00:03, goo...@voyagercomponents.com wrote:
> Or, at least it seems complex to me.
> I need 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 .
> -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 ful 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.

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 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 .
> > -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 ful 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

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 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 .
> > > -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 ful 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

Mo 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 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 .
>>> -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 ful 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

If you g into phpMyAdmin, as I mentioned in my post, there is an Export tab.

That allows you to export the structure and data. The structure comes in the
form of CREATE TABLE statements and the data comes in the form of INSERT
statements. That allows me (or others) to quickly setup tables just like
your ones and have the sample data in there too.

On Nov 2, 12:59 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Mo 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 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 .
> >>> -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 ful 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
>
> If you g into phpMyAdmin, as I mentioned in my post, there is an Export tab.
>
> That allows you to export the structure and data. The structure comes in the
> form of CREATE TABLE statements and the data comes in the form of INSERT
> statements. That allows me (or others) to quickly setup tables just like
> your ones and have the sample data in there too.- Hide quoted text -
>
> - Show quoted text -

OK, I did that, but after more than 5,400 lines (which is only the
first of 65 tables), I got an error:
#1146 - Table 'voyager1_voyager.AGENTS' doesn't exist.

This is a table which we aren't really using (it was placed there for
our data-migration into MySQL), but I see it in our tables list.
Any suggestions?

Additionally, if we get the whole dump, it will have a bunch of info
in there which I'm sure our company wants to keep as proprietary.
How should we solve this?

Thanks for your and patience.

On Nov 2, 3:51 pm, Mo <goo...@voyagercomponents.com> wrote:
> On Nov 2, 12:59 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
>
>
> > Mo 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 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 .
> > >>> -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 ful 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
>
> > If you g into phpMyAdmin, as I mentioned in my post, there is an Export tab.
>
> > That allows you to export the structure and data. The structure comes in the
> > form of CREATE TABLE statements and the data comes in the form of INSERT
> > statements. That allows me (or others) to quickly setup tables just like
> > your ones and have the sample data in there too.- Hide quoted text -
>
> > - Show quoted text -
>
> OK, I did that, but after more than 5,400 lines (which is only the
> first of 65 tables), I got an error:
> #1146 - Table 'voyager1_voyager.AGENTS' doesn't exist.
>
> This is a table which we aren't really using (it was placed there for
> our data-migration into MySQL), but I see it in our tables list.
> Any suggestions?
>
> Additionally, if we get the whole dump, it will have a bunch of info
> in there which I'm sure our company wants to keep as proprietary.
> How should we solve this?
>
> Thanks for your and patience.- Hide quoted text -
>
> - Show quoted text -

I think it just clicked, in regards to your original instructions
regarding the INSERT statements.
Once we get the dump completed, I'll just weed out most of the INSERTs
to just keep some base info which applies.