Solved

Oracle

Posted on 2011-03-24
16
314 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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