Dynamic dates in SQL Server SQL statement

Thanks in advance for any help on this one.

Say I have a query that compares data across years, starting at some arbitrary year and never ending (going into the future), for the same period each year up to the last completed month (which has the characteristic that Jan data never shows until Feb 1). Say also, that one cannot use T-SQL. Is there a way to reformulate the following query to generate the dates dynamically starting from 2008/01/01 (or even just doing it for all years) and going forever without any hardcoding?

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(d开发者_如何转开发ay, -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

Failing that, any one care to try their hand at a reformulation using T-SQL in a stored procedure that solves the problem? The date upper bound could always be the current year as long as it is dynamic.

The TSQL below shows a method of building a dynamic calendar table. The query as shown changes the pivot date with each year, but further on is shown how you can fix the calendar 'start' date at a particular 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 select creates a single record of 2 pivot dates, as named

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

The 2 pivot dates are the first day of the **current year**, and the first day of the current month (also in the current year). If you need the first day of a **specific** year and the first day of month (current month) but in the same specific year, you can use the variation below (example for 2008-Jan-01)

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

This uses the pivot dates and the built-in number sequence to progressively add 1 year each time to the pivot dates, starting at adding 0 (for 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)

Notice also that instead of

date between A and B

I normally prefer

date >= A and date < B+1

Which works whether or not B includes time information. It doesn't matter for your query, but is good practice for consistence.

Start with a numbers table to generate datesets and join on it

This SO question does it for day sequences

Would somethign like this work?:

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

If you are using SQL Server 2005+, you can simply build 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 solution, I started with today's Year and expanded out to the Year of the last refdate.