I'm trying to find a way to auto fill an SQL table with another SQL table on the basis of 4 parameters (I will implement this query into a stored procedure in a logic app system).

I have a logic app who insert automatically rows in a SQL table (from cognitive service source)
We'll call it table1:

table1:

I want to fill this table1 with the column "signal" present in table2 (my cognitive service fill all the columns of table1 except "signal" - who appear NULL)

table2:

There are the 4 conditions to insert the data from table2.signal to table1.signal:

table1.brand = table2.brand  **AND**  
table1.category = table2.category **AND**  
table1.start_date ( /- 5 days range) = table2. start_date **AND**  
table1.end_date ( /- 5 days range)= table2. end_date 

Challenge: I need to do the union on the date time by finding the min absolute value in 5 days range (negative or positive). The date will very rarely match into a simple equal between table1 and table2. Table2 date are the source of true and some **** people decided to not respect these date by starting the signal in a range of /- 5 days in the table1

I started writing this code but I cannot see if my datetime range is possible:

UPDATE
   t1
SET
   t1.signal = t2.signal,
FROM
   table1 as t1,
   table2 as t2
WHERE
   t1.brand = t2.brand
   AND t1.category = t2.category
   AND t1.start_date = t2.start_date
   AND t1.end_date = t2.end_date

If everything works, then my table1 example should receive the signal "action 4" on line 1 (action 1 and 2 date are out of range, and action 3 have a bigger absolute value date difference than action 4)
And my table1 line 2 should receive the event action 2.

Many thanks by advance for those who'll find me a solution.

CodePudding user response：

You need to use DATEADD() command to get the range.

UPDATE
   t1
SET
   t1.signal = t2.signal,
FROM
   table1 as t1,
   table2 as t2
WHERE
   t1.brand = t2.brand
   AND t1.category = t2.category
   AND t2.start_date BETWEEN DATEADD(day ,-5 ,t1.start_date) AND DATEADD(day ,5 ,t1.start_date)
   AND t2.end_date BETWEEN DATEADD(day ,-5 ,t1.end_date) AND DATEADD(day ,5 ,t1.end_date)