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 !
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(*)
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