恩~遇到一個不知道如何用純SQL寫法的當月排班班別區間

只能想到用T-SQL來達成寫法...

先給前置資料的SQL

declare @Shift table(
	Name nvarchar(20)
	,SetDate date
	,Code nvarchar(5)
)
insert into @Shift
values('小明','2024/10/1','A'),('小明','2024/10/2','A'),('小明','2024/10/3','A'),('小明','2024/10/4','A')
,('小明','2024/10/5','休'),('小明','2024/10/6','休'),('小明','2024/10/7','A'),('小明','2024/10/8','A')
,('小明','2024/10/9','A'),('小明','2024/10/10','休'),('小明','2024/10/11','A'),('小明','2024/10/12','休')
,('小明','2024/10/13','休'),('小明','2024/10/14','B'),('小明','2024/10/15','B'),('小明','2024/10/16','B')
,('小明','2024/10/17','B'),('小明','2024/10/18','B'),('小明','2024/10/19','休'),('小明','2024/10/20','休')
,('小明','2024/10/21','A'),('小明','2024/10/22','A'),('小明','2024/10/23','A'),('小明','2024/10/24','A')
,('小明','2024/10/25','A'),('小明','2024/10/26','休'),('小明','2024/10/27','休'),('小明','2024/10/28','A')
,('小明','2024/10/29','A'),('小明','2024/10/30','A'),('小明','2024/10/31','A')
,('大白','2024/10/1','特'),('大白','2024/10/2','特'),('大白','2024/10/3','B'),('大白','2024/10/4','B')
,('大白','2024/10/5','休'),('大白','2024/10/6','休'),('大白','2024/10/7','B'),('大白','2024/10/8','B')
,('大白','2024/10/9','B'),('大白','2024/10/10','休'),('大白','2024/10/11','B'),('大白','2024/10/12','休')
,('大白','2024/10/13','休'),('大白','2024/10/14','A'),('大白','2024/10/15','A'),('大白','2024/10/16','A')
,('大白','2024/10/17','A'),('大白','2024/10/18','A'),('大白','2024/10/19','休'),('大白','2024/10/20','休')
,('大白','2024/10/21','B'),('大白','2024/10/22','B'),('大白','2024/10/23','B'),('大白','2024/10/24','B')
,('大白','2024/10/25','B'),('大白','2024/10/26','休'),('大白','2024/10/27','休'),('大白','2024/10/28','B')
,('大白','2024/10/29','B'),('大白','2024/10/30','B'),('大白','2024/10/31','B')

--A代表 08-16 早班時段
--B代表 13-21 午班時段

--想要的結果： 
--大白 , B:1~13、A:14~20、B:21~31
--小明 , A:1~13、B:14~20、A:21~31

我用T-SQL後，得到此結果~

看有哪位高手比較厲害的SQL寫法...^^

我再分享我寫的T-SQL

好像我表達的不好~我貼出T-SQL好了~看如何改善成為純SQL

我也把直轉橫列出~比較看得懂..

select *
from (
	select Name
	,day(SetDate) DayNum
	,Code
	from @Shift
) k
pivot (
	max(Code)
	for DayNum in(
		[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
		,[11],[12],[13],[14],[15],[16],[17],[18]
		,[19],[20],[21],[22],[23],[24],[25],[26]
		,[27],[28],[29],[30],[31]
	)
) p

T-SQL語法的寫法....0.0

declare @ShiftMonth table(
	Name nvarchar(20)
	,MonthCode nvarchar(max)
)

declare @ShiftName table(
	Sort int
	,Name nvarchar(20)
)

declare @ShiftSet table(
	Name nvarchar(20)
	,SetDate date
	,Code nvarchar(5)
	,LastDate date
	,LastCode nvarchar(5)
)

declare @Count int = 0
declare @Code nvarchar(5) = ''
declare @LastCode nvarchar(5) = ''
declare @StartDate date,@EndDate date
declare @UpdStartDate date,@UpdEndDate date
declare @i int = 0,@n int=0
declare @MonthCode nvarchar(max) = ''
declare @MonthStart date = '2024/10/1'
declare @MonthEnd date = dateadd(day,-1,dateadd(month,1,@MonthStart))

insert into @ShiftName
select Row_Number()Over(order by Name) Sort
,Name
from @Shift
where SetDate between @MonthStart and @MonthEnd
group by Name

declare @NameCount int = isNull((select count(0) from @ShiftName),0)
declare @Name nvarchar(20)
declare @k int = 0

while(@k<@NameCount)
begin
	set @k = @k + 1
	select @Name=Name
	from @ShiftName
	where Sort = @k

	insert into @ShiftSet
	select *
	,isNull((
		select top 1 b.SetDate
		from @Shift b
		where a.Name = b.Name
		and Code not in('休','特')
		and a.SetDate < b.SetDate
	),a.SetDate) LastDay
	,isNull((
		select top 1 b.Code
		from @Shift b
		where a.Name = b.Name
		and Code not in('休','特')
		and a.SetDate < b.SetDate
	),a.Code) LastCode
	from @Shift a
	where Code not in('休','特')
	and Name = @Name

	set @i = 0
	set @n = 0
	set @Count = isNull((select count(0) from @ShiftSet),0)
	set @MonthCode = ''

	set @StartDate = @MonthStart

	while(@i<@Count)
	begin
		set @i = @i + 1

		if(@i=1)
		begin
			select top 1 @EndDate=LastDate
			,@Code=Code
			,@LastCode=LastCode
			from @ShiftSet
			order by SetDate
		end

		select @UpdStartDate=SetDate
		,@UpdEndDate=dateadd(day,-1,LastDate)
		,@Code=Code
		,@LastCode=LastCode
		from (
			select Row_Number()Over(order by SetDate) Sort
			,*
			from @ShiftSet
		) k
		where Sort = @i
		and Code<>LastCode

		if(@Code<>@LastCode)
		begin
			set @n = @n + 1
			set @EndDate = @UpdEndDate
			if(@MonthCode='')
			begin
				set @MonthCode = Convert(varchar,@Code) + ':' + Convert(varchar,Day(@StartDate)) + '~' + Convert(varchar,Day(@EndDate))
			end
			else
			begin
				set @MonthCode = @MonthCode + ',' + Convert(varchar,@Code) + ':' + Convert(varchar,Day(@StartDate)) + '~' + Convert(varchar,Day(@EndDate))
			end
			set @StartDate = dateadd(day,1,@EndDate)
			set @Code = @LastCode
		end
	end

	if(@Count>0)
	begin
		select @EndDate=max(SetDate)
		from @Shift
		where Name = @Name

		if(@MonthCode='')
		begin
			set @MonthCode = Convert(varchar,@Code) + ':' + Convert(varchar,Day(@StartDate)) + '~' + Convert(varchar,Day(@EndDate))
		end
		else
		begin
			set @MonthCode = @MonthCode + ',' + Convert(varchar,@Code) + ':' + Convert(varchar,Day(@StartDate)) + '~' + Convert(varchar,Day(@EndDate))
		end

		insert into @ShiftMonth 
		select @Name
		,@MonthCode

		delete from @ShiftSet
	end


end

select *
from @ShiftMonth