Solved

Oracle

Posted on 2011-03-24
16
312 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 41
Oracle Nested table uses ? 2 44
Oracle Errors 11 52
Password_rules_securitty.. 12 34
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

733 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