oracle query

 select 'DRV'||drive_id,
       drive_date,
	   site_code,
	   nvl(actual_draw,0) as actual,
	   nvl(projection,0) as projection
from blood_drives
where drive_date between '01-aug-2014' and '31-aug-2014'
  and area_rep_no = 11
  and drive_cancelled is null
  order by drive_date

Open in new window


My requirement

The user does not want to put the date range as I have shown, but they want a parameter like Aug 2014, Sep 2014....  etc.

where drive_date = Aug 2014 ( this comes from a list)

Once the user chooses value then I have to translate that value to 1st day of the month and last day of the month.

1. I want to know how to get sql to get list of month and year
2. based on the value chosen translate to 1st day of the month and last day of the month.
3. Based on actual and projection I need to give % efficiency ( how did the drive perform) as one column next to projection.
table-script-and-sample.txt
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Priya SudharsanProgrammer AnalystCommented:
Do you want to make these changes(Identifying first and last day of the month) in the Stored procedure?
0
slightwv (䄆 Netminder) Commented:
First day of month:
to_date('Aug 2014','Mon YYYY')

Last day:
last_day(to_date('Aug 2014','Mon YYYY'))

Add where appropriate to your select.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
the data comes from the table blood_drives. I did not see the need of a stored procedure. But one thing is I might have to create 2 parameters in the query since I need between. But parameter can be hidden from the user. What do you think?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
1 parameter, can still use between.  Just convert the one parameter to dates twice as I showed above.
0
anumosesAuthor Commented:
ok. that is a very good suggestion. Also I need to create a list for the user. with month and year. Any help?
0
slightwv (䄆 Netminder) Commented:
>>Also I need to create a list for the user. with month and year. Any help?

I don't understand.
0
anumosesAuthor Commented:
Aug 2014
Sep 2014
Oct 2014
Nov 2014
Dec 2014
Jan 2015
  and so on
0
anumosesAuthor Commented:
I am sorry. No need to get a list. User will enter values by themselves.
as Aug 2014  etc
0
slightwv (䄆 Netminder) Commented:
From when to when?

Creating the list is easy:
select to_char(add_months(to_date('Jan 2014','Mon YYYY'),level-1),'Mon YYYY')
from dual connect by level < 100
/


You just need a start point and for how many months.
0
anumosesAuthor Commented:
two questions have been answered. Only one left out

3. Based on actual and projection I need to give % efficiency ( how did the drive perform) as one column next to projection.

will it be (actual/projection) * 100

Thanks in advance.
0
slightwv (䄆 Netminder) Commented:
You already have the two numbers...
Divide actual by projection?
0
anumosesAuthor Commented:
user wants drive efficiency. So to give in percentage.
0
slightwv (䄆 Netminder) Commented:
and?

You have the actual number.
You have the projected number.

Divide them to get the percentage.

http://www.helpingwithmath.com/by_subject/percentages/per_calculating.htm

If actual was 10 and projected was 20:
10/20 = .5
.5 * 100 = 50

So, it is 50%
0
anumosesAuthor Commented:
case when actual_draw is not null then (actual_draw/projection) * 100 else null end as drive_efficiency
0
slightwv (䄆 Netminder) Commented:
That should work as long as projection is NEVER 0.  If 0 is a possibility, you'll need another case statement to account for it.
0
anumosesAuthor Commented:
projection will never be zero that is why I am taking drive_cancelled is null.
Also I removed nvl(actual_draw,0) as actual_draw and made only actual_draw as the user does not want to see zero there.
0
slightwv (䄆 Netminder) Commented:
Anything else?
0
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.