Dynamic dates in SQL SQL query

Question

Thanks in advance for your help.

Let's say I have a query that compares data over years, starting from some arbitrary year and never ending (in the future), for the same period every year until the last completed month (which has the characteristic that Jan’s data is never before 1st of February). We also say that you can not use T-SQL. Is there a way to reformulate the following query to dynamically create dates starting from 2008/01/01 (or even just do it for all years) and work forever without any hard coding?

select case when oact.fathernum like '112%' then sum(jdt1.debit) - sum(jdt1.credit) end as [Accounts Receivable], jdt1.refdate as [Posting Date] from jdt1 inner join oact on jdt1.account = oact.AcctCode where (oact.fathernum like '1%') and (jdt1.refdate between '2008/01/01' and dateadd(day, -1, '2008/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2009/01/01' and dateadd(day, -1, '2009/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2010/01/01' and dateadd(day, -1, '2010/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2011/01/01' and dateadd(day, -1, '2011/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2012/01/01' and dateadd(day, -1, '2012/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2013/01/01' and dateadd(day, -1, '2013/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2014/01/01' and dateadd(day, -1, '2014/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2015/01/01' and dateadd(day, -1, '2015/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2016/01/01' and dateadd(day, -1, '2016/' + cast(month(getdate()) as varchar(2)) + '/01') or jdt1.refdate between '2017/01/01' and dateadd(day, -1, '2017/' + cast(month(getdate()) as varchar(2)) + '/01')) group by oact.fathernum, jdt1.refdate 

Otherwise, will anyone try their hand at reformulating using T-SQL in a stored procedure that solves the problem? The upper bound of the date can always be the current year, while it is dynamic.

Answer 1

TSQL below shows how to build a dynamic calendar table. The query, as shown, changes the date from each year, but the following shows how you can fix the calendar start date for a specific year.

 select case when oact.fathernum like '112%' then sum(jdt1.debit) - sum(jdt1.credit) end as [Accounts Receivable], jdt1.refdate as [Posting Date] from jdt1 inner join oact on jdt1.account = oact.AcctCode inner join (select FirstDayOfYear =DATEADD(m,datediff(m,0,getdate())-MONTH(getdate())+1,0), FirstDayOfMonth =DATEADD(m,datediff(m,0,getdate()),0)) D inner join master..spt_values v on v.type='P' and v.number between 0 and 500 -- is 500 years enough? max=2047 from this table on jdt1.refdate >= DATEADD(year,v.number,D.FirstDayOfYear) and jdt1.refdate < DATEADD(year,v.number,D.FirstDayOfMonth) where (oact.fathernum like '1%') group by oact.fathernum, jdt1.refdate 

The selection creates a single record of two rotation dates, called

 inner join (select FirstDayOfYear =DATEADD(m,datediff(m,0,getdate())-MONTH(getdate())+1,0), FirstDayOfMonth =DATEADD(m,datediff(m,0,getdate()),0)) D 

The two summary dates are the first day **current year** and the first day of the current month (also in the current year). If you need the first day of the year **specific** and the first day of the month (current month), but in the same specific year, you can use the option below (example 2008 - January-01)

 select FirstDayOfYear =cast('20080101' as datetime), FirstDayOfMonth =dateadd(m,month(getdate())-1,'20080101') 

It uses summary dates and a built-in sequence of numbers to gradually add 1 year each time to the dates of the summary, starting from adding 0 (for the current year).

 inner join master..spt_values v on v.type='P' and v.number between 0 and 500 on jdt1.refdate >= DATEADD(year,v.number,D.FirstDayOfYear) and jdt1.refdate < DATEADD(year,v.number,D.FirstDayOfMonth) 

Also note that instead

 date between A and B 

I usually prefer

 date >= A and date < B+1 

What works, regardless of whether B includes time information. This does not matter for your request, but is good practice for consistency.

Answer 2

Start with a number table to create and join dates

This SO question does this for a sequence of days

Answer 3

Would it be so like this job ?:

 YEAR(jdt1.refdate) between 2008 and 2017 and MONTH(jdt1.refdate) < MONTH(getdate()) 

Answer 4

If you are using SQL Server 2005+, you can simply create your calendar on the fly:

 With MaxDate As ( Select Max(refdate) As [Date] From jdt1 ) , Calendar As ( Select Cast( Cast(Year(GetDate())As char(4)) + '0101' As datetime ) As [StartDay] , DateAdd(d, -1, Cast( Cast(Year(GetDate()) + 1 As char(4)) + '0101' As datetime ) )As [EndDay] Union All Select DateAdd(yyyy, 1, [StartDay]) , DateAdd(yyyy, 1, [EndDay]) From Calendar Join MaxDate On Year(DateAdd(yyyy, 1, [EndDay])) <= Year(MaxDate.[Date]) ) Select ... From Calendar As C Join jdt1 On jdt1.refdate Between C.StartDay And C.EndDay Join oact On oact.AcctCode = jdt1.account Where oct.fathernum Like '%1' Group By oact.fathernum, jdt1.refdate Option ( MaxRecursion 0 ); 

In this decision, I started from this Year and expanded to the Year of the last refdate .