Join Where Rows Don't Exist or Where Criteria Matches...?

Go To StackoverFlow.com

5

I'm trying to write a query to tell me which orders have valid promocodes. Promocodes are only valid between certain dates and optionally certain packages.

I'm having trouble even explaining how this works (see psudo-ish code below) but basically if there are packages associated with a promocode then the order has to have one of those packages and be within a valid date range otherwise it just has to be in a valid date range.

The whole "if PrmoPackage rows exist" thing is really throwing me off and I feel like I should be able to do this without a whole bunch of Unions. (I'm not even sure if that would make it easier at this point...)

Anybody have any ideas for the query?

if `OrderPromoCode` = `PromoCode`

    then if `OrderTimestamp` is between `PromoStartTimestamp` and `PromoEndTimestamp`

        then if `PromoCode` has packages associated with it
            //yes
                then if `PackageID` is one of the specified packages
                    //yes
                        code is valid
                    //no
                        invalid
            //no
                code is valid

Order:

OrderID* | OrderTimestamp | PackageID | OrderPromoCode
1        | 1/2/11         | 1         | ABC
2        | 1/3/11         | 2         | ABC
3        | 3/2/11         | 2         | DEF
4        | 4/2/11         | 3         | GHI

Promo:

PromoCode* | PromoStartTimestamp* | PromoEndTimestamp*
ABC        | 1/1/11               | 2/1/11
ABC        | 3/1/11               | 4/1/11
DEF        | 1/1/11               | 1/11/13
GHI        | 1/1/11               | 1/11/13

PromoPackage:

PromoCode* | PromoStartTimestamp* | PromoEndTimestamp* | PackageID*
ABC        | 1/1/11               | 2/1/11             | 1
ABC        | 1/1/11               | 2/1/11             | 3
GHI        | 1/1/11               | 1/11/13            | 1

Desired Result:

OrderID | IsPromoCodeValid
1       | 1
2       | 0
3       | 1
4       | 0
2012-04-04 20:10
by Greg
Why do both Promo and PromoPackage have start and end dates? Do they ever vary - GarethD 2012-04-04 20:30
ABC could be valid in January 2011, March 2011, December 2011, April 2057... - Greg 2012-04-04 20:56
So...is the PackageID column on the Order table nullable? If not, then in what situation would the start and end dates on the Promo table ever matter? Ah, I supposed in the case of DEF, where there is no associated PromoPackage record - Dr. Wily's Apprentice 2012-04-04 22:53


3

;WITH PromoCTE AS
(   SELECT  promo.*, CASE WHEN p.PromoCode IS NULL THEN 0 ELSE 1 END [HasPackage]
    FROM    Promo
            LEFT JOIN
            (   SELECT  DISTINCT PromoCode
                FROM    PromoPackage
            ) p
                ON promo.PromoCode = p.PromoCode
)
SELECT  [Order].OrderID, 
        CASE WHEN COUNT(CASE WHEN HasPackage = 1 THEN PromoPackage.PromoCode ELSE Promo.PromoCode END) >= 1 THEN 1 ELSE 0 END [IsPromoCodeValid]
FROM    [Order]
        LEFT JOIN PromoCTE promo
            ON Promo.PromoCode = [Order].OrderPromoCode
            AND [Order].OrderTimeStamp BETWEEN Promo.PromoStartTimestamp AND Promo.PromoEndTimestamp
        LEFT JOIN PromoPackage
            ON PromoPackage.PromoCode = OrderPromoCode
            AND PromoPackage.PackageID = [Order].PackageID
            AND [Order].OrderTimeStamp BETWEEN PromoPackage.PromoStartTimestamp AND PromoPackage.PromoEndTimestamp
GROUP BY [Order].OrderID;

NON CTE VERSION

SELECT  [Order].OrderID, 
        CASE WHEN COUNT(CASE WHEN HasPackage = 1 THEN PromoPackage.PromoCode ELSE Promo.PromoCode END) >= 1 THEN 1 ELSE 0 END [IsPromoCodeValid]
FROM    [Order]
        LEFT JOIN 
        (   SELECT  promo.*, CASE WHEN p.PromoCode IS NULL THEN 0 ELSE 1 END [HasPackage]
            FROM    Promo
                    LEFT JOIN
                    (   SELECT  DISTINCT PromoCode
                        FROM    PromoPackage
                    ) p
                        ON promo.PromoCode = p.PromoCode
        ) promo
            ON Promo.PromoCode = [Order].OrderPromoCode
            AND [Order].OrderTimeStamp BETWEEN Promo.PromoStartTimestamp AND Promo.PromoEndTimestamp
        LEFT JOIN PromoPackage
            ON PromoPackage.PromoCode = OrderPromoCode
            AND PromoPackage.PackageID = [Order].PackageID
            AND [Order].OrderTimeStamp BETWEEN PromoPackage.PromoStartTimestamp AND PromoPackage.PromoEndTimestamp
GROUP BY [Order].OrderID;
2012-04-04 20:27
by GarethD
This also doesn't account for the scenario of a valid order that has no packages associated with it. In your query if there's no package it's always invalid - JNK 2012-04-04 20:43
@JNK I have corrected this now, and I think this is now the only solution so far that correctly deals with the OPs criteria. Demonstrated here (note order 4 shows as valid in accepted answer and not in mine) - GarethD 2012-04-04 21:19
I'm getting an error when I try to make a view with this query... http://dl.dropbox.com/u/3115379/screengrab_20120404192746.pn - Greg 2012-04-04 23:28
Does PromoPackage contain a column called PromoCode? The error implies that the column or table name is wrong - GarethD 2012-04-04 23:33
@GarethD yes, PromoPackage contains a column PromoCode, it works when I run it in a new query window but throws that error when I paste it into a view...? any ideas - Greg 2012-04-04 23:43
How are you creating your view? Seems to work fine for meGarethD 2012-04-04 23:57
I added unique variable names for all the tables and it works now, thanks for all the help I'll upvot - Greg 2012-04-04 23:59


3

Agh....I think there are a few ways to do it:

Here's a somewhat messy approach using a couple of left outer joins, a group by, and a case statement

SELECT [Order].OrderID, CASE count(isnull(Promo.PromoCode, PromoPackage.PromoCode)) WHEN 0 THEN 0 ELSE 1 END
FROM [Order]
LEFT OUTER JOIN Promo ON
    Promo.PromoCode = [Order].OrderPromoCode
    AND [Order].OrderTimestamp BETWEEN Promo.PromoStartTimestamp and Promo.PromoEndTimestamp
    AND NOT EXISTS (SELECT 1 FROM PromoPackage WHERE PromoPackage.PromoCode = Promo.PromoCode)
LEFT OUTER JOIN PromoPackage ON
    PromoPackage.PromoCode = [Order].OrderPromoCode
    AND PromoPackage.PackageID = [Order].PackageID
    AND [Order].OrderTimestamp BETWEEN PromoPackage.PromoStartTimestamp and PromoPackage.PromoEndTimestamp
GROUP BY
    [Order].OrderID

You might be able to do this in a cleaner manner using a CTE.

EDIT: updated with a query using a CTE

WITH OrderPromo (OrderID, PromoCode, PackageID)
AS
(
SELECT [Order].OrderID, Promo.PromoCode, null
FROM [Order]
INNER JOIN Promo ON
    [Order].OrderPromoCode = Promo.PromoCode
    AND [Order].OrderTimestamp BETWEEN Promo.PromoStartTimestamp AND Promo.PromoEndTimestamp
    AND NOT EXISTS (SELECT 1 FROM PromoPackage WHERE PromoPackage.PromoCode = Promo.PromoCode)

UNION ALL

SELECT [Order].OrderID, PromoPackage.PromoCode, PromoPackage.PackageID
FROM [Order]
INNER JOIN PromoPackage ON
    [Order].OrderPromoCode = PromoPackage.PromoCode
    AND [Order].PackageID = PromoPackage.PackageID
    AND [Order].OrderTimestamp BETWEEN PromoPackage.PromoStartTimestamp AND PromoPackage.PromoEndTimestamp
)
SELECT [Order].OrderID, 1
FROM [Order]
WHERE
    EXISTS (SELECT 1 FROM OrderPromo WHERE OrderPromo.OrderID = [Order].OrderID)

UNION ALL

SELECT [Order].OrderID, 0
FROM [Order]
WHERE
    NOT EXISTS (SELECT 1 FROM OrderPromo WHERE OrderPromo.OrderID = [Order].OrderID)
;

EDIT: one more solution. This one creates a "Promotion" table by combining the Promo and PromoPackage tables. Promo records that have no associated PromoPackage record effectively have a PackageID of null.

SELECT
  [Order].OrderID,
  CASE count(Promotion.PromoCode) WHEN 0 THEN 0 ELSE 1 END
FROM [Order]
LEFT OUTER JOIN (
    SELECT
      Promo.PromoCode,
      PromoPackage.PackageID,
      isnull(PromoPackage.PromoStartTimestamp, Promo.PromoStartTimestamp) as PromoStartTimestamp,
      isnull(PromoPackage.PromoEndTimestamp, Promo.PromoEndTimestamp) as PromoEndTimestamp
  FROM Promo
  LEFT OUTER JOIN PromoPackage ON
      Promo.PromoCode = PromoPackage.PromoCode
) Promotion ON
    Promotion.PromoCode = [Order].OrderPromoCode
    AND (Promotion.PackageID is null OR Promotion.PackageID = [Order].PackageID)
    AND [Order].OrderTimestamp BETWEEN Promotion.PromoStartTimestamp AND Promotion.PromoEndTimestamp
GROUP BY
  [Order].OrderID
2012-04-04 20:26
by Dr. Wily's Apprentice
+1 - I think you are the only person who both answered AND read the question, as this is the only query I see that accounts for both the OP's scenarios appropriately - JNK 2012-04-04 20:45
thank you sir, it looks like it's working, and @JNK thank you for reviewing answer - Greg 2012-04-04 21:12
This returns order 4 as being valid? Surely that is not the required behaviour - GarethD 2012-04-04 21:14
@GarethD Hmm, I think order 4 should have a valid promo, right? The order date is 4/2/2011, which falls between promotion dates 1/1/11 and 1/11/13 for promo code GHI, package ID 1. If that is the case, then the desired result in the question is incorrect, or the example data is incorrect - Dr. Wily's Apprentice 2012-04-04 22:27
@GarethD is correct. This query is incorrect. Order 4 should not be valid because GHI is only valid on package - Greg 2012-04-04 22:30
Hmm, I think we are understanding the requirements differently, then. My understanding was that the promotion was valid if the order date fell within the date range of a promotion in either the Promo table or the PromoPackage table. Also, on second glance, it looks like the order date for order #4 falls within the date range for the "GHI" records in both the Promo table and the PromoPackage table - Dr. Wily's Apprentice 2012-04-04 22:34
It falls within the date range but fails the promopackage because there is a promopackage defined, I know this query is a pain in the ass, I didn't make that clear in my pos - Greg 2012-04-04 22:36
Doh! My appologies. I missed the fact that the Order table also has a PackageID column. Sorry for the confusion - Dr. Wily's Apprentice 2012-04-04 22:38
As I understand it DEF is valid on all packages between the dates defined in the promo table because there is no entry for promo code DEF in promopackage. GHI is only valid on package 1 because there is an entry in the promopackage table - GarethD 2012-04-04 22:39
@GarethD yes you are correc - Greg 2012-04-04 22:40
This latest edit doesn't work either. Order 4 still shows as valid, you need to exclude promocodes with a package from the top half of the union within the CTE - GarethD 2012-04-04 22:49
@GarethD Ah, right. I see that it's still valid due to the GHI record in the Promo table. I suppose the phrase "and optionally certain packages" is partly what threw me off - Dr. Wily's Apprentice 2012-04-04 22:51
@Dr.Wily'sApprentice It took me a couple of attempts to get there too. I think I got there in the end. There is some sample data on SQL Fiddle if it help - GarethD 2012-04-04 22:54
@GarethD THANK YOUUUUUUUUU IT WORKSSSSS. I didnt know there was an SQLfiddle.com eithe - Greg 2012-04-04 23:04
@GarethD I saw your link to SQL Fiddle, and I did find it very helpful, as well as your comments to help understand the requirements. Thank you very much, and +1 on your answer as well - Dr. Wily's Apprentice 2012-04-04 23:07


0

You might be able to accomplish this via correlated subquery. I didn't test this, but:

SELECT  
    a.OrderID,
    CASE WHEN 0 <= (
            SELECT COUNT(*)
            FROM PromoCode x
            JOIN PromoPackage y
                ON y.PromoCode = x.PromoCOde
                AND a.OrderTimeStamp BETWEEN y.PromoSTartTimestamp AND y.PromoEndTimestamp
            WHERE
                x.PromoCode = a.OrderPromoCode              
        ) THEN 0
    ELSE 1
    END AS 'IsPromoCodeValid'

FROM    
    Order a     
2012-04-04 20:24
by Seth
This does not account for the valid timestamp but no packages scenari - JNK 2012-04-04 20:39
Ahh, I didn't read the question correctly. Dr. Wiley Apprentice's answer seems to be the best solution - Seth 2012-04-04 20:56
In your defense it's a pretty convoluted requiremen - JNK 2012-04-04 20:58


0

I didn't try recreating your tables, but this query should come close

SELECT o.OrderID, case ISNULL(pck.PackageID, 0) when 0 then 0 else 1 end as IsPromoCodeValid
FROM [Order] as o 
LEFT OUTER JOIN [Promo] as p ON
o.OrderPromoCode = p.PromoCode AND o.OrderTimestamp >= p.PromoStartTimestamp AND o.OrderTimestamp <= p.PromoStartTimestamp
LEFT OUTER JOIN [PromoPackage] pck ON  o.PackageID = pck.PackageID AND p.PromoCode = pck.PromoCode
2012-04-04 20:26
by user1313773
This also doesn't account for the scenario of a valid order that has no packages associated with it. In your query if there's no package it's always invali - JNK 2012-04-04 20:44
All you would have to do in that case would be to modify the case statement. Have you tried comparing execution times? My proposed solution does not require any UNIONS and with LEFT JOINS on primary keys it should be pretty fast - user1313773 2012-04-05 14:00


-1

Something like this might work (untested):

select o.OrderID,
  isPromoCodeValid = 
    isnull((select 1 from Promo p where o.OrderTimestamp >= p.PromoStartTimestamp and o.OrderTimestamp <= p.PromoEndTimestamp), 0)
    or isnull((select 1 from PromoPackage pp where o.OrderTimestamp >= pp.PromoStartTimestamp and o.OrderTimestamp <= pp.PromoEndTimestamp and o.PackageID = pp.PackageID), 0)
from orders o
2012-04-04 20:22
by Keith
This will return valid even if the promo has packages that aren't met, as long as the dates line up - JNK 2012-04-04 20:47
Ads