Solved

Oracle

Posted on 2011-03-24
16
310 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

808 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