CODEWITHSUNDEEP
sqlsql-server
TheSacredKiller
TheSacredKiller

Reputation: 141

Conditional SQL Query to fetch last date of the quarter on the basis of few condition

Basically a report is needed once a quarter, and the analysis date(any date) is the last date of the preceding quarter. So if we are loading on 10 July, “Analysis Date” would be 30 June (of the same year). HOWEVER - it is possible that the users will want to load the data early, in order to see what may need to be addressed by the end of the quarter. In that case they may want to load it on 20 June, and “Analysis Date” will still be 30 June.

How do i write the query for these two conditions, considering there will be only one month in advance for which this specific constraint is needed.

Upvotes: 0

Views: 526

Answers (1)

James Casey
James Casey

Reputation: 2507

This should put you on the right track. If you have a version of SQL Server older than 2012 you will need to use something instead of EOMONTH 

CREATE FUNCTION dbo.GetAnalysisDate(@date DATE)
RETURNS DATE
AS
BEGIN
    IF MONTH(@date) % 3 = 0 RETURN EOMONTH(@date)
    -- else
    RETURN DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @date), 0))
END
GO

-- tests
-- these should return 20181231
SELECT dbo.GetAnalysisDate('20190101')
SELECT dbo.GetAnalysisDate('20190120')
SELECT dbo.GetAnalysisDate('20190220')
-- these should return 20190331
SELECT dbo.GetAnalysisDate('20190320')
SELECT dbo.GetAnalysisDate('20190401')
SELECT dbo.GetAnalysisDate('20190420')

Upvotes: 1

Related Questions