我有一个日期维度表,它为每个日期记录了大量字段,其中之一是一年中的两周。
我正在尝试编写一个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发布于 2020-07-30 13:16:06
尝试如下所示:
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])发布于 2020-07-30 13:30:35
尝尝这个。它假设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发布于 2020-07-31 06:56:47
感谢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_datehttps://stackoverflow.com/questions/63166094
复制相似问题