[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 9
  • 8
4 Solutions
 
Priya SudharsanCommented:
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now