Query To List Employee Number And Amount Of Days Sick
Apr 19, 2009
I have been trying to construct a query in Oracle that allows me to do the following:
For example if I have the data below:
EmpNo DOB SickDays
Alex 445 15/06/1985 7
Tom 778 22/08/1981 4
James 992 07/10/1978 5
I need to write and a query to lists the employee number and the amount of days sick that they have had and also add a column that compares the number of sick days to the average number of suck days by ALL employees.
I can calculate the average sick days etc, but It wont see to allow me to find the difference between that and the amount of sick days that each person has had. I have tried this many ways and have not been able to come up with a solution.
11 rows selected. I am not able to understand what is the difference between the two queries! Why is the second query able to do what the first query cannot?
/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */ CREATE PROCEDURE p_get_name ( p_empno IN OUT NUMBER, p_name OUT VARCHAR2, p_err OUT NUMBER [code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
Is there any Oracle query I can run to determine the "number of days since the last backup"? SQL Server provides this data but I cant seem to find the equivalent for Oracle. Looks like there may be some information like this via RMAN tables and if so I want to create the simplest query possible to obtain that information.
We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.
We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.
We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below
Number of Working days in March is 31 - 3 + 1 = 29
Allowance A1 = (300 * 29 )/31
Number of Working days in April is 5 ( this also needs to find logical I am guess ) Allowance A2 = (300 * 5 )/30
Then A1 + A2.
The A(n) would be the total allowance where provided the number of month across.
i have an employee table where there is a column called join date. Now i have to select the employee according to the days. Means how many employee joined on Monday/Tuesday etc.
What I need to do is take 2 dates from a table named 'projects' and insert the number of days between them into a table named 'time_record'.How do I go about this?
setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.
CODESELECT To_Date(need_date, 'YYYYMMDD') Need_Dt, To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date, To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
FROM tableT
WHERE need_date <= (Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END)
I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.
I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..
like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')
There is a attendance table having structure(empid number,signtime datetime)It has data of attendance of employees:
What is the right sql to show employees detail attendance according to the no of days attendance . i.e.(According to the maximum no of attendance first and so on)
suppose: 3 employees abc,bbc,cca abc has 20 days of attendance ,bbc has 21 days,cca has 19 days..The report like this:
EMP_ID EMP_NAME MANAGER_ID 1 KING 2 STEVE 1 3 FRANK 2
What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.
For example, when I query for EMP_ID=2 ,I want to get this result
EMP_ID EMP_NAME MANAGER_ID 1 KING 2 STEVE 1
and when I query for EMP_ID=3 ,I want to get this result:
EMP_ID EMP_NAME MANAGER_ID 1 KING 2 STEVE 1 3 FRANK 2
The data in one of the temporary table has been compiled as below. Number of Days elapsed between two transaction dates is required to be computed and multiplied with the balance. There can be multiple number of same transaction dates. When previous date and the current transaction dates are same the resulting number of day difference should be 0. But when they are different the difference between them is to be computed. On the last day of the Financial Year i.e. '31/03' of any year the difference day should be shown as 1 so as to make 365 or 366 days in a year. Simply deducting d1 from d2 on 31st will not be suffice as the difference is one day less.
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
I am trying to get the last 7 days of record from today date, this query runs every night and I always want the last 7 days. Example - today is 3/13/2013 so I want record from 3/7/2013 to 3/13/2013 and tomorrow it would be 3/8/2013 to 3/14/2013
I have a problem with a query. I have a table employee with data as
emp_id date day working_ind 1 01-Jan-2011 Mon Y 1 02-Jan-2011 Tue Y 1 03-Jan-2011 Wed Y 1 04-Jan-2011 Thu Y 1 05-Jan-2011 Fri Y 1 06-Jan-2011 Sat N 1 07-Jan-2011 Sun N 1 09-Jan-2011 Tue Y
Sundays/ Monday/ any public holiday the working_ind will be N. If the emp is absent on one day then there will be no record entered in the table (e.g. 8th jan there is no record). Each table has only one year data.
I need to retrieve for all employees when they worked for 30 consecutive days without being absent which does not include sat/ Sunday / holidays.
Its like: -- i need to order by emp_id and date -- get oly the data with working_ind as Y -- make sure that i get 30 consecutive days (from what ever i get above) where no days data is missing
I tried using lag and inner join but it does not seem to be working.
I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).
