Need a SQL Server 2008 case declaration to evaluate an array and return two values

advertisements

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:

  1. If any of the SKUs have null or WEBNO as an IsActive value, the entire kit must return WEBNO. Otherwise, return WEBYES.
  2. 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 ISNULL and MIN / 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