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

scole

Valued Member

Points: 63
More actions
February 11, 2023 at 9:32 pm

#4148544

So today's date is 2/11/2022 so my results should be from 2/11/2022 and 8/11/2021.
SELECT
oi.Name as ItemAssignedBranch,
col.Name as CollectionName,
cir.MaterialTypeID,
cir.Barcode as ItemBarcode,
ird.CallNumber,
br.BrowseAuthor,
br.BrowseTitle,
cir.FirstAvailableDate,
cir.LifetimeCircCount as ItemLifetimeCircCount
FROM
Polaris.CircItemRecords cir
inner join Polaris.ItemRecordDetails ird on (cir.ItemRecordID = ird.ItemRecordID)
left join Polaris.Collections col on (cir.AssignedCollectionID = col.CollectionID)
inner join Polaris.BibliographicRecords br on (cir.AssociatedBibRecordID = br.BibliographicRecordID)
inner join Polaris.Organizations oi on (cir.AssignedBranchID = oi.OrganizationID)
WHERE
cir.AssignedBranchID in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,49,50)
and cir.LifetimeCircCount <= 1
and cir.RecordStatusID in (1)
and cir.ItemStatusID in (1,2,3,4,5,6,12,13,15,17,18,19)
AND DATEDIFF(DAY, cir.FirstAvailableDate, GETDATE()) > 365
AND DATEDIFF(MONTH, cir.FirstAvailableDate, GETDATE()) <= 18

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

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

Jeff Moden SSC Guru Points: 1004340 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: 1004340 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: 1004340 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)