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?
GRChandrashekarAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
try this :

select trunc(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 trunc(REGNDATE)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
GRChandrashekarAuthor Commented:
Attached
GUEST.XLS
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Do you know how many records you will get at the most ? is it always fixed that you will get only 3 records ?
0
 
GRChandrashekarAuthor Commented:
No I wanted all those records which belongs to FRIDAY day in last 2 years
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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 ?
0
 
GRChandrashekarAuthor Commented:
YES
0
 
GRChandrashekarAuthor Commented:
Or I dont mind like this

Date Amount
1/2  100
2/2 200

But only fridays and sum(amount) for fridays
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok. this makes sense : try this :

select date1, sum(amount )
from your_table
where to_char(date1,'DY') = 'FRI'
group by date1
0
 
GRChandrashekarAuthor Commented:
how do i filter only year 2010 and 11
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
GRChandrashekarAuthor Commented:
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

0
 
GRChandrashekarAuthor Commented:
Is it becoz my REGNDATE has time in DB? if so how to alter the query ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes, if time portion is there, it will not group properly unless we use trunc(..)
0
 
GRChandrashekarAuthor Commented:
how do I use trunc?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i already posted the query, just see the previous update of mine.
0
All Courses

From novice to tech pro — start learning today.