Link to home
Start Free TrialLog in
Avatar of GRChandrashekar
GRChandrashekarFlag for India

asked on

Oracle

I have a table GUESTREGISTRATION
COLUMN -
DATE
AMOUNT

I need to prepare my MIS and request help in getting the following ouput

I need to filter only FRIDAYS for last years and get ouput like this

Date              AMOUNT      Date        Amount     Date     Amount

Depending on number of Fridays in 2 years the number of Columns will increase !

How do i do this?
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

give some sample data for 5 or 6 records and give the output what you need from that.

It is not always easy to understand from sentences/paragraphs.
Avatar of GRChandrashekar

ASKER

Attached
GUEST.XLS
Do you know how many records you will get at the most ? is it always fixed that you will get only 3 records ?
No I wanted all those records which belongs to FRIDAY day in last 2 years
assuming 52 in a year, so there are 52 fridays.

so do you mean to say that you need 104 columns ( 2 * 52 ) in your output ?
YES
Or I dont mind like this

Date Amount
1/2  100
2/2 200

But only fridays and sum(amount) for fridays
ok. this makes sense : try this :

select date1, sum(amount )
from your_table
where to_char(date1,'DY') = 'FRI'
group by date1
how do i filter only year 2010 and 11
try this :

select date1, sum(amount )
from your_table
where to_char(date1,'DY') = 'FRI'
and to_number(to_char(date1,'YYYY')) in (2010,2011)
group by date1
It is not grouped !

My query is
select REGNDATE, sum(REG_AMT )
from GUESTREGN
where to_char(REGNDATE,'DY') = 'FRI'
AND FLAG=0
and to_number(to_char(REGNDATE,'YYYY')) in (2010,2011)
group by REGNDATE

and OUTPUT IS


01/22/2010      250
01/22/2010      250
01/22/2010      500
01/22/2010      500

It should actually be
01/22/2010      1500

Is it becoz my REGNDATE has time in DB? if so how to alter the query ?
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yes, if time portion is there, it will not group properly unless we use trunc(..)
how do I use trunc?
i already posted the query, just see the previous update of mine.