首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >T-SQL前两周的最后日期,用于年份转换

问T-SQL前两周的最后日期,用于年份转换
EN

Stack Overflow用户
提问于 2020-07-30 12:14:57
回答 3查看 87关注 0票数 0

我有一个日期维度表,它为每个日期记录了大量字段,其中之一是一年中的两周。

我正在尝试编写一个T-SQL查询,根据当前日期(或传递给它的任何日期)返回前两周的最后日期和前两周的数字。

我可以得到当前的两周和最后的日期,我需要弄清楚的是如何获得前两周的结束日期,包括过渡到新的一年。

下面的代码可以返回一年中当前的两周和这两周的最后一天,我该如何修改它来返回之前的两周和这两周的结束日期,特别是如果日期是任何一年的1月2日?

代码语言:javascript
复制
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
EN

回答 3

Stack Overflow用户

发布于 2020-07-30 13:16:06

尝试如下所示:

代码语言:javascript
复制
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
EN

Stack Overflow用户

发布于 2020-07-30 13:30:35

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

代码语言:javascript
复制
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
EN

Stack Overflow用户

发布于 2020-07-31 06:56:47

感谢gotqn和K4M,因为它让我重新思考了我是如何去做的,但这两篇文章都没有起作用。

下面是返回所需数据的代码(我希望我标记正确了),在执行时将@rundate作为任意日期传递。

代码语言:javascript
复制
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
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/63166094

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档