SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

szyger
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 *
where:
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.

Thanks,
Simon

webfred
Flowing Fount of Yak Knowledge

Germany
8765 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

szyger
Starting Member

2 Posts

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

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
or
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?

Thanks,
Simon


Go to Top of Page

Robowski
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;
GO

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

--SELECT *
--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

--SELECT *
--FROM #CustomerB


SELECT *
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.06 seconds. Powered By: Snitz Forums 2000