CODEWITHSUNDEEP
google-bigquery
GF.Otaviano
GF.Otaviano

Reputation: 33

How to repeat rows of a table according to date gaps of each record?

I have a package journey history according to the recorded dates, however this table has gaps between the recorded dates. I would like to complete these gaps when the difference between the current date and the next date is greater than 1 day.

in the first ID_SHP the fact described above happens twice. Let's see:

ID_SHP EVENT_DATE STATUS
1000 2022-10-31T15:26:02 a_date_created
1000 2022-10-31T15:29:40 b_date_buff
1000 2022-11-01T03:10:03 c_date_hand
1000 2022-11-01T03:10:03 d_date_inv
1000 2022-11-01T06:18:28 e_date_rea_print
1000 2022-11-01T06:18:54 f_date_pri
1000 2022-11-01T11:49:56 h_date_dro
1000 2022-11-03T13:15:01 i_date_pic

In the example above, we should have 3 more records. 1 between 10/31 and 11/01 and another between 11/01 and 11/03.

So, it should be correct:

ID_SHP EVENT_DATE STATUS
1000 2022-10-31T15:26:02 a_date_created
1000 2022-10-31T15:29:40 b_date_buff
1000 2022-10-01T00:00:00 b_date_buff
1000 2022-11-01T03:10:03 c_date_hand
1000 2022-11-01T03:10:03 d_date_inv
1000 2022-11-01T06:18:28 e_date_rea_print
1000 2022-11-01T06:18:54 f_date_pri
1000 2022-11-01T11:49:56 h_date_dro
1000 2022-11-02T00:00:00 h_date_dro
1000 2022-11-03T00:00:00 h_date_dro
1000 2022-11-03T13:15:01 i_date_pic

Code for table: `

WITH table AS
 (SELECT '10000' as SHP_ID,
  '2022-10-31T15:26:02' as Event_date,
  'a_date_created' as Status
  UNION ALL SELECT '10000', DATETIME '2022-10-31T15:29:40', 'b_date_buff'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T03:10:03', 'c_date_hand'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T03:10:03', 'd_date_inv'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T06:18:28', 'e_date_rea_print'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T06:18:54', 'f_date_pri'
  UNION ALL SELECT '10000', DATETIME '2022-11-01T11:49:56', 'h_date_dro'
  UNION ALL SELECT '10000', DATETIME '2022-11-03T13:15:01', 'i_date_pic'
  UNION ALL SELECT '20000', DATETIME '2022-12-16T14:52:55', 'a_date_created'
  UNION ALL SELECT '20000', DATETIME '2022-12-19T11:10:23', 'g_date_rea_pickup'
  UNION ALL SELECT '20000', DATETIME '2022-12-20T10:58:25', 'i_date_pic'
  )

SELECT *
FROM table

`

I tried, through the lag function, to find out what the next date was and to make the difference between them, but not from this point on, I can't repeat the dates I need as in the previous example

Upvotes: 1

Views: 76

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Use below approach

select * except(next_date, day) replace(
  if(date(event_date) = day, event_date, datetime(day)) as event_date)
from (
  select *, ifnull(lead(event_date) over(partition by shp_id order by event_date), event_date) next_date
  from table
), unnest(generate_date_array(date(event_date), date(next_date))) day
order by event_date

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions