Solved

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

Posted on 2013-06-03
3
170 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now