Bob Bob - 9 days ago 8
SQL Question

SQL Query for Separated Group By

please consider below table

ProductCode     Flag    Date
-----------------------------------------

A                        N      01/10/2015

A                        N      02/10/2015

A                        Y      04/10/2015

A                        Y      05/10/2015

A                        Y      12/10/2015

A                        N      13/10/2015

A                        N      15/10/2015

A                        Y      16/10/2015

A                        Y      16/10/2015

A                        Y      17/10/2015

B                        N      01/12/2015

B                        N      02/12/2015

B                        Y      08/12/2015

B                        N      08/12/2015

B                        N      09/12/2015


what could be the SQL query to give me below resultL

ProductCode    Flag     StartDate     End Date
-------------------------------------------------------------------

A                         N       01/10/2015    02/10/2015

A                         Y       04/10/2015    12/10/2015

A                         N      13/10/2015    15/10/2015

A                         Y       16/10/2015    17/10/2015
B                        N       01/12/2015    02/12/2015

B                        Y       08/12/2015    08/12/2015
B                        N       08/12/2015    09/12/2015

Thanks.

vkp vkp
Answer

The key here is to assign groups to continuous N and Y flags for each product. Thereafter, it is just a grouping operation on the classified groups.

with grps as (    
 select t.*,
-row_number() over(partition by productcode,flag order by dt) 
+ row_number() over(partition by productcode order by dt) grp
from t
) 
select productcode,flag,min(dt) startdate,max(dt) enddate 
from grps
group by productcode,flag,grp
order by 1,3

Sample Demo