Calculate each hour from a month

Tools & Utilities
Fan

Calculate each hour from a month

Hi,

I want to calculate that, how many customer i gain from each hour a month.

For example between (20160401 00:00:00 and 20160401 01:00:00 i gain 5 customer)  

At the and of the month i want to show than frequency of the gained customer fır each hour.

How can i do that on teradata SQL assistant?

Thank you !

2 REPLIES
Junior Contributor

Re: Calculate each hour from a month

You can easily create all hours of a month using EXPAND ON, and then it's a Left Join using CONTAINS:

SELECT BEGIN(pd), COUNT(*)
FROM
(
SELECT pd
FROM sys_calendar.CALENDAR
WHERE calendar_date BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon') -- first day of previous month
AND LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1)) -- last day of previous month
EXPAND ON PERIOD (CAST(calendar_date AS TIMESTAMP(0)), CAST(calendar_date + 1 AS TIMESTAMP(0))) AS pd
BY INTERVAL '1' HOUR
) AS hours
LEFT JOIN your_table
ON pd CONTAINS your_timestamp_column
GROUP BY 1
Fan

Re: Calculate each hour from a month

Thank u for help :)