代码之家 › 专栏 › 技术社区 › user3115933

如何将这个新的自定义列添加到查询的输出中?

tsql sql-server sql

user3115933 · 技术社区 · 5 年前

我使用的是SQL Server 2014,我有以下T-SQL查询:

SELECT 
    [Date],
    (CASE
        WHEN [Date] BETWEEN '2016-07-01' AND '2017-06-30' THEN 'FY 16-17'
        WHEN [Date] BETWEEN '2017-07-01' AND '2018-06-30' THEN 'FY 17-18'
        WHEN [Date] BETWEEN '2018-07-01' AND '2019-06-30' THEN 'FY 18-19'
        ELSE 'Not Stated'
     END) AS [Period]
FROM 
    DateDimension
WHERE  
    [Date] BETWEEN '2016-07-01' AND '2019-06-30'

输出如下(摘录):

Date          Period
-----------------------
2016-07-01    FY 16-17
2016-07-02    FY 16-17
2016-07-03    FY 16-17
...           ...
2017-07-01    FY 17-18
2017-07-02    FY 17-18
2017-07-03    FY 17-18
...           ...
2018-07-01    FY 18-19
2018-07-02    FY 18-19
2018-07-03    FY 18-19
...           ...

我想将一个新列添加到输出中,如下所示:

    Date          Period       Day
    -------------------------------
    2016-07-01    FY 16-17     D1
    2016-07-02    FY 16-17     D2
    2016-07-03    FY 16-17     D3
    ...           ...          ...
    2017-07-01    FY 17-18     D1
    2017-07-02    FY 17-18     D2
    2017-07-03    FY 17-18     D3
    ...           ...          ...
    2018-07-01    FY 18-19     D1
    2018-07-02    FY 18-19     D2
    2018-07-03    FY 18-19     D3
    ...           ...          ...

注意到这一点 D1 在每个新的财政年度开始时重新开始(即, 2016-07-01 , 2017-07-01 和 2018-07-01 )

如何为这个新列编写SQL代码?

附加说明: D1 应持续到每个财政年度结束。示例,来自 2016年7月1日 直到 2017-06-30 ,列 Period 将显示d1、d2、…、d365)

2 回复 | 直到 5 年前

-1

Fahmi 5 年前

使用 row_number()

with cte as
(
 SELECT [Date]
,(CASE WHEN [Date] between '2016-07-01' and '2017-06-30' THEN 'FY 16-17'
      WHEN [Date] between '2017-07-01' and '2018-06-30' THEN 'FY 17-18'
      WHEN [Date] between '2018-07-01' and '2019-06-30' THEN 'FY 18-19'
      ELSE 'Not Stated'
      END) as [Period]
FROM DateDimension
)

select *,concat('D',row_number() over(partition by period order by date)) as DayNo
    from cte

ItsPete geeksensey 5 年前

你可以用 DATEDIFF 获取财政年度开始日期之间的天数差异& [Date] .

SELECT 

[Date]
,(CASE WHEN [Date] between '2016-07-01' and '2017-06-30' THEN 'FY 16-17'
  WHEN [Date] between '2017-07-01' and '2018-06-30' THEN 'FY 17-18'
  WHEN [Date] between '2018-07-01' and '2019-06-30' THEN 'FY 18-19'
  ELSE 'Not Stated'
  END) as [Period]
, CASE WHEN [Date] < DATEFROMPARTS(DATEPART(Year, GETDATE()), 7, 1)
       THEN CONCAT('D', (DATEDIFF(DAY, DATEFROMPARTS(DATEPART(Year, [Date]) - 1, 7, 1), [Date] + 1)))
       ELSE CONCAT('D', (DATEDIFF(DAY, DATEFROMPARTS(DATEPART(Year, [Date]), 7, 1), [Date] + 1)))
  END AS [Day]

FROM DateDimension

WHERE [Date] between '2016-07-01' and '2019-06-30'

此方法还意味着日期可以是任意顺序的,甚至可能缺少一些日期。 Day 列应该仍然正确。

Roger Wolf 5 年前

下面是一个示例,您可以简化现有的FY计算,并获取财政年度的日期:

declare @date date = '20190702';

select year(dateadd(month, -6, @date)) as [FY],
  datediff(day, datefromparts(year(dateadd(month, -6, @date)), 6, 30), @date) as [DOFY];

如果需要,也可以参数化指定日历和财政年度之间的抵销的硬编码常量。

Shakeer Mirza 5 年前

你可以用 DATEDIFF 计算该财政年度的天数。您只需要付出额外的努力来获得“日期”列的财政年度第一个日期。

DECLARE @DateDimension TABLE ([DATE] DATETIME)
INSERT INTO @DateDimension
SELECT '2019-03-25' 
UNION ALL 
SELECT '2018-12-06'
UNION ALL 
SELECT '2018-05-15'
UNION ALL 
SELECT '2017-11-22'
UNION ALL 
SELECT '2019-07-06'

SELECT  [DATE]
,'D'+CAST( DATEDIFF(DD, CASE WHEN MONTH([DATE]) BETWEEN 7 AND 12 
        THEN DATEFROMPARTS(YEAR([DATE]),07,01) 
        ELSE DATEFROMPARTS(YEAR([DATE])-1,07,01) END,[DATE])+1 
    AS VARCHAR(3)) AS DAY_IN_FY
FROM @DateDimension

结果:

+-------------------------+-----------+
|          DATE           | DAY_IN_FY |
+-------------------------+-----------+
| 2019-03-25 00:00:00.000 | D268      |
| 2018-12-06 00:00:00.000 | D159      |
| 2018-05-15 00:00:00.000 | D319      |
| 2017-11-22 00:00:00.000 | D145      |
| 2019-07-06 00:00:00.000 | D6        |
+-------------------------+-----------+

Gordon Linoff 5 年前

我想指出的是,您可以将查询表示为:

SELECT d.[Date], v.period,
       CONCAT('D', ROW_NUMBER() OVER (PARTITION BY period ORDER BY date)) as [Day]
FROM DateDimension dd CROSS APPLY
     (VALUES (RIGHT(DATENAME(year, d.[Date]), 2) + '-' +
              RIGHT(DATENAME(year, DATEADD(year, 1, d.[Date])), 2) 
             )
     ) as v(period)
WHERE [Date] BETWEEN '2016-07-01' AND '2019-06-30';

这个 period 也可以定义为:

     (VALUES (CONCAT(YEAR([Date] % 100, '-',
                     1 + YEAR([Date] % 100
                    )
             )
     ) as v(period)