SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Issues with selecting date - please help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 02/28/2014 :  07:00:50  Show Profile  Reply with Quote
Hi there,

I have a bit of a problem with selecting data I need.

Let's say I have got a date x in table a and date y in table b.

What I want to select is everything in both tables, so select *
b.date y <= a.date x by 4 days (so withing 4 days of date x, not current date)
or b.date y >= a.date x (greater than indefinitely)

I have been trying to get this sorted and I cannot figure out the best way to do it.
Preferrably it would be done in a single statement, but if not possible then please let me know my options.


Flowing Fount of Yak Knowledge

8780 Posts

Posted - 02/28/2014 :  07:45:30  Show Profile  Visit webfred's Homepage  Reply with Quote
try this
where dateadd(day,-4,b.date) >= a.date

if that isn't what you want then please give sample data and wanted result

Too old to Rock'n'Roll too young to die.
Go to Top of Page

Starting Member

2 Posts

Posted - 02/28/2014 :  08:38:01  Show Profile  Reply with Quote

Sorry, that didnt work.

Not sure if I was clear about what I am trying to achieve, I will try on this example:

Customer 1
Transaction1 made on 13/05/2013
Transaction2 made on 15/05/2013
Transaction3 made on 17/05/2013 - good sale transaction
Transaction4 made on 26/05/2013

Customer 2
Transaction5 made on 10/07/2013
Transaction6 made on 11/07/2013 - good sale transaction
Transaction7 made on 18/07/2013
Transaction8 made on 31/09/2013

Now let's assume I have got a list of all good sale transactions and their date. (a.date)

I want to bring in all b.transaction dates where:
a) b.transaction was made up to 4 days before a.good sale transaction
b) b.transaction was made on the same day as good sale transaction or anyday after.

The problem I have is that a.good sale transaction date will be different, and never same as current date.
B.transactions will vary too.

I hope that make sense?


Go to Top of Page

Posting Yak Master

101 Posts

Posted - 02/28/2014 :  09:01:35  Show Profile  Reply with Quote
No sure how you are joining the tables but hopefully below will be enough to get you started:

USE Tempdb;

IF OBJECT_ID ('#CustomerA', 'U') IS NOT NULL
DROP TABLE #CustomerA;
SELECT 1 as ID ,
CAST('15/05/2013' as DATE) as TranDate
INTO #CustomerA

--FROM #CustomerA

IF OBJECT_ID ('#CustomerB', 'U') IS NOT NULL
DROP TABLE #CustomerB;
SELECT 1 as ID ,
CAST('18/05/2013' as DATE) as TranDate
INTO #CustomerB

--FROM #CustomerB

FROM #CustomerA as A
JOIN #CustomerB as B
ON DATEDIFF(d, a.TranDate, B.TranDate) <= 4
OR a.TranDate >= B.TranDate
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000