Trying to get results back from 12 month ago thru 18 months ago from todays date

Question

Trying to get results back from 12 month ago thru 18 months ago from todays date

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

Welcome aboard!

Rather than me explaining the things in the code that I demo, here are some references that do a better job than I could do. Code specific to your example follow these references.

References:

https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

https://erikdarlingdata.com/sargability-week-what-is-it-anyway/

Example Code:

--===== Demo of formulas.
 SELECT  MinDateInclusive = DATEADD(mm,-18,CONVERT(DATE,GETDATE()))
        ,MaxDateExclusive = DATEADD(mm,-12,CONVERT(DATE,GETDATE()+1))
;
--===== Simplified Example in a WHERE clause.
     -- Don't use BETWEEN for such things.
     -- Instead, make your code "Bullet Proof" using the "Closed/Open" method below.
 SELECT whatever
   FROM Polaris.CircItemRecords cir
   JOIN whatever ON whatever, etc
  WHERE cir.FirstAvailableDate >= DATEADD(mm,-18,CONVERT(DATE,GETDATE()))   --Inclusive Date (Closed)
    AND cir.FirstAvailableDate <  DATEADD(mm,-12,CONVERT(DATE,GETDATE()+1)) --Exclusive Date (Open)
    AND someothercriteria if needed/desired
;

And, get rid of the following in your code.

AND DATEDIFF(DAY, cir.FirstAvailableDate, GETDATE()) > 365

AND DATEDIFF(MONTH, cir.FirstAvailableDate, GETDATE()) <= 18

As a bit of a sidebar, never include a column in a formula in your WHERE clause. If you do, it will almost guarantee that your code will do a full table/index scan instead of a seek to the first value followed by an efficient range scan. This problem is frequently referred to simply as "Non-SARGable" code. "SARG" is an abbreviation for "Search ARGument". There are some exceptions when it comes to dates but even those exceptions are slower than doing it correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

Sorry... duplicate post deleted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scole Valued Member Points: 63 More actions · Answer 3

Wow. Thanks for being so informative and nice. You helped me out a lot. I really appreciate it!

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

And thank you for the kind feedback!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)