Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Issues with selecting date - please help

Author  Topic 

szyger
Starting Member

2 Posts

Posted - 2014-02-28 : 07:00:50
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-02-28 : 07:45:30
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 - 2014-02-28 : 08:38:01
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 - 2014-02-28 : 09:01:35
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
   

- Advertisement -