时间戳

首页 > 解决方案 > 从每个名称的当前日期获取最后 5 个日期

sql - 从每个名称的当前日期获取最后 5 个日期

问题描述

嗨，你能帮我查询以下输出吗？我需要每个名称和每个日期的最后 5 个活动天数。

输入

Name date          Active
==== ====         ========
N1   07-30-2018      Y
N1   07-31-2018      N
N1   08-01-2018      Y
N1   08-02-2018      Y

输出

Name date       date2        Active
==== ========== =======      ======
N1   07-30-2018 07-25-2018    Y
N1   07-30-2018 07-26-2018    Y
N1   07-30-2018 07-28-2018    Y
N1   07-30-2018 07-29-2018    Y
N1   07-30-2018 07-30-2018    Y
N1   08-01-2018 07-27-2018    Y
N1   08-01-2018 07-28-2018    Y
N1   08-01-2018 07-29-2018    Y
N1   08-01-2018 07-30-2018    Y 
N1   08-01-2018 08-01-2018    Y
N1   08-02-2018 07-28-2018    Y
N1   08-02-2018 07-29-2018    Y
N1   08-02-2018 07-30-2018    Y
N1   08-02-2018 08-01-2018    Y
N1   08-02-2018 08-02-2018    Y

我正在使用以下查询来获取此输出，请您更正一下

DECLARE @windowStart DATETIME SET @windowStart = '2016-08-01 00:00:00' 
DECLARE @windowEnd DATETIME SET @windowEnd = '2016-09-01 23:00:00'; 

Select a.Wellname,cast(a.timestamp as date) as date1,b.timestamp    --,date2 = dateadd(DAY,-N,timestamp) 
From  TableA --where cast(timestamp as date)>=@windowStart and cast(timestamp as date)<= @windowEnd 
Cross Join (
    Select * 
    from (
        Select distinct m.wellname, cast(m.[timestamp] as date) as timestamp ,Rank() Over ( partition by  m.wellname order by timestamp desc) as Rank 
        from TableA m  
        where m.updown<>'D'  and cast(m.[timestamp] as date) <= (Select cast(T.timestamp as date) from dbo.odr_nd_well_data T where cast(T.timestamp as date)=cast(a.timestamp as date)) and m.wellname in ('WN1','WN2')
        group by m.wellname,m.timestamp
    ) a where a.rank<=5
) B
 --on a.wellname=b.wellname 
where a.wellname='WN1' and cast(a.timestamp as date)>=@windowStart and cast(a.timestamp as date)<= @windowEnd
Order by a.wellname,a.date1,cast(b.timestamp as date)

标签： sqlsql-server

解决方案

也许一个CROSS JOIN带有临时计数表的

例子

Select A.*
      ,date2 = dateadd(DAY,-N,date)
 From  YourTable A
 Cross Join (Select Top 5 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1) B
 Order By name,date,dateadd(DAY,-N,date)

退货

Name    date        date2
N1      2018-08-01  2018-07-27
N1      2018-08-01  2018-07-28
N1      2018-08-01  2018-07-29
N1      2018-08-01  2018-07-30
N1      2018-08-01  2018-07-31
N1      2018-08-02  2018-07-28
N1      2018-08-02  2018-07-29
N1      2018-08-02  2018-07-30
N1      2018-08-02  2018-07-31
N1      2018-08-02  2018-08-01

推荐阅读