Min date by product and ID

1

Hello,

I need to determine the minimum date for an ID associated with a product. The ID can change to another and than return to the original. I need the minimum date for the active sequence of the ID.
A few examples in the table below - in green the date that I want for each product.

image

How can I do this with a query?

Thanks.
Jorge

2

I don't understand your question but you should take a look at row_number. Based on that row number you can select the first. My best quess it will be

SELECT
  ROW_NUMBER() OVER(PARTITION BY [PRODUCT] ORDER BY [ID] ASC, [DATE] ASC) AS Row#
FROM YOUR_TABLE;

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs

4

I don't believe that code works correctly if multiple rows are in later sequences.

Let's adjust the data to add those cases:

truncate table temp;
insert into temp select 1,'a','2022-01-31'
insert into temp select 1,'a','2022-01-25'
insert into temp select 1,'b','2022-01-20'
insert into temp select 1,'a','2022-01-10'
insert into temp select 1,'a','2022-01-08'
insert into temp select 2,'a','2022-01-30'
insert into temp select 2,'a','2022-01-28'
insert into temp select 2,'b','2022-01-15'
insert into temp select 2,'b','2022-01-12'
insert into temp select 2,'b','2022-01-11'
insert into temp select 3,'a','2022-01-30'
insert into temp select 3,'a','2022-01-16'
insert into temp select 3,'a','2022-01-14'

Then try this query:


; WITH cte AS 
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Date DESC) - 
        ROW_NUMBER() OVER(PARTITION BY Product, ID ORDER BY Date DESC) AS row_num_diff
    FROM temp 
) 
SELECT Product, ID, Date
FROM (
    SELECT c.*, ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Date) AS row_num
    FROM cte c
    WHERE row_num_diff = 0
) AS derived
WHERE row_num = 1
1 Like
5

hi

i dont believe Scotts query gives what Jorge is looking for .. i could be wrong

based on my understanding and Scotts data .. here it is

; with rn_cte as
(
	select ROW_NUMBER() over(order by (select null)) as rn , * from temp 
) 
, grp_cte AS 
(  
	  SELECT *, 1 AS grp FROM   rn_cte  WHERE  rn = 1 
           UNION ALL 
      SELECT a.*, CASE WHEN a.Product = b.Product and a.id = b.id then  b.grp ELSE b.grp + 1  END  FROM   rn_cte a   JOIN grp_cte b  ON a.rn = b.rn + 1
) 
, filter_grp as 
(
select grp,count(rn) as cnttn from grp_cte group by grp having count(rn) > 1 
) 
select Product,Id,min(date),grp from grp_cte where grp in ( select grp from filter_grp ) group by Product,Id,grp

image

6

I believe the rows Jorge wanted returned are marked in green in the OP. I also believe those rows match the text given in the OP: "I need the minimum date for the active sequence of the ID."

7

so what do you say

JORGE ?

8

I'm sorry. Hadn't the chence to reply before.
The query from @ScottPletcher worked fine.

Thanks to all! :slight_smile:
Jorge

9

Thanks Scott. It worked.

Jorge

10

Nice Jorge

Glad it worked out for you

Thanks to Scott