Solved

How can I get the date column I need?

Posted on 2013-05-10
15
411 Views
Last Modified: 2013-05-13
current: (This works fine)
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE

Desired:
I also need to get date column which is within -30 days of date1

date2 is within date1 - 30days

So that date2 can be any date included in date1 -30 days range
0
Comment
Question by:rhservan
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39156812
date2 will be dateadd(dd, -30, date1)
0
 

Author Comment

by:rhservan
ID: 39156868
Just to clarify:

1. date2 is already its own column as a date. It lists dates beyond the date1 -30 date range.

2. So I need to filter the date2 column based on date1 -30 day range.

so can you write the formula so that it includes the existing date2 column?  Or, How can I apply the formula you sent.
0
 

Author Comment

by:rhservan
ID: 39156888
I am looking to have date2 column return only the dates in the date range of date1 -30days.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39156909
I am not quiet understood. Is this what you are looking

WHERE date1 >= DATEADD(dd, -30, @STARTDATE )
and date1 <= @StartDate
0
 

Author Comment

by:rhservan
ID: 39157027
date1 >= 01/01/2013 --@STARTDATE
 date1 <= 02/28/2013 --@ENDDATE

columns:
quote#          date1               date2
     1           01/01/2013      12/21/2012    <----This date is within -30 days of date1
     2           02/17/2013      01/25/2013    <----This date is within -30 days of date1
     3           02/08/2013      11/03/2012    <----This date is not within -30 days of date1
                                                                           I do not want to return this row.


I only want to return a row if the date2 column is within -30 days of date1
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39157042
WHERE date2 <= Dateadd(dd, 30, Date1 )

if you want to add to the existing where clause use this

WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 <= Dateadd(dd, 30, Date1 )
0
 

Author Comment

by:rhservan
ID: 39157084
Okay, I think you are getting close.
There is still a problem - It is returning up to 9 months difference.

Example:
     date1                              date2
10/15/2012                    01/27/2012    <------this should be within the range of date1 -30
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39157098
you can put another condition there
and date2 <= Dateadd(dd, 30, Date1 )
and date1 < date2
0
 

Author Comment

by:rhservan
ID: 39157106
But date1 should never be less than date2
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39157109
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and datediff(dd, date1, date2 ) <= 30
0
 

Author Comment

by:rhservan
ID: 39157138
Can't seem to make that work either.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157284
try this.
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 >= Dateadd(dd, -30, Date1 )

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39157624
I believe Sharath has identified an appropriate logic.

Do the Date1 or Date2 values also contain "time" or is "time" always at 00:00:00?
You may want to do this just in case:
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  Date1 ), 0) )

Open in new window

or you can use
cast(Date1 as date)

if you don't like this syntax:
dateadd(dd, datediff(dd,0,  Date1 ), 0)
0
 
LVL 31

Expert Comment

by:awking00
ID: 39158239
where dateadd(dd,30,date2) >= date1
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39158311
do you have it working? maybe you can try:

WHERE date1 BETWEEN @STARTDATE and @ENDDATE
and date2 BETWEEN Dateadd(dd, -30, Date1 ) and Date1
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

747 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

13 Experts available now in Live!

Get 1:1 Help Now