Sql Query to get the active products during a certain timeframe

Question

I have the following table

Create table #products
(
productId int,
regionId int,
date datetime,
active bit
)

insert into #products values
(1,20,'10/10/2020',1),(1,20,'12/10/2022',0),(1,20,'5/8/2023',1),
(2,20,'11/11/2020',0),(2,20,'12/12/2022',1),(2,20,'5/8/2023',0),
(3,20,'10/10/2020',1),(3,20,'12/10/2022',0),(3,20,'5/8/2023',1)

I'm trying to find out which products were active between March 1, 2023, and March 30, 2023. Looking at the data provided, only product ID 2 was active during that period. How can I write a SQL query to accurately retrieve this information? I've tried a few queries, but I'm not getting the results I expect. Thanks in advance for your time!

Answer 1

CREATE TABLE #products (
  productId INT,
  regionId INT,
  date DATETIME,
  active BIT
  );
INSERT INTO #products VALUES
(1,20,'10/10/2020',1),(1,20,'12/10/2022',0),(1,20,'5/8/2023',1),
(2,20,'11/11/2020',0),(2,20,'12/12/2022',1),(2,20,'5/8/2023',0),
(3,20,'10/10/2020',1),(3,20,'12/10/2022',0),(3,20,'5/8/2023',1),
(4,20,'01/01/2020',1),
(5,20,'01/01/2020',0);
SELECT *, LEAD(date) OVER (PARTITION BY productId ORDER BY date) nextdate
FROM #products;

productId	regionId	date	active	nextdate
1	20	2020-10-10 00:00:00.000	True	2022-12-10 00:00:00.000
1	20	2022-12-10 00:00:00.000	False	2023-05-08 00:00:00.000
1	20	2023-05-08 00:00:00.000	True	null
2	20	2020-11-11 00:00:00.000	False	2022-12-12 00:00:00.000
2	20	2022-12-12 00:00:00.000	True	2023-05-08 00:00:00.000
2	20	2023-05-08 00:00:00.000	False	null
3	20	2020-10-10 00:00:00.000	True	2022-12-10 00:00:00.000
3	20	2022-12-10 00:00:00.000	False	2023-05-08 00:00:00.000
3	20	2023-05-08 00:00:00.000	True	null
4	20	2020-01-01 00:00:00.000	True	null
5	20	2020-01-01 00:00:00.000	False	null

DECLARE @from DATE = '03/01/2023';
DECLARE @till DATE = '03/31/2023';

WITH cte AS (
  SELECT *, COALESCE(LEAD(date) OVER (PARTITION BY productId ORDER BY date), 
                     DATEADD(dd, 1, @till)) nextdate
  FROM #products
  )
SELECT *
FROM cte
WHERE date <= @till
  AND @from <= nextdate
  AND active = 1;

productId	regionId	date	active	nextdate
2	20	2022-12-12 00:00:00.000	True	2023-05-08 00:00:00.000
4	20	2020-01-01 00:00:00.000	True	2023-04-01 00:00:00.000

fiddle

If you want to process your data for each region separately then expand PARTITION BY expression accordingly.

Answer 2

One more way:

WITH ActiveDateRange AS (
    SELECT ProductID, date as BeginDate, EndDate
    FROM #products AS P OUTER APPLY (SELECT MIN(date) AS EndDate FROM #Products p2 where p2.productid = p.productid and p.date<p2.date) a
    WHERE P.active = 1)
SELECT p.*, EndDate
FROM #products p inner join ActiveDateRange ADR on p.productID = ADR.productID and p.date = ADR.BeginDate
WHERE BeginDate <= '20230301' AND EndDate >= '20230330'

Answer 3

I tweaked my query and based on the suggestion provided by Akina this is what I have. Let me know if you think this query yields the correct results.

Declare @from date = '03/01/2021'
Declare @to   date = '03/31/2021'

;WITH MaxActiveDates AS (
SELECT productId
      ,MAX(date) AS max_active_date
  FROM #products
 WHERE active = 1
   AND regionId = 20
   AND date < @to
 GROUP BY productId
),
InactiveNextDates AS (
SELECT p.productId
      ,MIN(p.date) AS next_inactive_date
  FROM #products       p
  JOIN MaxActiveDates  m 
    ON p.productId   = m.productId
 WHERE p.active      = 0
   AND p.regionId    = 20
   AND p.date        > m.max_active_date
   AND p.date       >= @from
 GROUP BY p.productId
)
SELECT p.*
  FROM #products p
  JOIN MaxActiveDates m 
    ON p.productId = m.productId
  JOIN InactiveNextDates i 
    ON p.productId = i.productId
   AND p.date = m.max_active_date