GRChandrashekar
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?
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?
ASKER
Attached
GUEST.XLS
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 ?
ASKER
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 ?
so do you mean to say that you need 104 columns ( 2 * 52 ) in your output ?
ASKER
YES
ASKER
Or I dont mind like this
Date Amount
1/2 100
2/2 200
But only fridays and sum(amount) for fridays
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
select date1, sum(amount )
from your_table
where to_char(date1,'DY') = 'FRI'
group by date1
ASKER
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,'Y YYY')) in (2010,2011)
group by date1
select date1, sum(amount )
from your_table
where to_char(date1,'DY') = 'FRI'
and to_number(to_char(date1,'Y
group by date1
ASKER
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
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
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
ASKER
Is it becoz my REGNDATE has time in DB? if so how to alter the query ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes, if time portion is there, it will not group properly unless we use trunc(..)
ASKER
how do I use trunc?
i already posted the query, just see the previous update of mine.
It is not always easy to understand from sentences/paragraphs.