As Andre pointed out your input data seems to be incorrect. I took the liberty of changing the modified time stamps for the last two records. Now to get the results you need, first assign a sequence number based on the time stamp. Descending order will help you identify the current schedule easily. You the need to wrap it in a CTE (common table expression) and join to itself on the sequence to get the previous record:
with cte as
(
select *, row_number() over(partition by scheduleid order by ModifiedTimeStamp desc) seq
from schedule
)
select curr.ScheduleID, curr.StartTime, curr.EndTime, curr.ModifiedTimeStamp,
case
when prev.seq is null then 'Schedule Created'
when curr.seq = 1 then 'Current Schedule'
else dbo.uf_GetModifiedMessage(curr.StartTime, prev.StartTime, curr.EndTime, prev.EndTime)
end as ActionType
from cte curr
left join cte prev on curr.seq = prev.seq - 1
order by curr.ModifiedTimeStamp
To get the modified message I would prefer a function. You could do it inline but the query would be a mess:
create function dbo.uf_GetModifiedMessage(
@currStartTime varchar(10),
@prevStartTime varchar(10),
@currEndTime varchar(10),
@prevEndTime varchar(10)
)
returns varchar(1000)
as
begin
declare @message varchar(1000) = NULL
if @currStartTime <> @prevStartTime
set @message = 'Start time modified from '+@prevStartTime+' to '+@currStartTime
if @currEndTime <> @prevEndTime
set @message = isnull(@message+', ','')+'End time modified from '+@prevEndTime+' to '+@currEndTime
return @message
end
I created a
fiddle[
^] too, check it out.