Quarter Date Format -Urgent

[chidi]

It seems like I send a faq everyday but do not see appear on the forum.
Let me try again.
I need to know if someone can tell me how to write a quarter function.
Example, I have 2 tables, t1 and t2.
t1 has 3 columns - month_no as char, year_no as char
and a key called calendar_id.
t2 has two colums - amount and key calendar_id.
What I am trying to do is to determine the quarterly amount for each year.
I need my output to look like this:
period amount
1q98 200
2q98 300
3q98 0
4q01 50.
Like I said, it could be any year and any quarter.
I just the amount displayed for each quarter of a given year.
Can anyone please help me?
chidi

 

[alphonsus]

Try something like this (use your date separator):

select
quarter(date('01/'||month_no||'/'||year_no)),
year_no,
sum(amount)
from
t1 a,
t2 b
where
a.calendar_id = b.calendar_id
group by
quarter(date('01/'||month_no||'/'||year_no)),
year_no

HTH,
Alphonsus.

 

[chidi]

Thank you very much for responding.
What I really wanted is a code that would return quarter as
1q97, 2q98, or 4q99 or any given quarter for any given year.
At any given instance, I would like the code to return any
quarter that applies.
What you did for me could not accomplish that.
chidi

 

[pcawdron]

That's an unusual format, but you can get it if you concatenate

Try...

SELECT
TRIM(CHAR(QUARTER(MyTable.MyDate))) || 'q' || SUBSTR(CHAR(YEAR(MyTable.MyDate)),2,2)
FROM
MyTable