I'm trying to set up a report that will run monthly and return data from the prior month. I found a lot of information about how to do this but the only way I can get any data returned is to manually enter the dates. I'm new to Teradata. I appreciate your help!
The commented out section is one of the other things I tried.
SELECT ADD_MONTHS((DATE - EXTRACT(DAY FROM DATE)+1), -1) report_date, b.col1,
SUM (a.col3) out_adh
FROM view.dim_a_a a
JOIN view.b_b b
BETWEEN ADD_MONTHS (CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1), -1) AND CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)
/* ADD_MONTHS(CURRENT_DATE-1 -EXTRACT(DAY FROM CURRENT_DATE-1) +1 ,-1)
AND CURRENT_DATE-1 -EXTRACT(DAY FROM CURRENT_DATE-1)*/
GROUP BY 1, b.col1
ORDER BY b.col1
The explain plan shows the date filter is correct. If I copy it from the explain plan and run it, the first part returns dates but the second part doesn't return any rows.
Unless you run it on the first day of a month both BETWEEN should return the same result.
Did you double check if both Explains are the same?
What's your TD release, 1160301 instead of DATE '2016-03-01' looks strange.
This is the first part of your filter:
(view_tables.a_a IN VIEW view.a_a.date_ck >= 1160301)->returns dates
and this is the second one:
(view_tables.a_a in view view.a_a.date_ck <= 1160331)->doesn't return any rows
Then the dates returned in the first filter must be greater than 31 march 2016 and there's no rows in march 2016.
So I think this must be a data problem no a technical problem.
Thanks for the responses. The TD Version is 14.10.0.04 and database version is 14.10.06.07.
Dieter, I don't know what you mean by both Explains. I hit f6 and it shows me one answerset.
AtardecerR0j0 and yuvaevergreen - When I try to replace current_date with actual dates in the query, I get 0 rows returned.
If I run the query like this: (a.date_ck between '20160301' and '20160331'), I get all the data from the prior month.
One expects a DATE when a column is named DATE, so it's important infomation when it's an INT instead.
Currently you're comparing DATEs and INTEGERs and they simply don't match.
In Explan you could see that 2016-01-31 is 1160301 as INT and not 20160331.
Teradata stores DATEs using following formula:
(year - 1900) * 1000
+ month * 100
BETWEEN CAST(ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1), -1) AS INT) + 19000000
AND CAST(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) AS INT) + 19000000
That's the penalty when a date is not stored as a date, you can't use existing date calculations :)
If "(a.date_ck between '20160301' and '20160331')" works as written and date_chk is integer, then the dates are not Teradata date storage and the 1160331 form will not compare correctly. You will have to do integer arithmetic to create the integer 20160301. Also, comparing to character strings adds overhead and possibly removes good query plans when comparing to integers.