Solved

Oracle

Posted on 2011-03-24
16
315 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:GRChandrashekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35204851
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
 

Author Comment

by:GRChandrashekar
ID: 35204911
Attached
GUEST.XLS
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35204989
Do you know how many records you will get at the most ? is it always fixed that you will get only 3 records ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GRChandrashekar
ID: 35204992
No I wanted all those records which belongs to FRIDAY day in last 2 years
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35204996
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
 

Author Comment

by:GRChandrashekar
ID: 35205001
YES
0
 

Author Comment

by:GRChandrashekar
ID: 35205008
Or I dont mind like this

Date Amount
1/2  100
2/2 200

But only fridays and sum(amount) for fridays
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35205264
ok. this makes sense : try this :

select date1, sum(amount )
from your_table
where to_char(date1,'DY') = 'FRI'
group by date1
0
 

Author Comment

by:GRChandrashekar
ID: 35205269
how do i filter only year 2010 and 11
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35205274
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
 

Author Comment

by:GRChandrashekar
ID: 35205283
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
 

Author Comment

by:GRChandrashekar
ID: 35205292
Is it becoz my REGNDATE has time in DB? if so how to alter the query ?
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 35205426
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35205438
yes, if time portion is there, it will not group properly unless we use trunc(..)
0
 

Author Comment

by:GRChandrashekar
ID: 35205441
how do I use trunc?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35205448
i already posted the query, just see the previous update of mine.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question