I am trying to separate out a clip if it has the same date. For example, since "Clip Number" 1 and 2 has the same date so I am trying to make the date different by adding a 6 hour interval. I would like to add a new field that is similar to the column "New Date"
| Date | Clip_Number | New Date |
|---|---|---|
| 1/2/2021 | 1 | 1/2/2021 0:00 |
| 1/2/2021 | 1 | 1/2/2021 0:00 |
| 1/2/2021 | 2 | 1/2/2021 6:00 |
| 1/2/2021 | 2 | 1/2/2021 6:00 |
| 1/3/2021 | 3 | 1/3/2021 0:00 |
| 1/3/2021 | 3 | 1/3/2021 0:00 |
Gordon solution works, but only if "there are only two values with the same date".
This solution works even in that case:
with data as (
select 1 clip, '2020-10-03' d
union all select 1, '2020-10-03'
union all select 1, '2020-10-03'
union all select 2, '2020-10-03'
union all select 2, '2020-10-03'
union all select 3, '2020-10-03'
union all select 3, '2020-10-04'
union all select 3, '2020-10-04'
)
select *
, timestampadd('hour'
, 6*(-1+dense_rank() over(partition by d order by clip))
, d
) new_date
from data
Assuming there are only two values with the same date, you could use:
select t.*,
(case when clip_number <> min(clip_number) over (partition by date)
then date + interval '6 hour'
else date
end)
from t;