如何比较同一张表的2条记录？ [英] How to compare 2 records of same table?

问题描述

嗨专家，



我有一个表中的时间表相关数据如下。

Hi Experts,

I have data related to schedules in one of the table as following.

ScheduledID    StartTime  EndTime   ModifiedTimeStamp

1              10 AM      6 PM      01-01-2014 09:00
1              11 AM      6 PM      01-01-2014 09:30
1              11 AM      7 PM      01-01-2014 10:00
1              10 AM      6 PM      01-01-2014 10:30
1              11 AM      8 PM      01-01-2014 11:00

如上所述，计划开始和结束时间会多次修改。



我需要显示以下数据。

The schedule start and end time is modifies several times as above.

I need to display the above data as following.

ScheduledID    StartTime  EndTime   ModifiedTimeStamp   Action Type

1              10 AM      6 PM      01-01-2014 09:00    Schedule Created
1              11 AM      6 PM      01-01-2014 09:30    Start Time Modified from 10AM to 11AM
1              11 AM      7 PM      01-01-2014 10:00    End Time Modified from 6PM to 7PM
1              10 AM      6 PM      01-01-2014 10:30    Start Time Modified from 11AM to 10AM, End Time Modified from 7PM to 6PM
1              11 AM      8 PM      01-01-2014 11:00    Current Schedule

请指导我如何实现这个目标？



谢谢。

Please guide me how can i achieve this?

Thank you.

推荐答案

正如安德烈指出你的输入da ta似乎不正确。我冒昧地更改了最后两条记录的修改时间戳。现在，要获得所需的结果，首先根据时间戳分配序列号。降序将帮助您轻松识别当前的时间表。您需要将其包装在CTE（公用表表达式）中并在序列上连接到自己以获取先前的记录：

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

我创建了一个小提琴 [< a href =http://www.sqlfiddle.com/#!3/1035f/1/0target =_ blank> ^ ]，检查出来。

这篇关于如何比较同一张表的2条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！