CODEWITHSUNDEEP
sqlsql-servert-sqldate
James
James

Reputation: 91

Select records 1-2 years from begin date

I have two tables one that has a begin date, end date and age:

Begin_Date  End_Date            Age
2015-12-14 00:00:00.0000000 NULL 1
2015-12-10 19:00:00.0000000 NULL 1
2015-12-09 19:00:00.0000000 NULL 1
2015-12-07 00:00:00.0000000 NULL 1
2015-12-06 00:00:00.0000000 NULL 1
2015-12-06 00:00:00.0000000 NULL 1
2015-12-06 00:00:00.0000000 NULL 1
2015-12-05 00:00:00.0000000 NULL 1
2015-12-05 00:00:00.0000000 NULL 1
2015-12-05 00:00:00.0000000 NULL 1
2015-12-05 00:00:00.0000000 NULL 1
2014-12-12 00:00:00.0000000 NULL 1

The other table has associated locations for those records based on the uniqueID. The age column is the age when it was entered into the table.

I have a statement joining these two tables together but I am struggling to write the second half of this query. I want the query to pull records from the second table starting 1 year from the begin date in the first table.(i.e. I don't want locations from 2015-2016 but I DO want locations starting from 2016 to current date.) Right now I have it pulling records from the second table starting at the Begin Date but I can't figure out how to tell it to begin 1 year after the begin date. Thanks for your help.

What I have so far for the WHERE clause is this:

WHERE(points.DateYearAndJulian BETWEEN xref.Begin_Date AND COALESCE (xref.End_Date, GETDATE() + 1))

Upvotes: 3

Views: 126

Answers (1)

GMB
GMB

Reputation: 222392

In SQL Server, you can use dateadd() to add a given interval to a date, like:

dateadd(year, 1, Begin_Date)

If I followed you correctly, you would incorporate this in your query as follows:

WHERE points.DateYearAndJulian 
    BETWEEN dateadd(year, 1, xref.Begin_Date) 
    AND COALESCE (xref.End_Date, GETDATE() + 1)

Or if you want the number of years to add to be taken in the age column, then:

WHERE points.DateYearAndJulian 
    BETWEEN dateadd(year, xref.Age, xref.Begin_Date) 
    AND COALESCE (xref.End_Date, GETDATE() + 1))

Upvotes: 1

Related Questions