I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example
5.24 Plus 5.24 it will give the result 10.48
I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.
In roster form user define shift of every employee like
Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00
Roster table structure
code varchar2
name varchar2
Shift date
Remarks varchar2
shift table structure
code varchar2
timein date
timeout date
latetime date
Hdaytime date
Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.
Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.
I'm getting trouble in getting the sum of all the product's prize present in the form fields of invoice form. For example, I user bought 2 products so I want to get there prize sum in the total box. I first want that I've QUANTITY, DISCOUNT and SELLING PRIZE.
If I multiply Quantity with Selling Prize then I gets the result in Product and Quantity Total Box. However, Now I want to add discount option in it. Means if user is providing 10% discount to his/her customer then Quantity * Selling Prize - Discount% becomes a result in product's solution final prize box.
Here is the pic..
Look...Now here..I've added 22 in quantity.. now if I'll insert 10% in discount then the total will comes in Amount Display Box.
I'm currently using this coding on on Prize Box..
:SP_PRODUCT_PTOTAL := NVL(:SP_PRODUCT_QUANTITY,0) * NVL(:SP_PRODUCT_PRIZE,0) * NVL(:SP_PRODUCT_DST,0) / 100;
I have 2 fields f1,f2
I define f1 a summary in calculation mode
I want to sum f1 where f2 = 5
I'm creating an on demand software. I have 3 text boxes on my form
1st text box will carry Product's Actual Price
2nd text Box will carry Product's Selling Price
3rd text Box will carry Profit..
3rd one is display item..this will carry "selling price - actual price" and then result into 3rd box.. that would be profit..
my query is select nvl(:PL_PRODUCT_SELL_PRICE, 0) - nvl(:PL_PRODUCT_ACT_PRICE, 0) INTO :PL_PRODUCT_PROFIT FROM SBM_PRODUCT_LIST;
how can I move Proceed? I've currently applied this trigger as POST-query on form and POST-CHANGE on :PL_PRODUCT_PROFIT
How To Display Runtime Calculation in cursor for example in loop i define xyx number then add + 1 in xyz and move its value to block item until loop valid but in display no value shown but if i apply message(xyz) then it also shown the answer is correct but with message style i click every time in message but i want that with some delay form automatically show the value of xyz in block field.
View 3 Replies View RelatedI am able to run this query in Sql Prompt.
DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
[Code]...
It gives proper / desired results. I want to use it on my Form, how can it be done?
I'm facing a problem where I create a form and I want to feed time in "HH:MI" format, Firstly I choose the "Date" datatype, but during the feeding of time i.e "00:30", means if I want to feed only mints, then it doesn't accept, at the other hand if I feed "01:30" means mints with hr then it accepts.
To get rid of this problem I changed datatype "Date" to "Varchar2".
I create 3 columns on my form "TTL_WORKING_TIME" - "TTL_RUN_TIME" = "BRK_DWN_TIME". If I get any value in "BRK_DWN_TIME" column thn it has to be distribute in 3 or 4 reason of B/D, means if I get 00:30 mints brkdwn thn 00:15 mints for "reason1" and 00:15 mints for "reason2".
How I make calculation where I use "Varchar2" datatype.
I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table. Now I want to generate stud_roll_no automatically until the last record is present.
View 4 Replies View RelatedI have made one application form where users need to enter some data. This data is getting inserted in four tables. As in for now the data is properly getting saved and retrieved only for one user at a time. But problem arrives when more than one users are simultaneously making an entry and saving the data at one time.Same number is getting generated for the users who are saving the data at one time which should not happen.
View 4 Replies View RelatedI have a sale invoice form with 3 data blocks.
Data block 1- master_blk : For date/customer of sale invoice
Data block 2- detail_blk1 (detail of the master block - For products and qty)
Data block 3- detail_blk2 (detail of DETAIL_BLK1 For entering serial numbers of products)
My requirement is that whatever quantity user enter in data block 2 against each product he must enter equal number of serial numbers of that product in data block 3.
For this I have created on item (cnt_iteml : to count product's serial numbers in block3 ) in data block 2, and on summary item (t_serial_no ) in block3.
Whenever user changes in quantity, cnt_iteml: item is populated with t_serial_no in block3 of that product by following trigger on quantity column.
POST-Change Trigger ( quantity column )
:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;
Following trigger is written on block level at data block-3 to populate cnt_iteml with t_serial_no.
PRE-RECORD
IF GET_BLOCK_PROPERTY('SERIAL_NUMBERS',STATUS) IN ('CHANGED') THEN
:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;
END IF;
Above triggers are fulfilling my requirement except following condition.
If user after entering serial numbers in block 3 and without saving goes back to block2 and try to navigate to another record he gets a message asking him to save changes by forms. At this time if user presses no then cnt_itl item is not been populated with t_serial_no item's value.
What I want in above condition is that if user was inserting new record cnt_it item should be populated with 0, so that he shouldn't be able to save this record. And If he was updating then cnt_itl item should be populated with actual no of records in database against that product.
i have attached the .csv file for gratuity calculation.
In the given criteria you can see if number of years 1 then he can get 1 salary if years 1.5 then 2 salary and so on also we need to take care about the leap year.
,i had given the sample data below
create table ex (sno number,ename varchar2(10),job_code char(4),sal number);
insert into ex values(101,'John','Java',21000,'IT');
insert into ex values(102,'Michel','BI',25000,'IT');
insert into ex values(103,'Johny','Java',30000,'IT');
[code]...
My expected output is attached in a text file
I want to display Week No as the heading.So, I have these:
define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;
I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;
The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?
I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.
I need to be able to determine the difference between column A and B eg.
Column A: Column B:
11:00 14:00
I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.
Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:
Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.
Check the following
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'gmt')
AT TIME ZONE '+05:30' "Time at Time zone"
FROM DUAL;
Time at Time zone
01-DEC-99 04.30.00.000000 PM +05:30
My requirement is that
I want to add 2 hours to DateTime i get here i.e. add two hours in result and display the resultant date and tine in time zone '+05:30'. I also want to check that the resultant time falls in business hours (9 am to 6 pm).
for making the query for following data,when we give start and end date then query need to calculate number of years.
if less than one year then return 0
if exact one year then return 1
if exact 1.5 years (18 months) then return 2
if exact 2 years (24 months) then also return 2
if exact 2.5 years (30 months) then return 3
if exact 3 years (36 months) then also return 3
if exact 3.5 years (42 months) then return 4
if exact 4 years (48 months) then also return 4
and so.
also we need to add leap year 1 day if exist in start and end date.
YearSalary
11
1.52
22
2.53
33
3.54
44
4.55
55
[code]....
I have three table for the stock calculations. The structure are like this
Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)
Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
I need to find out the valuation on particular sales date at FIFO method.
T1 = 06-Jun-2011 4:00PM
T2 = 10-Jun-2011 11:AM
Calculation is Required.
If at the end of Day Hours of T1 > 6 hours then it should calculate 1 Day.
same with T2. hours of T2(from begining of Day till end Time (11AM), it should again caculate if >6 hours then 1 Day.
remaining will be date calculation. this is how T2 - T1 should give us 5 Days.
Detail
06-Jun-2011 4:00PM to 06-Jun-2011 12:00AM (8 hours > 6 = 1 Day)
06-Jun-2011 12:00AM to 07-Jun-2011 12:00AM = (1 Day)
07-Jun-2011 12:00AM to 08-Jun-2011 12:00AM = (1 Day)
08-Jun-2011 12:00AM to 09-Jun-2011 12:00AM = (1 Day)
09-Jun-2011 12:00AM to 10-Jun-2011 12:00AM = (1 Day)
10-Jun-2011 12:00AM to 10-Jun-2011 11:00AM (11 hrs > 6 = 1 Day)
this is how 5Days calculation is required.
We want to calculate Annual leave the scenerio is as follows
1. Employee service more the one year
2. if joining date is 07-04-2008 than on 07-04-2009 completed one year and on 07-04-2010 completed two years and so on. day and month of date must be the same.
so we need YEAR*14. e.g. if 1 year completed then 1*14 if two years completed then 2*14 and so on. by this we will get the opening of Annual leaves.
We have another table where we entered the leave day by day in whole year, if he avail 10 leaves in first year then 4 will be remaining and we can say it is the closing of first year and opening of second year. so on second year he will entitled 14 more leaves so 14 + 4 = 18 should be opening of next year.In year Caclulation Day and Month must be the same for example 07 day, 04 month for every year. 14 Annual leaves are fix for each year
following are columns for output.
empid, ename, date of joining, sysdate, leave opening, leave avail, closing balance,
1, jone, 29-05-2009, 29-05-2012
2, herry,29-05-2008, 29-05-2012
3, bell, 29-05-2006, 29-05-2012
between two dates (date of joining and system date)
V: 10.2.0.3
I want to calculate on column-layer of a sql-query:
select a "column1", "column1" + 1
from ...but it does not work...
I got 4 fields:
event_date (01/01/1900 hh:mm:ss)
team_count (number)
interview_count (number)
duration (01/01/1900 hh:mm:ss) - duration of each interview
The calculation is:
(interview_count * team_count * duration(hh:mm)) + event_date
i.e.:
(3 * 2 * 02:30) + 01/01/1900 08:00:00
= 15:00:00 + 01/01/1900 08:00:00
= 01/01/1900 23:00:00.
all I care about is the hh:mm in the result.How can I preform this calculation in pl/sql?
I want to write a sql qeuery to get result similar to expected average column as shown in sheet.
Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.
Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists)
Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5)
Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)
We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .
View 7 Replies View RelatedI'm unable to create a select statement which calculates & brings the below fields :
- Act_Net_Last_Month (LastMonth)
- Act_Net_Same_Month_Last_Year (LM_LastYear)
My Table :
Acc_ID - Period_Name - Period_Year - Act_Net
000044 - Aug-2011 - 2011 - 4493
000044 - Aug-2010 - 2010 - 4300
000044 - Jul-2011 - 2011 - 4389
000044 - Jul-2010 - 2010 - 4266
Example :
Acc_ID - Period_Name - Period_Year - Act_Net - LastMonth - LM_LastYaer
000044 - Aug-2011 - 2011 - 4493 - 4389 - 4266
My problem is that I am working with a balance (Infor/WorkBrain - Time & Attendance) I have a balance called Bank Holiday(bal_id 10044). Every holiday that an employee works, he is allowed to take another day off in lieu of the day he worked. The employee has up to twelve months to take that holiday.
My job is to figure out when he worked that holiday (not a problem) and send an email/note at the 11 month mark and notify the employee and supervisors that said employee has 1 month to use his banked holiday.
Here is what the employee_balance log looks like.
EBLOG_IDEMP_IDBAL_IDEBLOG_DELTAWRKS_WORK_DATEWRKS_IDEBLOG_MESSAGE
51200128537100441 10/15/2010 5281229TCODE_NAME:FR_HWS
51150828537100441 7/1/2010 5136881TCODE_NAME:FR_HWS
51150928537100441 7/27/2010 5136942TCODE_NAME:FR_HWS
51150528537100441 8/1/2010 5137137TCODE_NAME:FR_HWS
5294372853710044-1 8/31/2010 5137173TCODE_NAME:FR_HWO
52947828537100441 6/3/2010 5001014TCODE_NAME:FR_HWS
My problem is the data/constraints I am working with. I can tell what day the holiday was based on a time code (EBLOG_MESSAGE) and EBLOG_DELTA shows a positive 1. I can also tell when the employee has used a bank holiday (EBLOG_DELTA is -1) and EBLOG_MESSAGE = FR_HWO.
But how to I perform the deduction so that I get four most current dates. Meaning since my first date that the employee gets for a holiday wokred is 6/3/2010 but the employee used a bank holiday on 8/31/2010. So I want to display the dates 7/1/2010, 7/27/2010, 8/1/2010 and 10/15/2010.
My preference would be not to add anything new to this table as it is a core table for Infor/WorkBrain.
I'm calculating a Z score based on some simple numerical data thus:
create table t (id number, val number);
insert into t values(1, 1795);
insert into t values(2, 1753);
insert into t values(3, 1743);
insert into t values(4, 1876);
insert into t values(5, 1848);
[Code] .....
the logic is quite simple - calculate a moving average over the previous 12 rows, and a stdev over the same window. Then subtract the prior row's moving average from the current value, and divide by the prior row's stdev.
The issue is I want to expose this logic in a BI tool (OBI EE v10g), meaning I can't use the nested analytic functions. How to achieve this logic in a single analytic pass? The sql above took about 2 minutes to write this morning, then I've spent all day looking at user-defined aggregate functions, but haven't even been able to get the first step, the moving average, working. I can understand that I can probably create an udaf to replicate the avg(val) over (order by id ROWS BETWEEN 11 PRECEDING AND 0 FOLLOWING) functionality, but I can't see how to bundle the logic for the other three steps in the calculation into this.
From what I've read, the ODCIAggregateMerge should allow me to combine different threads that can return the different values I need for the current row calculation. Is this correct?
The only example udafs I can find are either not relevant (STRAGG) or very simple (ie don't appear to invoke multiple passes). I've also had a look at the COLLECT function, but again I can't see a way to use this.
Till statspack we had
elapsed time = time spent on waits + time CPU was used
Total time during snaps = Elapsed time + (may be) time waited for CPU...In AWR is it possible to draw such equation? I can see that the AWR report has following elements
1) End Snap time - Begin Snap time
2) DB time - as mentioned at the top of AWR report
3) DB CPU - in "Top 5 Timed Foreground Events" (I assume this is 'CPU used by sesson timing' in statspack)
4) Total of time for all Statistics in "Time Model Statistics"
5) BUSY_TIME + IDLE_TIME - "Operating System Statistics"
Time between 2 snapshots? or what else? Also for which seconds to multiply to 'DB Time(s)' per second and 'DB CPU(s)' per second in Load Profile to get the db time and CPU time?