Cfrgtkky

I have two tables. The first table looks like this:

CompanyID  | TIMESTAMP           | NewsitemID 

---------------------------------------------    

ID1        | 2000-01-06 15:00:00 | 1       

ID2        | 2000-01-06 15:32:00 | 2

ID1        | 2000-01-07 14:00:00 | 3

ID3        | 2000-01-07 17:00:00 | 4

Now I want to update the timestamp if it falls inbetween two consecutive Closingtimes given in the second table. The new timestamp should be the second of the two consecutive entries as indicated by the corresponding SelectedTimestamp. This is the second table:

Closingtime         | SelectedTimestamp 

-----------------------------------------          

2000-01-05 16:00:00 | --

2000-01-06 16:00:00 | 2000-01-06 16:00:00

2000-01-07 16:00:00 | 2000-01-07 16:00:00

2000-01-10 16:00:00 | 2000-01-10 16:00:00

So, what I want to achieve is:

CompanyID  | TIMESTAMP           | NewsitemID | NewTimestamp

-------------------------------------------------------------------    

ID1        | 2000-01-06 15:00:00 | 1          | 2000-01-06 16:00:00    

ID2        | 2000-01-06 15:32:00 | 2          | 2000-01-06 16:00:00

ID1        | 2000-01-07 14:00:00 | 3          | 2000-01-07 16:00:00

ID3        | 2000-01-07 17:00:00 | 4          | 2000-01-10 16:00:00

But I am pretty new to Snowflake SQL and have problems even with starting the query. I have figured that I could probably use something in the direction of

SELECT SelectedTimestamp 

WHERE TIMESTAMP BETWEEN lower_bound AND upper_bound

but I have no clue how to "loop" through the lower and upper bounds given by the ClosingTime.

Any hint on how I could proceed here would be very much appreciated!

I'm not 100% sure what is the expected behavior you want, as the second table columns are identical.
Also, you didn't specify what "lower/upper bounds" mean for you, but I'll assume you mean two consecutive rows.

In either case, Snowflake actually has pretty rich support for subqueries. Hopefully this is what you need:

select first.*, 

(select min(selectedtimestamp) 

  from second where closingtime > first.timestamp) as newtimestamp 

from first;

-----------+---------------------+------------+---------------------+

 COMPANYID |      TIMESTAMP      | NEWSITEMID |    NEWTIMESTAMP     |

-----------+---------------------+------------+---------------------+

 id1       | 2000-01-06 15:00:00 | 1          | 2000-01-06 16:00:00 |

 id2       | 2000-01-06 15:32:00 | 2          | 2000-01-06 16:00:00 |

 id3       | 2000-01-07 14:00:00 | 3          | 2000-01-07 16:00:00 |

 id4       | 2000-01-07 17:00:00 | 4          | 2000-01-10 16:00:00 |

-----------+---------------------+------------+---------------------+