OraFAQ Forum: SQL & PL/SQL » how to get the QUARTERLY data

Home » SQL & PL/SQL » SQL & PL/SQL » how to get the QUARTERLY data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get the QUARTERLY data [message #248166]

Thu, 28 June 2007 03:55

msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR

Member

hi,
i hav the following query, from which is it possible to get the quarterly results.....
ie.
Q1:Jan-Mar
Q2:Apr-Jun
Q3:Jul-Sept
Q4:Oct-Dec

select 	a.name,
	a.actno,
	a.countrycd,
	a.curcd,
	a.amtfin,
	a.settamt
from 	ifmr a,
	ifschd b,
	globtab c
where 	actcls IN ('2','3') and
	(to_char(to_date(b.txday,'YYYYMMDD'),'YYYY') = to_char(to_date(c.globtab_tbsdy,'YYYYMMDD'),'YYYY')) 
	order by a.prodcd;

EX:
    Q1:
       name    actno       countrycd    curcd    amtfin        settamt
       ILIC 00062000001911    KW          KWD    1000000.00    100000.00
    Q2:
       name    actno       countrycd    curcd    amtfin    settamt
       ILIC 00062000001911    KW          KWD    1000000.00    100000.00
    Q3:
       name    actno       countrycd    curcd    amtfin    settamt
       ILIC 00062000001911    KW          KWD    1000000.00    100000.00
    Q4:
       name    actno        countrycd    curcd    amtfin    settamt
       ILIC 00062000001911    KW          KWD    1000000.00    100000.00

thx,
Ajay.

[Updated on: Thu, 28 June 2007 03:56]

Report message to a moderator

Re: how to get the QUARTERLY data [message #248169 is a reply to message #248166]

Thu, 28 June 2007 03:57

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select sysdate today, to_char(sysdate,'Q') quarter, 
  2         trunc(sysdate,'Q') first_quarter_day
  3  from dual
  4  /
TODAY      Q FIRST_QUAR
---------- - ----------
28/06/2007 2 01/04/2007

1 row selected.

Regards
Michel

Report message to a moderator

Re: how to get the QUARTERLY data [message #248205 is a reply to message #248169]

Thu, 28 June 2007 04:50

msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR

Member

hi,

Then how to get the first quarter data.
Thx,
Ajay

Report message to a moderator

Re: how to get the QUARTERLY data [message #248213 is a reply to message #248205]

Thu, 28 June 2007 05:21

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Ajay,
Michel has showed how you can transform a date into the corresponding quarter. If you use that transformation in a WHERE clause, you can limit the results of your query to the first quarter. Think for a minute, test for yourself.

MHE

Report message to a moderator

Previous Topic:	How can I generate output like Matrix Transposition?
Next Topic:	TRYKY QUERY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 01:25:15 CST 2024