如何创建比使用sql的另一个另一个查询创建的下一个开始日期少一天的结束日期？

I queried off of a table that pulls in anyone who has working time percentage of less than 100 and all their working time records if they met the less than 100 criteria.

我查询了一张桌子,这张桌子可以吸引任何工作时间百分比小于100的人以及他们满足少于100个标准的所有工作时间记录。

This table contains the columns: id, eff_date (of working time percentage), and percentage. This table does not contain end_date.

此表包含以下列:id,eff_date(工作时间百分比)和百分比。此表不包含end_date。

Problem: how to build on top of the query below and add a new column called end_date that is one date less than the next eff_date?

问题:如何在下面的查询之上构建并添加一个名为end_date的新列,该列比下一个eff_date少一个日期?

Current query

select 
    j1.id, j1.eff_date,  j1.percentage
from 
    working_time_table j1
where 
    exists (select 1
            from working_time_table j2
            where j2.id = j1.id and j2.percentage < 100)

Data returned from the query above:

从上面的查询返回的数据:

ID | EFF_DATE| PERCENTAGE
------------------------
12 | 01-JUN-2012 |  70
12 | 03-MAR-2013 | 100
12 | 13-DEC-2014 |  85

The desired result set is:

期望的结果集是:

ID | EFF_DATE    | PERCENTAGE | END_DATE
-------------------------------------------
12 | 01-JUN-2012 |     70     | 02-MAR-2013
12 | 03-MAR-2013 |    100     | 12-DEC-2014
12 | 13-DEC-2014 |     85     | null

2 个解决方案

#1

You didn't state your DBMS so this is ANSI SQL using window functions:

您没有说明您的DBMS,所以这是使用窗口函数的ANSI SQL:

select j1.id, 
       j1.eff_date,
       j1.percentage, 
       lead(j1.eff_date) over (partition by j1.id order by j1.eff_date) - interval '1' day as end_date
from working_time_table j1
where exists (select 1
              from working_time_table j2
              where j2.id = j1.id and j2.percentage < 100);

#2

First off, curious if the "id" column is unique or it has duplicate values like the 12's in your sample, or is that a unique column or primary key possibly. It would be WAAAAY easier to do this if there was a unique id column that held the order. If you don't have a unique ID column, are you able to add one to the table? Again, would simplify this tremendously.

首先,好奇如果“id”列是唯一的,或者它具有重复值,如样本中的12,或者可能是唯一的列或主键。如果有一个唯一的id列保存订单,那么WAAAAY会更容易实现。如果您没有唯一的ID列,是否可以在表中添加一个?再次,将极大地简化这一点。

This took forever to get right, I hope this helps, burned many hours on it.
Props to Akhil for helping me finally get the query right. He is a true SQL genius.

这需要永远正确,我希望这会有所帮助,在此上花费了很多时间。向Akhil道具,帮助我最终获得正确的查询。他是一个真正的SQL天才。

Here is the ..

这里是 ..

SQLFIDDLE

SELECT 
    id, 
    firstTbl.eff_Date, 
    UPPER(DATE_FORMAT(DATE_SUB(
        STR_TO_DATE(secondTbl.eff_Date, '%d-%M-%Y'), 
            INTERVAL 1 DAY), '%d-%b-%Y')) todate, 
    percentage FROM 
    (SELECT 
        (@cnt := @cnt + 1) rownum, 
        id, eff_date, percentage 
     FROM working_time_table, 
     (SELECT 
         @cnt := 0) s) firstTbl
      LEFT JOIN 
          (SELECT 
              (@cnt1 := @cnt1 + 1) rownum, 
              eff_date 
           FROM working_time_table, 
               (SELECT 
                   @cnt1 := 0) s) secondTbl
                ON (firstTbl.rownum + 1) = secondTbl.rownum

#1