Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: Finding orders that have at least 1 promo item and 1 non-promo item

    I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.

    I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:

    Code:
    create table #Promos (
      Offer varchar(2) null,
      SKU int null,
      StartDt date null,
      EndDt date null
      )
      
    create table #Sales (
      OrderNo varchar(5) null,
      SKU int null,
      OrderDt date null
      )
      
    INSERT INTO #Promos
    VALUES ('JF',1,'20140101','20140228')
    ,('JF',2,'20140101','20140228')
    ,('JF',3,'20140101','20140228')
    ,('MA',1,'20140301','20140430')
    ,('MA',5,'20140301','20140430')
    ,('MA',6,'20140301','20140430')
    
    
    INSERT INTO #Sales
    VALUES ('A1111',1,'20140103')
    ,('A1111',5,'20140103')
    ,('A1111',2,'20140103')
    ,('A2222',1,'20140310')
    ,('A2222',5,'20140310')
    ,('A3333',99,'20140105')
    ,('A4444',1,'20140501')
    
    
    --Orders that have at least one Promo Item
    SELECT distinct Offer,OrderNo
    INTO #OrdersWithAtLeastOnePromoItem
    FROM #Sales a
    INNER JOIN #Promos b
    ON a.sku = b.sku
    AND a.OrderDt >= b.StartDt
    AND a.OrderDt <= b.EndDt
    
    
    --All Orders placed during promo timeframes (may or may not have promo items in them)
    SELECT distinct Offer,OrderNo
    INTO #OrdersPlacedDuringPromo
    FROM #Sales a
    INNER JOIN #Promos b
    ON a.OrderDt >= b.StartDt
    AND a.OrderDt <= b.EndDt
    
    
    --Orders that have 0 promo items
    SELECT distinct OrderNo,Offer
    INTO #OrdersWithNoPromoItems
    FROM #OrdersPlacedDuringPromo a
    WHERE not exists(SELECT * FROM #OrdersWithAtLeastOnePromoItem b WHERE a.OrderNo = b.OrderNo)
    So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm lazy:
    Code:
    create table #Promos (
      Offer varchar(2) null,
      SKU int null,
      StartDt date null,
      EndDt date null
      )
      
    create table #Sales (
      OrderNo varchar(5) null,
      SKU int null,
      OrderDt date null
      )
      
    INSERT INTO #Promos
    VALUES ('JF',1,'20140101','20140228')
    ,('JF',2,'20140101','20140228')
    ,('JF',3,'20140101','20140228')
    ,('MA',1,'20140301','20140430')
    ,('MA',5,'20140301','20140430')
    ,('MA',6,'20140301','20140430')
    
    
    INSERT INTO #Sales
    VALUES ('A1111',1,'20140103')
    ,('A1111',5,'20140103')
    ,('A1111',2,'20140103')
    ,('A2222',1,'20140310')
    ,('A2222',5,'20140310')
    ,('A3333',99,'20140105')
    ,('A4444',1,'20140501')
    
    --Show how many items on an order, and how many are promo items
    SELECT s.OrderNo
    ,  Count(*)       AS AllItems
    ,  Count(p.Offer) AS PromoItem
       FROM #Sales AS s
       LEFT JOIN #Promos AS p
          ON (p.SKU = s.SKU
    	  AND s.OrderDt BETWEEN p.StartDt AND p.EndDt)
       GROUP BY s.OrderNo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    This is a great way of going about it, but there are 2 issues. I need to have the result set broken out by offer since maangement is using this to understand the impact of each set of promos (offer). Also, the result set shows order A4444 which was ordered outside of any of the promo time frames. Here is how I adapted your code. Maybe there is a cleaner way though.

    Code:
    create table #Promos (
      Offer varchar(2) null,
      SKU int null,
      StartDt date null,
      EndDt date null
      )
      
    create table #Sales (
      OrderNo varchar(5) null,
      SKU int null,
      OrderDt date null
      )
      
    INSERT INTO #Promos
    VALUES ('JF',1,'20140101','20140228')
    ,('JF',2,'20140101','20140228')
    ,('JF',3,'20140101','20140228')
    ,('MA',1,'20140301','20140430')
    ,('MA',5,'20140301','20140430')
    ,('MA',6,'20140301','20140430')
    
    
    INSERT INTO #Sales
    VALUES ('A1111',1,'20140103')
    ,('A1111',5,'20140103')
    ,('A1111',2,'20140103')
    ,('A2222',1,'20140310')
    ,('A2222',5,'20140310')
    ,('A3333',99,'20140105')
    ,('A4444',1,'20140501')
    
    SELECT distinct Offer,OrderNo
    INTO #OrdersPlacedDuringPromo
    FROM #Sales a
    INNER JOIN #Promos b
    ON a.OrderDt >= b.StartDt
    AND a.OrderDt <= b.EndDt
    
    --Show how many items on an order, and how many are promo items
    WITH cte AS (SELECT s.OrderNo
    ,  Count(*)       AS AllItems
    ,  Count(p.Offer) AS PromoItem
       FROM #Sales AS s
       LEFT JOIN #Promos AS p
          ON (p.SKU = s.SKU
    	  AND s.OrderDt BETWEEN p.StartDt AND p.EndDt)
       WHERE exists(SELECT * FROM #OrdersPlacedDuringPromo x WHERE s.orderno = x.orderno)
       GROUP BY s.OrderNo)
    SELECT x.Offer,cte.*
    FROM #OrdersPlacedDuringPromo x
    INNER JOIN cte
    ON x.orderno = cte.orderno

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Again, I'm lazy:
    Code:
    --Show how many items on an order, and how many are promo items
    SELECT s.OrderNo
    ,  Count(*)       AS AllItems
    ,  Count(p.Offer) AS PromoItem
    ,  Min(p.Offer)   AS Offer
    ,  CASE
          WHEN 0 = Count(p.Offer) THEN 'Regular'
    	  WHEN Count(*) = Count(p.Offer) THEN 'Promo'
    	  ELSE 'Mixed'
       END AS status
       FROM #Sales AS s
       LEFT JOIN #Promos AS p
          ON (p.SKU = s.SKU
    	  AND s.OrderDt BETWEEN p.StartDt AND p.EndDt)
       GROUP BY s.OrderNo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Again, I'm lazy:
    Code:
    --Show how many items on an order, and how many are promo items
    SELECT s.OrderNo
    ,  Count(*)       AS AllItems
    ,  Count(p.Offer) AS PromoItem
    ,  Min(p.Offer)   AS Offer
    ,  CASE
          WHEN 0 = Count(p.Offer) THEN 'Regular'
    	  WHEN Count(*) = Count(p.Offer) THEN 'Promo'
    	  ELSE 'Mixed'
       END AS status
       FROM #Sales AS s
       LEFT JOIN #Promos AS p
          ON (p.SKU = s.SKU
    	  AND s.OrderDt BETWEEN p.StartDt AND p.EndDt)
       GROUP BY s.OrderNo
    -PatP

    so close. Order A3333 should show up as offer "JF" because it was placed during the JF offer time frame (despite not containing any JF promo items). And order A4444 should not show up at all because it was placed outside of any of the promo time frames (It could also just stay as null offer, and i could filter it out from there).
    Last edited by clawlan; 10-17-14 at 12:56.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're making it harder for me to claim that I'm lazy!
    Code:
    --Show how many items on an order, and how many are promo items
    SELECT s.OrderNo
    ,  Count(*)       AS AllItems
    ,  Count(p.Offer) AS PromoItem
    ,  a.Offer
    ,  Min(p.Offer)   AS OrderOffer
    ,  CASE
          WHEN 0 = Count(p.Offer) THEN 'Regular'
    	  WHEN Count(*) = Count(p.Offer) THEN 'Promo'
    	  ELSE 'Mixed'
       END AS status
       FROM #Sales AS s
       JOIN #Promos AS a
          ON (s.OrderDt BETWEEN a.StartDt AND a.EndDt)
       LEFT JOIN #Promos AS p
          ON (p.SKU = s.SKU
    	  AND p.Offer = a.Offer
    	  AND s.OrderDt BETWEEN p.StartDt AND p.EndDt)
       GROUP BY s.OrderNo, a.Offer
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    You're making it harder for me to claim that I'm lazy!
    Code:
    --Show how many items on an order, and how many are promo items
    SELECT s.OrderNo
    ,  Count(*)       AS AllItems
    ,  Count(p.Offer) AS PromoItem
    ,  a.Offer
    ,  Min(p.Offer)   AS OrderOffer
    ,  CASE
          WHEN 0 = Count(p.Offer) THEN 'Regular'
    	  WHEN Count(*) = Count(p.Offer) THEN 'Promo'
    	  ELSE 'Mixed'
       END AS status
       FROM #Sales AS s
       JOIN #Promos AS a
          ON (s.OrderDt BETWEEN a.StartDt AND a.EndDt)
       LEFT JOIN #Promos AS p
          ON (p.SKU = s.SKU
    	  AND p.Offer = a.Offer
    	  AND s.OrderDt BETWEEN p.StartDt AND p.EndDt)
       GROUP BY s.OrderNo, a.Offer
    -PatP
    Ah, great! The AllItems and PromoItem values go a little wacky, but I don't seed them so I took them out. Thank you for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •