Using DATEADD with certain condition

user_17346778 0
Viewed 124

I need to use the dateadd function to add a number of days found in one column to a certain timestamp found in another column.

select number_id,
DATEADD('days',NUM_DAY, MY_DATETIME) as LAST_DATETIME
FROM TBL1
GROUP BY 1

However, for each number_id, I might have more than one "num_day". For each number_id, I only want have one record, and the NUM_DAY should select the LAST num_day that was inserted based on last added_timestap.

Is there a way to do this?

snowflake-cloud-data-platform sql
2 Answers

FIRST_VALUE/LAST_VALUE is your friend here:

SELECT number_id,
    LAST_VALUE(num_day) OVER (PARTITION BY number_id ORDER BY added_timestap) AS last_num_day,
    DATEADD('days',last_num_day, my_datedtime) AS last_datetime
FROM table

But that will get the last value all rows past/future of this row, which might be what you want, but if you want between all past row and this row you will want this:

SELECT number_id,
    LAST_VALUE(num_day) OVER (PARTITION BY number_id ORDER BY added_timestap RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_num_day,
    DATEADD('days',last_num_day, my_datedtime) AS last_datetime
FROM table
user_439920

Add "last-edit"-timestamp to the Table "TBL1"

num_day last_edit number_id
42 01.12.2021 10:11:16.0 01
13 01.12.2021 11:11:16.0 01

... and get the result with a sub-select.

SELECT
  number_id,
  DATEADD('days',NUM_DAY, MY_DATETIME) as LAST_DATETIME
FROM TBL1
WHERE 
  number_id = '01' 
  AND last_edit in (
    SELECT
     max(last_edit)
    FROM 
      TBL1
    WHERE
      number_id = '01'
);
user_135974320
Related