I'm new to this Forum and have been looking around for logic to fetch only month-end dates from SYS_CALENDAR.CALENDAR table.
Here's the scenario:
I've a query where in I need to fetch records for a specific period from a table with huge volume of records. This table has a field called SNAP_DT which denotes the business date.
The current query that is being used is coded in such a way where SNAP_DT IN clause is used and this part of the query is updated to include the previous month-end date (every time before execution)
I would like to modify the query in such a way that this SNAP_DT field is automatically passed with (say) the last x month-end dates (calculated from current date) [For e.g. let x = 6] for fetching records. I would want to code logic wherein the month-end dates are fetched from the SYS_CALENDAR.CALENDAR table from the current date and is passed to this SNAP_DT field via a subquery.
Can someone help me coding logic for my requirement? I use Teradata 13.11
Will this be helpful for you?
select add_months(current_date -extract(day from current_date) +1,1) -1,
add_months(current_date -extract(day from current_date) +1,-1) -1,
add_months(current_date -extract(day from current_date) +1,-2) -1
I'm not sure if I am interpretting your scenario correctly...
CREATE MULTISET TABLE some_tbl
AS (SELECT calendar_date AS SNAP_DT
WHERE calendar_date BETWEEN DATE-400 AND DATE
) WITH DATA;
FROM some_tbl tbl
WHERE tbl.SNAP_DT IN (
SELECT calendar_date-1 AS calendar_date FROM Sys_Calendar."CALENDAR"
WHERE day_of_month = 1 AND month_of_calendar-1
BETWEEN MONTH_OF_CALENDAR(CURRENT_DATE)-6 AND MONTH_OF_CALENDAR(CURRENT_DATE)
There are date functions in Teradata (I'm using 13.10) which are very useful for readability. The subquery in the "IN" clauses returns all of the month-end dates. The catch is, I'm not sure how you are planning to implement the "last x months" part. I'm assuming you're okay with changing the "6" in your code?
That's exactly the code I was looking out for.. The subquery inside the IN clause resolves my need...
Thank you very much !!