any way to accomplish this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

any way to accomplish this?

Author

Topic

bglodde
Starting Member

25 Posts

Posted - 2002-04-01 : 13:06:23

The query below returns a set of data that contains (sometimes) sets of records that have the same sku, sku style, and department, but different "EFF_DATE"s. I need the most recent EFF_DATE for each record who has a similar counterpart, but cannot use anything that's tied to a certain database. In otherwords, database agnostic, no stored procs, proprietary keywords, etc.

Please let me know if you have any ideas how I might be able to do this. I am out of ideas :)

SELECT DISTINCT
TBLPSKU10.SKU, TBLPSKU10.SKU_STYLE, TBLPSKU10.COMPANY, TBLPSKU10.SKU_SIZE, TBLPSKU00.POS_DESC, TBLPSKU20.QTY_PER_RETAIL, TBLPSKU20.RETAIL,
TBLDEPT10.DEPARTMENT, TBLDEPT10.DEPARTMENT_DESC, TBLMESR10.MEASURE, TBLMESR10.MEASURE_ABBREV, TBLPSKU20.EFF_DATE
FROM TBLPSKU10
INNER JOIN TBLPSKU20 ON (TBLPSKU20.COMPANY = TBLPSKU10.COMPANY AND TBLPSKU10.SKU = TBLPSKU20.SKU)
INNER JOIN TBLPSKU00 ON (TBLPSKU00.COMPANY = TBLPSKU10.COMPANY AND TBLPSKU00.SKU_STYLE = TBLPSKU10.SKU_STYLE)
INNER JOIN TBLDEPT10 ON (TBLDEPT10.DEPARTMENT = TBLPSKU00.DEPARTMENT)
INNER JOIN TBLMESR10 ON (TBLPSKU10.SKU_MEASURE = TBLMESR10.MEASURE)
WHERE TBLPSKU10.COMPANY = 1 AND TBLDEPT10.DEPT_ACCUM_LEVEL = 1
ORDER BY SKU, DEPARTMENT_DESC

* I know the database design appears cryptic; I am only contracting on the job. *sigh*

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-01 : 13:14:15

Take a look at Jay's reply (2nd one):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14281

If your database product(s) support subqueries, then that should work for you. Basically you write a subquery to find the max (or min) date value for each group, then JOIN that subquery to your normal query. Make sure you JOIN on all of the common columns (date, SKU, SKU_STYLE, etc.)

bglodde
Starting Member

25 Posts

Posted - 2002-04-01 : 13:15:35

Rob, thanks for the super quick reply, I am checking across the supported dbs for subquery. Hope it works :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources