I have the following data from a SQL DB. There are 98 columns in total this is an extract of 4 to illustrate what I would like to to and in the final query I would expect to require 51 of them. There are 15 million rows to this table.
+-----------------+-----------------+---------------------+------------------------------+ | RatePlan | RatePlanCharge | SubscriptionVersion | RatePlanChargePreDiscountMRR | +-----------------+-----------------+---------------------+------------------------------+ | | | | | | Main Product | Main Product | 7 | 90 | | | | | | | Online Support | Online Support | 7 | 0 | | | | | | | Module Support | Module Support | 7 | 0 | | | | | | | Main Product | Main Product | 6 | 90 | | | | | | | Module Support | Module Support | 6 | 0 | | | | | | | Main Product | Main Product | 5 | 90 | | | | | | | Premium Support | Premium Support | 5 | 40 | | | | | | | Module Support | Module Support | 5 | 0 | | | | | | | Main Product | Main Product | 4 | 90 | | | | | | | Module Support | Module Support | 4 | 0 | | | | | | | Main Product | Main Product | 3 | 90 | | | | | | | Online Support | Online Support | 3 | 0 | | | | | | | Module Support | Module Support | 3 | 0 | | | | | | | Main Product | Main Product | 2 | 90 | | | | | | | Online Support | Online Support | 2 | 0 | | | | | | | Main Product | Main Product | 1 | 85 | | | | | | | Online Support | Online Support | 1 | 0 | +-----------------+-----------------+---------------------+------------------------------+
This table shows each change to a subscription. We can see;
- At version 3 Module Support is added
- At version 4 Online Support is removed
- At version 5 Premium Support is added
- At version 6 Premium Support is removed
- At version 7 Online Support is added
What I want to do is run a query that will return rows missing from the current version that were present in the previous version, as below.
+-----------------+-----------------+---------------------+------------------------------+--------------------------------+ | RATEPLAN | RATEPLANCHARGE | SUBSCRIPTIONVERSION | RATEPLANCHARGEPREDISCOUNTMRR | SUBSCRIPTIONVERSION_MISSINGFROM | +-----------------+-----------------+---------------------+------------------------------+--------------------------------+ | | | | | | | Premium Support | Premium Support | 5 | 40 | 6 | | | | | | | | Online Support | Online Support | 3 | 0 | 4 | +-----------------+-----------------+---------------------+------------------------------+--------------------------------+
Is this possible?
Advertisement
Answer
SELECT n.RatePlan, n.SubscriptionVersion,
CASE
WHEN c.RatePlan IS NOT NULL AND p.RatePlan IS NOT NULL THEN 'Continued'
WHEN c.RatePlan IS NULL AND p.RatePlan IS NOT NULL THEN 'Discontinued'
WHEN c.RatePlan IS NOT NULL AND p.RatePlan IS NULL THEN 'New'
ELSE '!' END AS Status,
FROM (
-- The labels we need are everything in the current and previous version.
SELECT DISTINCT RatePlan, SubscriptionVersion
FROM (
-- Plans in the current verson.
SELECT RatePlan, SubscriptionVersion FROM WhateverItIs
UNION
-- Plans in the previous version.
SELECT p.RatePlan, w.SubscriptionVersion
FROM WhateverItIs w
INNER JOIN WhateverItIs p ON p.SubscriptionVersion + 1 = w.SubscriptionVersion
) T
) AS n
LEFT JOIN WhateverItIs c ON n.RatePlan = c.RatePlan AND n.SubscriptionVersion = c.SubscriptionVersion -- Is it current?
LEFT JOIN WhateverItIs p ON n.RatePlan = p.RatePlan AND n.SubscriptionVersion = p.SubscriptionVersion + 1 -- Is it previous?
I don’t generally think of FULL OUTER JOIN, but indeed that works too.
SELECT ISNULL(c.RatePlan, p.RatePlan) AS RatePlan,
ISNULL(c.SubscriptionVersion, p.SubscriptionVersion + 1) AS SubscriptionVersion,
CASE
WHEN c.RatePlan IS NOT NULL AND p.RatePlan IS NOT NULL THEN 'Continued'
WHEN c.RatePlan IS NULL AND p.RatePlan IS NOT NULL THEN 'Discontinued'
WHEN c.RatePlan IS NOT NULL AND p.RatePlan IS NULL THEN 'New'
ELSE '!'
END AS Status,
FROM WhateverItIs c
FULL OUTER JOIN WhateverItIs p ON c.RatePlan = p.RatePlan AND c.SubscriptionVersion = p.SubscriptionVersion + 1