Styjun

I am using SQL Server 2014 and I have the following T-SQL query:

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'

The output is as follows (extract):

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
... ...

I want to add a new column to the output as follows:

 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
 ... ... ...

To note that D1 starts again at the beginning of each new financial year (that is,2016-07-01, 2017-07-01 and 2018-07-01).

How do I write the SQL code for this new column?

Additional note: D1 should be continuous till the end of each financial year. Example, from 2016-07-01 till 2017-06-30, column Period will show D1, D2, ..., D365)

use 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

You could use DATEDIFF to get the difference in days between the start of the financial year & [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'

This method also means that the dates could be in any order or even have some days missing & the Day column should still be correct.

Here is an example how you can simplify your existing FY calculation, and get the day of financial year as well:

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];

The hard-coded constants that designate the offset between the calendar and financial year can also be parameterised, if you need it.

You can use DATEDIFF to calculate number of days in that Financial year. You just need an extra effort to get the Financial year first date for the DATE column.

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

Result:

+-------------------------+-----------+
| 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 |
+-------------------------+-----------+

I want to point out that you can express the query as:

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';

The period can also be defined as:

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