[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

select only the product with the most current year

In the below query I have to add the additional logic of:

In the USR_PRODUCT_DETAIL Table
records can have the following data
STD_ABBV          Year
Z100                   1966-01-01
Z100                    1972-01-01
Z100                    2012-01-01
B222                   1976-01-01
B222                   1997-01-01
....
more STD_ABBV values

I need to only grab the record with a STD_ABBV with the latest year.  So Z100-2012 and B222-1997.  Not all records will have a STD_ABBV in which case there would be a null value and those should be ignored.

SELECT 
	p.PRODUCT_ID, 
	p.PRODUCT_CODE,
	p.SUBSYSTEM,
	p.PRODUCT_CLASS_CODE, 
	p.AVAILABLE_TO_ORDERS_FLAG,
	ISBN_WN = ISNULL(upd.ISBN, ''),
	TITLE = ISNULL(p.LONG_NAME, ''),
	AUTHORS_WN = ISNULL(upd.AUTHORS, ''),
	NUM_PAGES_WN = ISNULL(upd.NUM_PAGES, 0),
	KEYWORDS_WN = STUFF(COALESCE((SELECT '^' + pk.KEYWORD
								  FROM dbo.PRODUCT_KEYWORD pk
								  WHERE pk.PRODUCT_ID = p.PRODUCT_ID
								  FOR XML PATH('')),' '),1,1,''),
	CATEGORIES = STUFF(COALESCE((SELECT '^' + pcat.CATEGORY
								 FROM dbo.PRODUCT_CATEGORY pcat
								 WHERE pcat.PRODUCT_ID = p.PRODUCT_ID
								 FOR XML PATH('')),' '),1,1,''),
	CATEGORY_DESCRIPTIONS = STUFF(COALESCE((SELECT '^' + appc.DESCR
								 FROM PRODUCT_CATEGORY pcat
								 JOIN APP_CODE appc ON pcat.CATEGORY = appc.CODE											 
								 WHERE pcat.PRODUCT_ID = p.PRODUCT_ID AND appc.SUBSYSTEM = 'ORD' AND appc.TYPE = 'PRODUCT_CATEGORY' AND pcat.CATEGORY NOT IN ('PROC', 'STAND', 'CD-SFWE', 'MANUALS', 'VIDEOS', 'RFREPORTS')
								 FOR XML PATH('')),' '),1,1,''),											 
	SUBCATEGORY_DESCRIPTIONS = STUFF(COALESCE((SELECT '^' + appsc.DESCR
									FROM PRODUCT_CATEGORY pc
									JOIN PRODUCT_SUB_CATEGORY psc ON pc.PRODUCT_CATEGORY_ID = psc.PRODUCT_CATEGORY_ID
									JOIN APP_SUBCODE appsc ON appsc.SUBCODE = psc.SUB_CATEGORY
									WHERE pc.PRODUCT_ID = p.PRODUCT_ID AND appsc.SUBSYSTEM = 'ORD' AND appsc.TYPE = 'PRODUCT_CATEGORY'
									FOR XML PATH('')),' '),1,1,''), 									 
	ISSN_WN = ISNULL(upd.ISSN, ''),
	LOC_WN = ISNULL(upd.LOC, ''),
	PUB_YEAR_INV = ISNULL(f.USR_PUB_YEAR, ''),
	PUB_DATE_WN = ISNULL(upd.PUB_DATE, ''), -- looks like this inserts a default value of 1900-01-01 if it is null
	-- PUBLISHER_INV = ISNULL(f.USR_PUBLISHER, ''), -- These were added to USR_PRODUCT_DETAIL
	PUBLISHER_WN = ISNULL(upd.PUBLISHER, ''),
	VOLUME_WN = ISNULL(upd.VOLUME, -1),
	ISSUE_NO_WN = ISNULL(upd.ISSUE_NO, -1),
	EDITION_WN = ISNULL(upd.EDITION, -1),
	CODEN_WN = ISNULL(upd.CODEN, ''),
	CORPORATE_SOURCE_WN = ISNULL(upd.CORPORATE_SOURCE, ''),
	DOI_WN = ISNULL(upd.DOI, ''),
	PAGE_RANGE_WN = ISNULL(upd.PAGE_RANGE, ''),
	ACCESSION_NO_WN = ISNULL(upd.WN_ACCESSION_NO, ''),
	SUB_CLASS_CODE_WN = ISNULL(upd.SUB_CLASS_CODE, ''),
	STD_ABBV_CODE_WN = ISNULL(upd.STD_ABBV_CODE,''),
	DOCUMENT_TYPE_CODE_WN = ISNULL(upd.DOCUMENT_TYPE_CODE, ''),
	TEXT = ISNULL(t.TEXT, ''), -- this is the abstract/long description
	EVENT_START_DATE = ISNULL(START_DATE, ''),
	EVENT_END_DATE = ISNULL(END_DATE, ''),
	MEETING_LOCATION = ISNULL(mpl.LOCATION, ''),
	LIST_PRICE = ISNULL(p.ListPrice, -1),
	MEMBER_PRICE = ISNULL(p.MemberPrice, -1),
	SMALL_IMAGE_FILE_NAME = ISNULL(p.SMALL_IMAGE_FILE_NAME, ''),
	LARGE_IMAGE_FILE_NAME = ISNULL(p.LARGE_IMAGE_FILE_NAME, ''), 
	PRODUCT_URL = ISNULL(pwc.PRODUCT_URL, '') 
FROM dbo.WEB_PRODUCT_VW p
	LEFT OUTER JOIN dbo.PRODUCT_TEXT t ON t.PRODUCT_ID = p.PRODUCT_ID and t.TEXT_TYPE_CODE = 'WEB_LONG'
	LEFT OUTER JOIN dbo.USR_PRODUCT_FACTS f ON f.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.INV_PRODUCT i ON i.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.USR_PRODUCT_DETAIL upd ON upd.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.MTG_PRODUCT_LOCATION mpl ON mpl.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.PRODUCT_WEB_CONTROL pwc ON pwc.PRODUCT_ID = p.PRODUCT_ID
WHERE (p.SUBSYSTEM = 'INV' OR p.SUBSYSTEM = 'ECD' OR p.SUBSYSTEM = 'MTG' OR p.SUBSYSTEM = 'MBR') 
	   AND ((p.PRODUCT_CODE = p.PARENT_PRODUCT) OR (p.PRODUCT_ID = '30' OR p.PRODUCT_ID = '82' OR p.PRODUCT_ID = '264')) 
	   AND p.AVAILABLE_TO_ORDERS_FLAG = 'Y'
	   AND p.END_DATE >= CURRENT_TIMESTAMP
ORDER BY p.PRODUCT_ID

Open in new window

0
PurpleSlade
Asked:
PurpleSlade
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
when you run this:
select std_abbv, max([Year]) max_year
from USR_PRODUCT_DETAIL 
group by std_abbv

Open in new window

you get, for each value of std_abbv, the max year value.

from there, you can join back to this or other tables to get other records:

select d.*
  from  (
select std_abbv, max([Year]) max_year
from USR_PRODUCT_DETAIL 
group by std_abbv ) a
JOIN USR_PRODUCT_DETAIL d
  ON d.std_abbv = a.std_abbv
 AND d.[year] = a.max_year

Open in new window


you may also want to read this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
PurpleSladeAuthor Commented:
Hi Angel - ok, i understand your query as constructed.  I am having difficulty understanding how to fit it into the existing query.  I am already pulling these 2 fields in the main query (STD_ABBV_CODE_WN and PUB_DATE).  I am not sure how to fit the other select with the group by in.  I will play with it more and see if I can rethink it and maybe take it in small pieces as you started for me.
0
 
PurpleSladeAuthor Commented:
Angel - a problem I am having with this solution is that I am not sure how to keep the rest of the products from the USR_PRODUCT_DETAIL table.  There are 1000s of products in this table which are being grabbed in the above query I originally posted.  How do I keep the rest of the products showing up?
0
 
PurpleSladeAuthor Commented:
I think that this answered my question as I posed it but I think that I need to rephrase the question because I wasn't clear about what I need.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now