Skip to content
Advertisement

How to get a count of records by minute using a datetime column

I have a table with columns below:

Customer Time_Start
A 01/20/2020 01:25:00
A 01/22/2020 14:15:00
A 01/20/2020 03:23:00
A 01/21/2020 20:37:00

I am trying to get a table that outputs a table by minute (including zeros) for a given day.

i.e.

Customer Time_Start Count
A 01/20/2020 00:01:00 5
A 01/20/2020 00:02:00 2
A 01/20/2020 00:03:00 0
A 01/20/2020 00:04:00 12

I would like to have it show only 1 day for 1 customer at a time.

Here is what I have so far:

Advertisement

Answer

In Teradata 16.20 this would be a simple task using the new time series aggregation

Before you must implement it similar to Ramin Faracov answer, create a list of all minutes first and then left join to it. But I prefer doing the count before joining:

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement