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
Solved

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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 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.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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