My dataset looks like this and I need to generate the StartDate (Min), EndDate(Min) by grouping them by Name and Date columns. When Type changes, the group by logic should break and take Max date till there.

The output would be like:

CodePudding user response：

Hope this clarify you.

select Name,Type,min(date) as StartDate,max(date) as EndDate
from Table_Name
group by Type,Name

CodePudding user response：

The challenge in this case is to identify all target groups by the columns Name and Type taking into account the gaps. As a possible solution, you can use an additional grouping expression based on the difference between Row_Number ordered by Date and Row_Number ordered by Date with Partion by Name, Type.

With A As (
Select Name, [Type], [Date], 
       Row_Number() Over (Order by [Date]) As Num, 
       Row_Number() Over (Partition by Name, [Type] Order by [Date]) As Num_1
From Tbl)
Select Name, [Type], Min([Date]) As StartDate, Max([Date]) As EndDate
From A
Group by Name, [Type], Num - Num_1

dbfiddle

CodePudding user response：

Below approach would be bit clumsy yet fetches the desired output. The buckets are partitioned based on the date (day) difference.

declare @tbl table(name varchar(5),type varchar(5),[date] date)

insert into @tbl
values('A','xx','1/1/2018')
,('A','xx','1/2/2018')
,('A','yy','1/3/2018')
,('A','xx','1/4/2018')
,('A','xx','1/5/2018')
,('A','xx','1/6/2018')

select distinct name,type
,min(date)over(partition by name,type,diffmodified order by diffmodified) as [StartDate]
,max(date)over(partition by name,type,diffmodified order by diffmodified) as [EndDate]
from(
select *
,case when max(diff)over(partition by name,type order by [date]) > 1
then max(diff)over(partition by name,type order by [date]) else diff end as [diffmodified]
from(
select *, 
isnull(DATEDIFF(day, lag([date],1)
over(partition by name,type order by [date]), [date] ),1)[diff]
from
@tbl)
t)t