- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi everyone,
I looking at doing a left join with two tables. I have a data set 'X' that I want to left join to another data set where a trading group ID matches to data set 'Y' and the date modified from table 'X' is >= than start_date in table 'Y' and less than end_date in table 'Y' or end_date is blank.
I hope the above makes sense. Below is an attempt at a portion of the code to show you what I'm trying to do:
on a.trading_group_id = b.trading_group_id and a.date_modified >= b.start_date and a.date_modified < end_date or end_date = '';
Not to sure on how to do this. Maybe a where data step first to order by dates.
Any help is appreciated.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Then you can use the following, which is easier to follow IMO.
And date between start_date and end_date
One thing to make sure when you're dealing with multiple conditions is to enclose them with parentheses especially if you're mixing AND/OR logic.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Reeza that is a good tip.
Where should I be adding parentheses?
Please rewrite the code below with the parentheses:
on a.trading_group_id = b.trading_group_id and a.date_modified >= b.start_date and a.date_modified < b.end_date or end_date = '';
Thanks mate
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Scott86 wrote:
Please rewrite the code below with the parentheses:
I don't know what logic you want....you do 🙂
Make a small data set and test it out, which you should be doing anyways really.
You can work with subsets of your data by using the OBS= option.
option obs=1000;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is your END_DATE variable a character varaible? If so then how are you able to make a greater than or less than comparison between two values? If your variable is coded to look like 'YYYY-MM-DD' then relative order will be preserved when comparing character versions of dates, but if you are using 'MM-DD-YYYY' or 'DD-MM-YYYY' or even 'ddMONyyyy' format then the order as character strings will not match the order as dates.
I assume you want to group your comparisons in this way so that you do not include every record with a missing end_date. Note the MISSING() function will work with both numeric and character variables.
on (a.trading_group_id = b.trading_group_id)
and ( (a.date_modified >= b.start_date
and (a.date_modified < b.end_date or missing(end_date))
)
)
;
If your varaibles are actual date values then perhaps you can simplify using BETWEEN and COALESCE()? Use the -1 to make the upper bound comparison of the BETWEEN match the < used in your original query instead of <= that is normally used.
on a.trading_group_id = b.trading_group_id
and a.date_modified between b.start_date and coalesce(b.end_date,mdy(2099,1,1))-1