All Forums Database
KVB 124 posts Joined 09/12
26 Dec 2013
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.

Raja_KT 1246 posts Joined 07/09
26 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
26 Dec 2013

Hi Bikky,
What if you try this?

WHERE COL_NAME BETWEEN CURRENT_DATE AND ADD_MONTHS(CURRENT_DATE,-3)

 

Khurram

KVB 124 posts Joined 09/12
26 Dec 2013

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

Raja_KT 1246 posts Joined 07/09
27 Dec 2013

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

Cheers,

Raja

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
27 Dec 2013

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

 

Dieter

Raja_KT 1246 posts Joined 07/09
27 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.