• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

How can I get this query to return the results I need?

I need to be able to determine a current Customer who has placed an order within the past year.  
The current customer is determined by a customer placing an order within the past 5 years as in the Current query below.  

Select Cust_IdA, Order_date
From TableA
Where Order_date >= DATEADD(Year,-5, GETDATE())

which returns:

Cust_IdA             Order_date
0x00001              2008-10-20
0x00002              2009-05-06
0x00003              2010-02-04
0x00004              2011-07-19
0x00005              2013-01-30

That a Cust_IdA list is created based on Order_dates within the 1 year of getdate(), which would be from 2012-06-3 to 2013-06-3, and that the Cust_IdA is in the Current Cust_IdA list which is 540 IDs.

Close to an example:

Cust_IdA             Order_date
0x00001              2012-10-20
0x00002              2013-05-06
0x00003              2013-02-04
0x00004              2012-07-19
0x00005              2013-01-30

The Cust_IdA list above  is based on
Order_date >= DATEADD(Year,-1, GETDATE())

Would something like this work?

Select Cust_IdA
From TableA
Where Order_date >= DATEADD(Year,-5,CONVERT (date, GETDATE()))
AND Order_Date >= DATEADD(Year,-1,CONVERT (date, GETDATE()))
  • 2
1 Solution
Anthony PerkinsCommented:
Which is exactly the same as this:
FROM    TableA
WHERE   Order_Date >= DATEADD(Year, -1, CONVERT (date, GETDATE()))

Open in new window

rhservanAuthor Commented:
Would there be any problem in using a 540 id's for my list in an IN .
Is there any best practices for this?
Anthony PerkinsCommented:
Your best bet is to place those 540 IDs in a well-indexed permanent or temporary table and then do a JOIN against that table.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now