在T-SQL中动态设置YTD

I want to check if a start date passed into a query is this year and if so, set the end date to look at this year's YTD. Else if the start date is last year, set the end date to look at last year's YTD.

我想检查一下查询的开始日期是否在今年,如果是,请设置结束日期以查看今年的年初至今。否则,如果开始日期是去年,则设置结束日期以查看去年的年初至今。

2 个解决方案

#1

Below script will give you YTD based on @StartDate.

下面的脚本将为您提供基于@StartDate的YTD。

DECLARE @StartDate date='01-Jan-2018'
DECLARE @YTD DATE 
DECLARE @CurrentDate DATE=GETDATE()
select DATEDIFF(YEAR,@StartDate,@CurrentDate)
IF DATEDIFF(YEAR,@StartDate,@CurrentDate)>=1
BEGIN
    SET @YTD = '31-DEC-'+ CAST(YEAR(@StartDate) AS VARCHAR(4))
END 
ELSE IF DATEDIFF(YEAR,@StartDate,@CurrentDate)=0
BEGIN 
    SET @YTD = GETDATE()--OR you can set this to 31st Dec like above
END

#2

@DateStart is passed to the query. You can create @DateEnd with CASE statement:

@DateStart传递给查询。您可以使用CASE语句创建@DateEnd:

-- Declare variables
DECLARE
    @DateStart DATETIME = '2018-08-17' -- passed to the query
    , @DateEnd DATETIME;

-- Populate variable @DateEnd
SELECT @DateEnd =
    CASE
        WHEN YEAR(@DateStart) = YEAR(GETDATE()) THEN CAST(GETDATE() AS DATE)
        ELSE CONVERT(DATETIME, CONCAT(CONVERT(CHAR(4), @DateStart, 120), '-12-31'))
    END;

-- Show variable @DateEnd
SELECT @DateEnd;

To include "today", you can add 1 day and filter with less than (<):

要包含“今天”,您可以添加1天并使用小于(<)的过滤器:

-- Populate variable @DateEnd
SELECT @DateEnd =
    CASE
        WHEN YEAR(@DateStart) = YEAR(GETDATE()) THEN DATEADD(DD, 1, CAST(GETDATE() AS DATE))
        ELSE DATEADD(DD, 1, CONVERT(DATETIME, CONCAT(CONVERT(CHAR(4), @DateStart, 120), '-12-31')))
    END;

-- Show variable @DateEnd
SELECT @DateEnd;

-- Use variable @DateEnd
SELECT
  ColumnName1
  , ColumnName2
  , ColumnName3
FROM TableName
WHERE DateTimeColumn < @DateEnd;

#1