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.

11 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

Re: Fetching the data for thelast quarter dates

 

The below works perfectly when I am trying to pull data from the previous quarter, but how can I pull data from the previous quarter from the previous year?  I want to get data from Q1 2018 and Q1 2017 and each quarter from now will want to pull the previous quarter for the current and previous year...

 

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

Highlighted
Junior Contributor

Re: Fetching the data for thelast quarter dates

 

 

WHERE datecol BETWEEN Trunc(Add_Months(Current_Date, -3), 'Q')    -- start of previous quarter current year
                  AND Trunc(           Current_Date     , 'Q')-1  -- end of previous quarter current year
   OR datecol BETWEEN Trunc(Add_Months(Current_Date,-15), 'Q')    -- start of previous quarter previous year
                  AND Trunc(Add_Months(Current_Date,-12), 'Q')-1  -- end of previous quarter previous year

 

 

Re: Fetching the data for thelast quarter dates

I am getting a Syntax Error (Failed. 3707: Syntax error, expected something like ')' between ')' and ';'.) - I have included my WHERE clause below.  Any ideas why this is not working?

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 4267 StartFragment: 314 EndFragment: 4235 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

WHERE (((REFUND_PAYMENT.APPR_DT BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-3),'Q') /*START OF PREVIOUS QUARTER CURRENT YEAR*/ 
        AND TRUNC(CURRENT_DATE,'Q')-1 /*END OF PREVIOUS QUARTER CURRENT YEAR*/ 
    OR REFUND_PAYMENT.APPR_DT BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-15),'Q') /*START OF PREVIOUS QUARTER  PREVIOUS YEAR*/ 
        AND TRUNC(ADD_MONTHS(CURRENT_DATE,-12),'Q')-1 /*END OF PREVIOUS QUARTER  PREVIOUS YEAR*/ 
OR REFUND_PAYMENT.CONTRL_NBR NOT IN ('00037855', '00037859', '00037902', '00038655')))