How to fetch only month end dates from SYS_CALENDAR from Current date

Database
Enthusiast

How to fetch only month end dates from SYS_CALENDAR from Current date

Hi,

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

3 REPLIES
Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

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

........

Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

I'm not sure if I am interpretting your scenario correctly...

CREATE MULTISET TABLE some_tbl
AS (SELECT calendar_date AS SNAP_DT
    FROM Sys_Calendar."CALENDAR"
    WHERE calendar_date BETWEEN DATE-400 AND DATE
) WITH DATA;

SELECT *
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?

Enthusiast

Re: How to fetch only month end dates from SYS_CALENDAR from Current date

Hi cmedved,

That's exactly the code I was looking out for.. The subquery inside the IN clause resolves my need...

Thank you very much !!