There are 2 tables -

1. Sales table with details of order_number, item_number and sale_date.
2. Promotions table with details of item_number, promotion_start_date and promotion_end_date.

Promotions run all through the year over a range of days. Need is to extract list of items sold when no promotion was running.

Need to extract only the order numbers between

• "first promotion_end_date" and "second promotion_start_date" then
• "second promotion_end_date" and "third promotion_start_date" then
• "third promotion_end_date" and "fourth promotion_start_date" and so on.

Sample tables are as follows -

1) Promotions table : ( When promotion_end_date is 'Null', promotion is still 'ACTIVE')

2) Sales Table :

3) Expected Result :

CodePudding user response：

There are many ways to solve this kind of problem but I generally prefer NOT EXISTS:

SELECT order_number, item_number, sale_date
FROM dbo.Sales AS s
WHERE NOT EXISTS 
(
  SELECT 1 
    FROM dbo.Promotions AS p
    WHERE s.item_number = p.item_number
    AND s.sale_date >= p.promotion_start_date
    AND s.sale_date <= COALESCE(p.promotion_end_date, GETDATE())
);

I think the trickiest part here is substituting the end date with a valid point in time when the promotion is ongoing.

• Example db<>fiddle which shows how we love to see table structure and sample data and also corrects September 31st.