Solved

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

Posted on 2013-06-03
3
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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