SQL Query for Separated Group By

Question

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.

Answer 1

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