sql - 如何从结束日期获取事件的开始日期和结束日期

Question

我有一个包含事件及其结束日期的表格，如下所示 -

Event_id    Event_name        Event_end_date
1           Name Change       31-Mar-2010       (prior to 31-Mar-2010)
2           Parent change     31-Oct-2010       (Logically its duration is 1-Apr-2010 (31-Mar-2010 + 1(Day) ) to 31-Oct-2010)
3           Name Change       31-Jul-2011       (Logically its duration is 1-Nov-2010 (31-Oct-2010 + 1(Day) ) to 31-Jul-2011)

所以我想写一个查询得到如下结果-

Event_id    Event_name     Event_start_date    Event_end_date
1           Name Change        -               31-Mar-2010
2           Parent change   1-Apr-2010         31-Oct-2010
3           Name Change     1-Nov-2010         31-Jul-2011

Answer 1

SELECT event_id, event_name,
   LAG (event_end_date ) OVER (ORDER BY event_end_date ASC )+1
                                                         event_start_date,
   event_end_date
 FROM event