Create a list of dates and match records to the list

Question

I am trying to gather a set of MI which counts the number of 'events' for a day. As this is MI i also need to list the days which haven't got any 'events' listed. My query is as follows:

SELECT
    event_timestamp,
    COUNT(*)
FROM
    table1
GROUP BY
    event_timestamp
ORDER BY
    event_timestamp
;

Is there anyway this query can be modified to show all dates (not just the ones with events) and if possible show '0' for the dates with no events?

You'll probably need some kind of calendar help table, with all possible dates to solve this. (SQL can't make up data by itself.) — jarlh, Dec 30 '14 at 15:33
Unfortunately I don't have DBA access (to create the table) is there a way I can do that within a subquery? — Nic2352, Dec 30 '14 at 15:35
Alternatively, does teradata support loops outside of stored procedures? If it did I suppose I could do something along the lines of Current_Date - x (where x is greater than a specified date) — Nic2352, Dec 30 '14 at 15:37

score 4 · Accepted Answer · answered Dec 30 '14 at 17:00

4

There's a simple calendar in Teradata with public access rights:

SELECT
    c.calendar_date,
    COUNT(event_timestamp) -- need to count a column from the inner table
FROM sys_calendar.CALENDAR AS c
LEFT JOIN
    table1
ON c.calendar_date = event_timestamp -- assuming event_timestamp is a DATE, otherwise it's CAST(event_timestamp AS DATE)
WHERE c.calendar_date BETWEEN your-starting-date AND your-ending-date
GROUP BY
    c.calendar_date
ORDER BY
     c.calendar_date
;

answered Dec 30 '14 at 17:00

dnoeth

59,503
4
39
56

Perfect thank you! I had no idea that calendar existed! – Nic2352 Dec 31 '14 at 09:40
This seems to have partially worked. I am getting the list of c.calendar_date and joining the results with my table1. For whatever reason, as soon as I make the join all dates from c.calendar_date with no 'event' have been removed (even when I use a FULL OUTER JOIN). – Nic2352 Dec 31 '14 at 10:09
Any WHERE-condition on table1 will change the plan to an Inner Join. To add such a condition you must AND it to the ON-condition or do it in a Derived Table. – dnoeth Dec 31 '14 at 11:16
Thanks! One final question. If I join a third table on this query, I would have assumed the same applied above (i.e. put any 'where' clauses after the on condition) however that seems to have got rid of all of my '0' valued rows. Is there something I have done wrong? – Nic2352 Dec 31 '14 at 14:12
Any additional table must be LEFT JOINed and the WHERE-conditions must be moved to ON, too. – dnoeth Dec 31 '14 at 14:40
Not quite correct about the WHERE/ON restriction unless this is a quirk of Teradata. The ON clause forms the join criteria -- the condition(s) that must be met to perform the join. The WHERE clause forms the filtering criteria (which rows from either table to look at in the first place). If, for example, you were looking at only a particular type of event, then you could certainly add "and table1.event_type = 17" in the WHERE clause. You get the same result if you add it to the ON clause, but it is less efficient. Either way, it has no effect on the type of join. – TommCatt Dec 31 '14 at 23:18
@TommCatt: This is true for every DBMS supporting Standard SQL Outer Join syntax. After processing the join condition there will be NULLs for non-matching rows from the inner table. When the WHERE-condition is evaluated those rows are removed because `NULL = 17` results in UNKNOWN. Teradata's optimizer is smart enough to recognize this and automatically changes the join from Outer to Inner, which can be seen in Explain :-) There's a nice chapter in the TD manuals on this topic: http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch02.033.077.html – dnoeth Jan 01 '15 at 11:45
@TommCatt: Forgot to add that logically the join/ON-clause is processed before WHERE. Of course the optimizer normally tries to apply the WHERE-conditions first to restrict the number of rows for the join, but it still has to follow the logical order ON-WHERE-GROUP BY-HAVING-OLAP-QUALIFY-ORDER BY. – dnoeth Jan 01 '15 at 12:34
OK, I will admit that I made a small mistake in my example, but the overriding argument stands, *properly* adding left-join-table criteria to the WHERE clause does NOT change the left outer join to an inner join. Improperly adding such will change the result set to be the same as if you had performed an inner join, but that is quite different. Back to my example: if you left join T2 to T1, then you will have all rows in T1 whether or not there is a joinable row in T2 -- non-matching T2 rows contain NULLs. You may freely filter T1 rows in the WHERE clause. – TommCatt Jan 02 '15 at 19:45
CONT: You may also filter T2 rows in the WHERE clause (and should when it makes sense to do so) but you have to remember there is an implied "where T2.joining_field is null" already there from the left join. So to add further filtering criteria, you must make the implied part explicit: "where T2.joining_field is null or T2.event_type = 17". The point is, a left outer join will include rows omitted by just an inner join. But sometimes you can inadvertently filter out those extra rows, making your left outer result the same as the inner result.. – TommCatt Jan 02 '15 at 19:59
@TommCatt: now we completely agree :) Most people tend to forget about the NULLs. I just consider it easier to move the where-condition to ON instead of adding `or T2.joining_field is null` for every additional table (and maybe fiddling with brackets). – dnoeth Jan 02 '15 at 22:19
@dnoeth : you must have gained a lot of knowledge and sharing it with us , thanks for that. I would like to know are there other things like sys_calendar.CALENDAR that we can use , I have tried to search but didnt found any. – dev Jan 05 '15 at 13:16
@etl_devs: after 15+ years of Teradata you'll get the same knowledge :-) What "other things" do you look for? – dnoeth Jan 05 '15 at 15:20

Create a list of dates and match records to the list

1 Answers1