Fetching the data for thelast quarter dates

Database
KVB
Enthusiast

Fetching the data for thelast quarter dates

Hi

I am using TD13 version.I have a query which runs for on  particular dates.Whenever the query runs it has to pick up the last quarter dates.

How to extract last quarter dates in BETWEEN condition in TD without accessing calendar table.

6 REPLIES
Enthusiast

Re: Fetching the data for thelast quarter dates

Hi,

The discussion , with calendar or without calendar is in this link. You could have searched for it :). 

http://forums.teradata.com/forum/data-modeling/can-teradata-display-the-quarter-value-of-a-date.

Now for the date logic, you can write case when statement to suit your requirement.

Cheers,

Raja

Enthusiast

Re: Fetching the data for thelast quarter dates

Hi Bikky,

What if you try this?

WHERE COL_NAME BETWEEN CURRENT_DATE AND ADD_MONTHS(CURRENT_DATE,-3)
Khurram
KVB
Enthusiast

Re: Fetching the data for thelast quarter dates

Suppose let's take 1st quarter Jan-March.If my job runs in March.I need to pick the data from Oct1st to Dec31st.

Enthusiast

Re: Fetching the data for thelast quarter dates

An easier way is to use a scheduler. Maybe a stored proc too, since you can stuff your logic you want.

Cheers,

Raja

Junior Contributor

Re: Fetching the data for thelast quarter dates

What's your TD release?

In 14 there's Oracle's TRUNC:

WHERE datecol BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-3), 'Q'), TRUNC(CURRENT_DATE, 'Q') - 1

Before it's more complicated:

WHERE datecol BETWEEN ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) -1), -(EXTRACT(MONTH FROM CURRENT_DATE) + 2) MOD 3 - 3)
AND ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) -1), -(EXTRACT(MONTH FROM CURRENT_DATE) + 2) MOD 3) - 1
Enthusiast

Re: Fetching the data for thelast quarter dates

Whaw this is cool. So the TRUNC second paramater, 'Q' refers to Quarter?

Thanks,

Raja