Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle

Posted on 2011-03-24
16
Medium Priority
?
318 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

926 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