|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need help with SQL SyntaxIDs, and I can't change anything. I need to create a select statement that returns all items that are in stock. Table rows look like this (I am simplifying it to convey the point): ProductSKU InStock AsOfDate 1234 Yes 04/05/06 1234 No 05/05/06 7777 Yes 01/01/01 7777 No 04/01/02 7777 Yes 05/01/05 8888 No 01/03/06 So, I want to create a table that returns 1234 and 7777 as available, and not return 8888. I feel like I have to join it to itself so that I can compare one row of 1234 to another row of 1234, and see if the latest date says "yes" in the InStock field, but I am not able to create the SQL Syntax. Or, if there was a way to just select the whole row of MAX(DATE) for each ProductSKU, then I could just look at the InStock field for that row, but I don't know how to get the whole row where the date is the biggest, and not the other rows. Thanks for any help. D. Patrick wrote:
Show quote > I didn't create the architecture, it doesn't have any unique numeric key -----BEGIN PGP SIGNED MESSAGE-----> IDs, and I can't change anything. I need to create a select statement that > returns all items that are in stock. > > Table rows look like this (I am simplifying it to convey the point): > > ProductSKU InStock AsOfDate > 1234 Yes 04/05/06 > 1234 No 05/05/06 > 7777 Yes 01/01/01 > 7777 No 04/01/02 > 7777 Yes 05/01/05 > 8888 No 01/03/06 > > So, I want to create a table that returns 1234 and 7777 as available, and > not return 8888. I feel like I have to join it to itself so that I can > compare one row of 1234 to another row of 1234, and see if the latest date > says "yes" in the InStock field, but I am not able to create the SQL Syntax. > > Or, if there was a way to just select the whole row of MAX(DATE) for each > ProductSKU, then I could just look at the InStock field for that row, but I > don't know how to get the whole row where the date is the biggest, and not > the other rows. Hash: SHA1 According to your example 1234 is NOT in stock at its latest date (5/5/06); therefore, only 7777 would be returned as the only item currently (7/1/06) in stock. SELECT ProductSKU, AsOfDate As LastStockDate FROM table_name As T1 WHERE AsOfDate = (SELECT MAX(AsOfDate) FROM table_name WHERE ProductSKU = T1.ProductSKU) AND InStock = 'Yes' -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBRKbXuYechKqOuFEgEQJODwCeMUKwczIrsZuFdPDhITRhMJ4Um9YAoK3H S6a/+d2YsUcQXYq2cQTVhOxH =OWHY -----END PGP SIGNATURE----- I have to admin that I'm a little confused.
If you want items that are 'available', and since item 1234 indicates in the latest entry that is not 'InStock', why would you want item 1234 included? However, to get the results you requested, try this: SELECT ProductSKU , InStock , max( AsOfDate ) FROM YourTable WHERE InStock = 'Yes' GROUP BY ProductSKU , InStock If my assumption that you would only want the items that are currently 'InStock is correct, the following query will return only item 7777. SELECT ProductSKU , InStock , AsOfDate FROM MyTest t1 WHERE ( AsOfDate = ( SELECT max( AsOfDate ) FROM MyTest t2 WHERE t1.ProductSKU = t2.ProductSKU ) AND InStock = 'Yes' ) ORDER BY ProductSKU -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "D. Patrick" <replywithinthegr***@thenotreal.com> wrote in message news:40Apg.5357$MF6.4651@tornado.socal.rr.com... >I didn't create the architecture, it doesn't have any unique numeric key > IDs, and I can't change anything. I need to create a select statement that > returns all items that are in stock. > > Table rows look like this (I am simplifying it to convey the point): > > ProductSKU InStock AsOfDate > 1234 Yes 04/05/06 > 1234 No 05/05/06 > 7777 Yes 01/01/01 > 7777 No 04/01/02 > 7777 Yes 05/01/05 > 8888 No 01/03/06 > > So, I want to create a table that returns 1234 and 7777 as available, and > not return 8888. I feel like I have to join it to itself so that I can > compare one row of 1234 to another row of 1234, and see if the latest date > says "yes" in the InStock field, but I am not able to create the SQL Syntax. > > Or, if there was a way to just select the whole row of MAX(DATE) for each > ProductSKU, then I could just look at the InStock field for that row, but I > don't know how to get the whole row where the date is the biggest, and not > the other rows. > > Thanks for any help. > > Sorry. 1234 should not be part of the results.
Show quote "D. Patrick" <replywithinthegr***@thenotreal.com> wrote in message news:40Apg.5357$MF6.4651@tornado.socal.rr.com... >I didn't create the architecture, it doesn't have any unique numeric key >IDs, and I can't change anything. I need to create a select statement >that returns all items that are in stock. > > Table rows look like this (I am simplifying it to convey the point): > > ProductSKU InStock AsOfDate > 1234 Yes 04/05/06 > 1234 No 05/05/06 > 7777 Yes 01/01/01 > 7777 No 04/01/02 > 7777 Yes 05/01/05 > 8888 No 01/03/06 > > So, I want to create a table that returns 1234 and 7777 as available, and > not return 8888. I feel like I have to join it to itself so that I can > compare one row of 1234 to another row of 1234, and see if the latest date > says "yes" in the InStock field, but I am not able to create the SQL > Syntax. > > Or, if there was a way to just select the whole row of MAX(DATE) for each > ProductSKU, then I could just look at the InStock field for that row, but > I don't know how to get the whole row where the date is the biggest, and > not the other rows. > > Thanks for any help. > > Try:
select m.* from ( select ProductSKU , max (AsOfDate) AsOfDate from MyTable group by ProductSKU ) x join MyTable m on m.ProductSKU = x.ProductSKU and m.AsOfDate = x.AsOfDate where m.InStock = 'Yes' -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. "D. Patrick" <replywithinthegr***@thenotreal.com> wrote in message Sorry. 1234 should not be part of the results.news:VhBpg.5475$MF6.3005@tornado.socal.rr.com... Show quote "D. Patrick" <replywithinthegr***@thenotreal.com> wrote in message news:40Apg.5357$MF6.4651@tornado.socal.rr.com... >I didn't create the architecture, it doesn't have any unique numeric key >IDs, and I can't change anything. I need to create a select statement >that returns all items that are in stock. > > Table rows look like this (I am simplifying it to convey the point): > > ProductSKU InStock AsOfDate > 1234 Yes 04/05/06 > 1234 No 05/05/06 > 7777 Yes 01/01/01 > 7777 No 04/01/02 > 7777 Yes 05/01/05 > 8888 No 01/03/06 > > So, I want to create a table that returns 1234 and 7777 as available, and > not return 8888. I feel like I have to join it to itself so that I can > compare one row of 1234 to another row of 1234, and see if the latest date > says "yes" in the InStock field, but I am not able to create the SQL > Syntax. > > Or, if there was a way to just select the whole row of MAX(DATE) for each > ProductSKU, then I could just look at the InStock field for that row, but > I don't know how to get the whole row where the date is the biggest, and > not the other rows. > > Thanks for any help. > > SELECT t.ProductSKU,
t.InStock, t.AsOfDate FROM #tmp t WHERE t.InStock = 'Yes' AND NOT EXISTS (SELECT 1 FROM #tmp t2 WHERE t2.ProductSKU = t.ProductSKU AND t2.AsOfDate > t.AsOfDate) Obviously you'll need to substitute both occurrances of '#tmp' with the name of your table. Chris Show quote "D. Patrick" wrote: > I didn't create the architecture, it doesn't have any unique numeric key > IDs, and I can't change anything. I need to create a select statement that > returns all items that are in stock. > > Table rows look like this (I am simplifying it to convey the point): > > ProductSKU InStock AsOfDate > 1234 Yes 04/05/06 > 1234 No 05/05/06 > 7777 Yes 01/01/01 > 7777 No 04/01/02 > 7777 Yes 05/01/05 > 8888 No 01/03/06 > > So, I want to create a table that returns 1234 and 7777 as available, and > not return 8888. I feel like I have to join it to itself so that I can > compare one row of 1234 to another row of 1234, and see if the latest date > says "yes" in the InStock field, but I am not able to create the SQL Syntax. > > Or, if there was a way to just select the whole row of MAX(DATE) for each > ProductSKU, then I could just look at the InStock field for that row, but I > don't know how to get the whole row where the date is the biggest, and not > the other rows. > > Thanks for any help. > > >  |
|||||||||||||||||||||||
Other interesting topics