t-sql前两周的最后一个日期，以适应年度过渡

cunj1qz1 于 2021-07-27 发布在 Java

关注(0)|答案(3)|浏览(285)

我有一个日期维度表，它为每个日期记录了大量字段，其中一个字段是一年中的两周。
我正在尝试编写一个t-sql查询，根据当前日期（或传递给它的任何日期）返回前两周的最后一个日期和前两周的数字。
我可以得到目前的两周和最后一个日期好吧，我需要弄清楚的是如何得到前两周结束日期，包括过渡到新的一年。
我下面的代码用于返回一年中的当前两周和该两周的最后一个日期，我将如何修改此代码以返回任何日期的前两周和该两周的结束日期，特别是如果该日期是任何一年中的1月2日？

With
    Query1 As 
    (Select 
     Cast(GetDate() As Date) As Todays_Date)
Select
    Query1.Todays_Date,
    staging.dbo.manualdata_DateDimension.FortnightofYear,
    Max(staging.dbo.manualdata_DateDimension.Date) As Max_Date
From
    Query1 Inner Join
    staging.dbo.manualdata_DateDimension On staging.dbo.manualdata_DateDimension.Date = Query1.Todays_Date
Group By
    Query1.Todays_Date,
    staging.dbo.manualdata_DateDimension.FortnightofYear

sql sql-server tsql

来源：https://stackoverflow.com/questions/63166094/t-sql-last-date-of-previous-fortnight-catering-for-year-transition

3条答案

按热度按时间

jdg4fx2g1#

尝试以下操作：

WITH DataSource AS
(
    SELECT FortnightofYear
          ,Date
          ,GETDATE() AS [Todays_date]
          ,ROW_NUMBER() PARTITION BY (FortnightofYear ORDER BY Date DESC) AS [RowID]          
          ,LAG(Date) PARTITION BY (FortnightofYear ORDER BY Date DESC) AS [PreviosDate]
    FROM staging.dbo.manualdata_DateDimension
    WHERE Date <= GETDATE()
)
SELECT *
FROM DataSource 
WHERE ([RowID] = 1 AND Date = [Todays_date])
    OR ([RowID] = 2 AND [PreviosDate] = [Todays_date])

赞(0）回复(0）举报 2021-07-27

9cbw7uwe2#

试试这个。它假设 FortnightofYear 每年从1开始

DECLARE @DateToSearch datetime = GetDate() ---- set this to the date to be searched

; WITH cte AS (
    SELECT
        t.Year
        , t.FortnightofYear
        , FirstDate = MIN(t.Date)
        , LastDate  = MAX(t.Date)
    FROM 
        staging.dbo.manualdata_DateDimension t
    GROUP BY 
        t.Year
        , t.FortnightofYear
)
SELECT
    Todays_Date                            = @DateToSearch

    , Current_FortnightofYear              = FortnightofYear  
    , Current_FortnightofYear_First_Date   = FirstDate
    , Current_FortnightofYear_Last_Date    = LastDate

    --- if no previous records, values will be NULL
    , Previous_FortnightofYear             = LAG(FortnightofYear, 1, NULL) OVER (ORDER BY Year, FortnightofYear)
    , Previous_FortnightofYear_First_Date  = LAG(First_Date,      1, NULL) OVER (ORDER BY Year, FortnightofYear)
    , Previous_FortnightofYear_Last_Date   = LAG(Last_Date,       1, NULL) OVER (ORDER BY Year, FortnightofYear)
FROM 
    cte
WHERE
    @DateToSearch BETWEEN FirstDate AND LastDate

赞(0）回复(0）举报 2021-07-27

lnlaulya3#

多亏了gotqn和k4m，因为这让我重新思考我是怎么做的，但这两个帖子都没用。
下面是返回所需数据的代码（我希望我正确地标记了它），在执行时将@rundate作为任意日期传递。

With
    Query1 As (
     Select
         staging.dbo.manualdata_DateDimension.Year,
         staging.dbo.manualdata_DateDimension.FortnightofYear,
         Min(staging.dbo.manualdata_DateDimension.Date) As min_date,
         Max(staging.dbo.manualdata_DateDimension.Date) As max_date,
         Cast(DateAdd(Day, DateDiff(Day, 1, Min(staging.dbo.manualdata_DateDimension.Date)), 0) As date) As
         previous_fortnight_max_date
     From
         staging.dbo.manualdata_DateDimension
     Group By
         staging.dbo.manualdata_DateDimension.Year,
         staging.dbo.manualdata_DateDimension.FortnightofYear
     Having
         @rundate Between Min(staging.dbo.manualdata_DateDimension.Date) And
         Max(staging.dbo.manualdata_DateDimension.Date)
    )
Select
    Query1.Year,
    Query1.FortnightofYear,
    Query1.min_date,
    Query1.max_date,
    Query1.previous_fortnight_max_date,
    staging.dbo.manualdata_DateDimension.FortnightofYear As PreviousFortnightofYear
From
    Query1 Inner Join
    staging.dbo.manualdata_DateDimension On staging.dbo.manualdata_DateDimension.Date =
            Query1.previous_fortnight_max_date

赞(0）回复(0）举报 2021-07-27

我来回答

t-sql前两周的最后一个日期，以适应年度过渡

3条答案

相关问题

热门标签

最新问答