Dynamic dates in SQL Server SQL statement

Question

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

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.

Is this sql server 2005 or above? – RichardTheKiwi Jan 21 '11 at 01:46 — RichardTheKiwi, Jan 21 '11 at 01:46

score 2 · Answer 1 · edited May 23 '17 at 12:04

2

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

This SO question does it for day sequences

edited May 23 '17 at 12:04

Community

1
1

answered Jan 21 '11 at 01:24

gbn

422,506
82
585
676

Also: http://stackoverflow.com/questions/1478951/tsql-generate-a-resultset-of-incrementing-dates – OMG Ponies Jan 21 '11 at 01:26

RichardTheKiwi · Accepted Answer · 2011-01-22T04:48:03.937

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.

Impressive, thanks! Cool to see how you handle that problem, and yes, 500 years ought to be enough ; ) — m7d, Jan 21 '11 at 04:57
Sorry I got your nick wrong above, so used to seeing wiki instead of kiwi. — m7d, Jan 21 '11 at 05:22
@cyberkiwi - this is a neat solution and I can see a lot of uses for it, but is there a way to keep the year that it starts at from always marching forward with each new year? I really want from 2008 on. It compares the same period in each year correctly, I just haven't figured out how to modify it to fix the first year at 2008 and then compare every subsequent year from there. In other words the same period of data for all years starting at 2008 onward indefinitely must be preset simultaneously. e.g., Jan 1 - Jan 21 2008 alongside Jan 1 - Jan 21 2009, etc.. Any ideas? — m7d, Jan 22 '11 at 03:34
I figured out a solution. I turn your solution on its head and work backwords from a future date suitably far away and count down from there by reversing the sign of v.number in the where clause date addition. I chose 2099 which is more than suitable for this project. Thanks again. — m7d, Jan 22 '11 at 03:46
@m7d - answer updated for the requirement to keep the start year fixed — RichardTheKiwi, Jan 22 '11 at 04:48

score 1 · Answer 3 · answered Jan 21 '11 at 01:26

1

Would somethign like this work?:

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

answered Jan 21 '11 at 01:26

ypercubeᵀᴹ

113,259
19
174
235

that's terrible for index usage – RichardTheKiwi Jan 21 '11 at 01:30
Is it? Why? And more important, should it be terrible? – ypercubeᵀᴹ Jan 21 '11 at 01:55
because to figure out the first part, it would need to run YEAR() across `all` records to return the int value. A properly constructed date range to test against `jdt1.refdate` need only collect 1/12 of the data. – RichardTheKiwi Jan 21 '11 at 02:12

score 1 · Answer 4 · answered Jan 21 '11 at 02:33

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.

Shoots, SO won't let me award two answers. Oh well, this is also a nice solution. Thanks! — m7d, Jan 21 '11 at 04:59

Dynamic dates in SQL Server SQL statement

4 Answers4