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.
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 thiswhere dateadd(day,-4,b.date) >= a.dateif that isn't what you want then please give sample data and wanted result Too old to Rock'n'Roll too young to die. |
|
|
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/2013Transaction2 made on 15/05/2013Transaction3 made on 17/05/2013 - good sale transactionTransaction4 made on 26/05/2013Customer 2Transaction5 made on 10/07/2013Transaction6 made on 11/07/2013 - good sale transactionTransaction7 made on 18/07/2013 Transaction8 made on 31/09/2013Now 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 transactionorb) 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 |
|
|
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;GOIF OBJECT_ID ('#CustomerA', 'U') IS NOT NULL DROP TABLE #CustomerA;SELECT 1 as ID , CAST('15/05/2013' as DATE) as TranDateINTO #CustomerA--SELECT *--FROM #CustomerAIF OBJECT_ID ('#CustomerB', 'U') IS NOT NULL DROP TABLE #CustomerB;SELECT 1 as ID , CAST('18/05/2013' as DATE) as TranDateINTO #CustomerB--SELECT *--FROM #CustomerBSELECT *FROM #CustomerA as AJOIN #CustomerB as BON DATEDIFF(d, a.TranDate, B.TranDate) <= 4OR a.TranDate >= B.TranDate |
|
|
|
|
|
|
|