I'm a novice SQL programmer and have been banging my head against this all morning, so please bear with me. My situation is this: I have a table of SKUs that need to be sent to our eCommerce website. Each of these SKUs has a 'quantity', an 'active' value, and a 'discontinued' value. This was easy enough to handle when we were dealing with one SKU at a time, but now I have to send kits, which contain one or more SKUs.
For example, if my Kit's ID is 000920_001449_001718_999999 (a combination of four SKUs) I need to collect data for the entire set of SKUs like so:
Here's the logic I need to incorporate:
- If any of the SKUs have null or WEBNO as an IsActive value, the entire kit must return WEBNO. Otherwise, return WEBYES.
- If any of the SKUs have null or '1' as an IsDiscontinued value, the entire kit must return IsDiscontinued = '1'. Otherwise, return a 0.
My code is a bit of a mess, but here's what I've managed so far:
SELECT CASE WHEN 'WEBNO' in ( SELECT IsActive FROM #SkusToSend as Sending RIGHT JOIN ( SELECT * FROM [eCommerce].[dbo].[Split] ( '000920_001449_001718_999999' ,'_') ) as SplitSkus on Sending.SKU = SplitSkus.items ) THEN 'WEBNO' ELSE 'WEBYES' END
My question is this: Is it possible to write a statement that parses through my example table, returning only one row of 'IsActive' and 'IsDiscontinued'? I've tried using GROUP BY and HAVING statements on those fields, but always get multiple rows returned.
The code I have handles the WEBNO value, but not NULL, and doesn't even start to take into consideration the IsDiscontinued field yet. Is there a concise way to parse this together, or a better way to handle this type of problem?
I think a combination of
MAX should do the trick:
SELECT MIN(ISNULL(sending.IsActive, 'WEBNO')) AS IsActive, MAX(ISNULL(sending.IsDiscontinuted, 1)) AS IsDiscontinuted FROM ( SELECT * FROM [eCommerce].[dbo].[Split] ( '000920_001449_001718_999999' ,'_') ) AS SplitSkus LEFT JOIN #SkusToSend AS Sending AS Sending.SKU = SplitSkus.items