MSSQL Date Analysis

Question

I need to write a query where it looks at a plethora of dates and determines if that date was 3 or more years ago, 2 or more years ago, 1 or more year ago, 6 or more months ago, or less than 6 months ago. Is there a way to do this without writing in physical dates, so that the analysis can be run again later without needing to change the dates? I have not started to write the query yet, but I have been trying to map it out first.

Possible duplicate of [How to compare two dates to find time difference in SQL Server 2005, date manipulation](https://stackoverflow.com/questions/9521434/how-to-compare-two-dates-to-find-time-difference-in-sql-server-2005-date-manipu) — Tab Alleman, Aug 09 '17 at 13:04

score 1 · Answer 1 · answered Aug 09 '17 at 13:05

1

You should use case. I would recommend something like:

select (case when datecol < dateadd(year, -3, getdate()) as '3 years ago'
             when datecol < dateadd(year, -2, getdate()) as '2 years ago'
             . . .
        end)

I specifically do not recommend using datediff(). It is counterintuitive because it counts the number of "boundaries" between two dates. So, 2016-12-31 and 2017-01-01 are one year apart.

answered Aug 09 '17 at 13:05

Gordon Linoff

1,242,037
58
646
786

We was thinking in the same way but I filtered rows instead of "classifying" them. The only thing is wrong in your "classification" is that when datecol < dateadd(year, -3, getdate()) means MORE than 3 years ago, not exactly 3 years ago – sepupic Aug 09 '17 at 13:11

score 0 · Answer 2 · answered Aug 09 '17 at 12:58

You can use the DATEDIFF function to calculate the number of months, days, years, etc. between two dates, e.g.

select datediff(day, '2016-01-01', '2017-01-01')

returns 366, because 2016 was a leap year

To get the current date, use the GETDATE() function.

score 0 · Answer 3 · answered Aug 09 '17 at 13:38

I tend to use a generic Tier Table for several reasons.

Logic is moved from code.
Alternate Tiers may be deployed depending on your audience.
Most importantly, things change.

The following will generate a series of dates, and then summarize by the desired tier. I should add, this is a simplified example

Example

-- Create Sample Tier Table
Declare @Tier table (Tier_Group varchar(50),Tier_Seq int,Tier_Title varchar(50),Tier_R1 int,Tier_R2 int)
Insert into @Tier values
 ('MyAgeTier',1,'+3 Years'       ,36,999999)
,('MyAgeTier',2,'2 - 3 Years'    ,24,36)
,('MyAgeTier',3,'1 - 2 Years'    ,12,24)
,('MyAgeTier',4,'6 Mths - 1 Year',6 ,12)
,('MyAgeTier',5,'<6 Mths'        ,0 ,6)
,('MyAgeTier',6,'Total '         ,0 ,999999)

Select Tier_Title
      ,Dates   = count(*)
      ,MinDate = min(D)
      ,MaxDate = max(D)
 From  @Tier A
 Join (
        -- Your Actual Source
        Select Top (DateDiff(DAY,'2010-01-01','2017-07-31')+1) 
               D=cast(DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),'2010-01-01') as date)
         From  master..spt_values n1,master..spt_values n2
      ) B
 On Tier_Group = 'MyAgeTier' and DateDiff(MONTH,D,GetDate()) between Tier_R1 and Tier_R2-1
 Group By Tier_Title,Tier_R1
 Order by Tier_R1 Desc

Returns (this example)

MSSQL Date Analysis

3 Answers3