Table1
event1: Event 1 Name
event1_date: Date of Event
Table2
event2: Event 2 Name
event2_date: Date of Event
event1: Event 1 Name
I have tried several syntaxes found online, but none provide exactly what I need. In layman's terms, this is what I need:
If an event in Table2 took place within 7 days of any event in Table1, then add the event's name in the event1 field.
Edited for Comments:
What have I tried, and why has it not worked? I have been trying to get this to work for five days. I don't know how many different syntaxes I have tried. At 3AM this morning I deleted all of my notes in a bit of rage, so I am not able to provide a list of what I have tried.
Small sample table is provided below.
Table1
| event1 | event1_date |
|---|---|
| Event 1 | 1/1/2020 |
| Event 2 | 1/15/2020 |
| Event 3 | 2/1/2020 |
| Event 4 | 2/20/2020 |
Table2
| event2 | event2_date | event1 |
|---|---|---|
| Event 2.1 | 1/10/2020 | |
| Event 2.2 | 1/11/2020 | |
| Event 2.3 | 1/16/2020 | Event 2 |
| Event 2.4 | 2/25/2020 | Event 4 |
Seven days prior AND/OR seven days in the future of the event from table 1?
The dot notation in table 2, column event2 - is that needed and what needs to be done if there are more than one events within that time frame (another line, or build out event list in table 2, event 1 column?
**not at SQL box at the moment.... but this might help
SET NOCOUNT ON;
DECLARE @event2_date datetime
DECLARE @outtext nvarchar(100)
DECLARE @prior7 datetime
DECLARE @after7 datetime
DECLARE event2_cursor CURSOR FOR SELECT event2_date FROM table2;
OPEN event2_cursor
FETCH NEXT FROM event2_cursor INTO @event2_date
WHILE @@FETCH_STATUS = 0
BEGIN
set @prior7 = dateadd(DD, -7, @event2_date);
set @after7 = dateadd(DD, 7, @event2_date);
set @outtext = select event1_text from table1 where event1_date
BETWEEN @prior7 AND @after7
print @outtext
END
CLOSE event2_cursor;
DEALLOCATE event2_cursor;