SQL View to Return the Accounting Period Starting and Ending Dates in Microsoft Dynamics 365 Business Central

While Microsoft Dynamics 365 Business Central is mainly used by clients in the cloud rather than on-premise, there are still some clients using the on-premise version. I was talking to one recently who was looking to create some transactional reports and needed to get the start and end dates for the accounting periods. For some clients, who use calendar months, this would be easy, but in the UK many companies use a 5-5-4 cycle of months starting from the first Monday of April.

When I did some exploring of the database, I found that the Accounting Period table holds the start date but not the end date of periods. SO what I needed to do was to calculate the end date based on the start date if the next period.

The below SQL view returns the start and end dates of each period:

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GetAccountingPeriodDates', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetAccountingPeriodDates
GO
-- create view
CREATE VIEW uv_AZRCRV_GetAccountingPeriodDates AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
WITH AccountingPeriods AS
	(
		SELECT
			[Starting Date]
			,[Name]
			,ROW_NUMBER() OVER (ORDER BY [Starting Date] ASC) AS [Line Number]
		FROM
			[CRONUS UK Ltd_$Accounting Period$437dbf0e-84ff-417a-965d-ed2bb9650972]
	)

SELECT
	[Starting Date].[Starting Date]
	,DATEADD( DAY, -1, [Ending Date].[Starting Date] ) AS [Ending Date]
	,[Starting Date].[Name]
	,[Starting Date].[Line Number]
FROM
	AccountingPeriods AS [Starting Date]
LEFT JOIN
	AccountingPeriods AS [Ending Date]
		ON
			[Ending Date].[Line Number] = [Starting Date].[Line Number] + 1
GO

GRANT SELECT ON uv_AZRCRV_GetAccountingPeriodDates TO [Reporting Users]
GO