Solved

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

Posted on 2013-06-03
3
175 Views
Last Modified: 2013-06-20
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.  

Current:
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


Desired:
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()))
0
Comment
Question by:rhservan
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39217670
Which is exactly the same as this:
SELECT  Cust_IdA
FROM    TableA
WHERE   Order_Date >= DATEADD(Year, -1, CONVERT (date, GETDATE()))

Open in new window

0
 

Author Comment

by:rhservan
ID: 39217692
Would there be any problem in using a 540 id's for my list in an IN .
Is there any best practices for this?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39217759
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.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question