CODEWITHSUNDEEP
sqlazureazure-databricks
lalaenil
lalaenil

Reputation: 13

SQL: Finding min, max date with repeated value

If I have data like below and I need to find min and max date of each promotion order by date Please help me for SQL script

Data:
Sample Data

Data:   
month           promotion
31/01/2019          A
28/02/2019          A
31/03/2019          B
30/04/2019          C
31/05/2019          C
30/06/2019          C
31/07/2019          C
31/08/2019          C
30/09/2019          B
31/10/2019          B
30/11/2019          B
31/12/2019          B

need result:
Need result

need result:        
min_month   max_month   promotion
31/01/2019  28/02/2019  A
31/03/2019  31/03/2019  B
30/04/2019  31/08/2019  C
30/09/2019  31/12/2019  B

result i got: incorrect
result i got: incorrect

min_month   max_month   promotion
31/01/2019  28/02/2019  A
31/03/2019  31/12/2019  B
30/04/2019  31/08/2019  C

Thank you

My script

select promotion, min(month) min_month, max(month) max_month  
from table  
group by promotion

Upvotes: 1

Views: 474

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also use lag() to fill the gap :

select promotion, min(month) as min_month, max(month) as max_month
from (select t.*, 
             sum(case when promotion <> prev then 1 else 0 end) over (order by month) as grp
      from (select t.*, 
                   lag(promotion) over (order by month) as prev
            from table t
           ) t
     ) t
group by promotion, grp
order by min(month);

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

This is form of GAPS and ISLAND problem. If your DBMS supports window functions, You may try below query -

select min(month) min_month, max(month) max_month, promotion
from (select promotion,
             month,
             rank() over(order by month) seqnum1,
             rank() over(partition by promotion order by month) seqnum2
      from table) t
group by promotion, seqnum1 - seqnum2;

Upvotes: 1

Related Questions