PL/SQL :: Query To Find Number Of Days Across Each Month
Oct 1, 2012
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.
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.
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST FROM EMRASTHAMAHISTORYDETAILS WHERE ------ GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
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.
MISSING_DATES EMPNO ---------------------- ---------- 09-SEP-12 TO 11-SEP-12 7499 23-SEP-12 TO 26-SEP-12 7499 01-sep-12 TO 30-SEP-12 7521 01-sep-12 TO 30-SEP-12 7788
I have a table with starting date and ending date.
I want to fetch monthwise days for given two dates
IE starting 25/12/2009 ending 25/03/2010
Result should like below.
Dec-09 7 Jan-10 31 Feb-10 28 Mar-10 25
I have tried this but it is not giving me the result which want..
select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from (SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE FROM ( SELECT 1 just_a_column FROM dual CONNECT BY LEVEL <= 366
i need to find the number of days employee was absent for the month, we are maintaining a table of attendance , where the daily data of hours is captured, what i need now is to capture total number of days he was absent or not present.How can i get the sum of absent days per employee based on given month, as the difficulty is i have to consider 30 days as standard working days for the month , regardless of months having either 30 days (April,June,September,November),or 31(January,March,May,July,August,December) or 28(Consider leap year also february) days.For eg ,If he is absent for 31 days in december then it should consider only 30 days as absent or if he is absent for 28 days in february still it should consider 30 Days as absent.
create table ot_job_det (jt_txn varchar2(12),jt_no number,jt_dt date ,jt_emp_code varchar2(6),jt_duration number ) delete from ot_job_det
[Code].....
I managed to do this with the given query , but this will check only for 31 days and 30 how to consider the february month or any leap year.
SELECT JT_EMP_CODE,DECODE(SIGN(30-COUNT(JT_DT)),-1,30,30-COUNT(JT_DT)) a FROM OT_JOB_DET WHERE TO_CHAR(JT_DT,'YYYYMM')='201212' GROUP BY JT_EMP_CODE;
I need to find out that assume i have a table having 2 column
Num Name 1 Adam 2 Akanksha 1 barren 2 bosli 3 Benergee 4 Bhawna 3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets
We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.
provide a simple query using case in ORDER BY clause.
ID CLASS NAME DIST_ID DIST_NAME 0 NULL KIRAN 0 AP 0 C1213 NULL 0 AP 0 NULL NULL 0 AP NULL C1234 NULL 0 AP 0 NULL NULL 0 AP NULL NULL NULL NULL NULL 0 C123 RAJESH 0 AP NULL C123 RAVI NULL AP
We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as
1 2 3 4 5 ID CLASS NAME DIST_ID DIST_NAME
for the following three records number of NULL values are same. but positions are different.
0 NULL NULL 0 AP NULL C1234 NULL 0 AP 0 NULL NULL 0 AP NULL C123 RAVI NULL AP
Based on the column positions the ranks as 2*2+3*3=13 1*1+3*3=10 2*2+3*3=13 1*1+4*4=17
Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as
ID CLASS NAME DIST_ID DIST_NAME 0 C123 RAJESH 0 AP 0 NULL KIRAN 0 AP 0 C1213 NULL 0 AP NULL C1234 NULL 0 AP 0 NULL NULL 0 AP 0 NULL NULL 0 AP NULL C123 RAVI NULL AP NULL NULL NULL NULL NULL
insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10); insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10); insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10); insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10); insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
I want to find the months and days between 2 dates. For Eg. Date-1 : 25-Aug-2013Date-2 : 23-Oct-2013 If we consider every month as 30 days it should give 25-Aug-2013 to 30-Aug-2013 = 6 days 01-Sep-2013 to 30-Sep-2013 = 1 Month 23-Oct-2013 to 30-Oct-2013 = 8 days Total = 1 month and 14 days
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?
Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?
For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE ( medical_record_number VARCHAR2(600), requested_test_name VARCHAR2(39), result_date DATE
[code]..
Process :will be explaining with a patient id : 1) Consider the patient 1001274 from mbs_first_date table. 2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor). 3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP) 4) The first record we have is CHL with 08/05/2009 (May 8th 2009).. 5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient. 6) The next record for SBP is 11/05/2009 (May 11th 2009) . 7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009).. Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it. 9) Go to the next test for the same patient which is DBP.. 10) The DBP first date is July 08th 2008.. 11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record. 12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be 1001274 SBP 11/05/2009 1001274 CHL 08/05/2009 1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
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)
case 1 code start month end month winter march march summer april january spring feburary feburary
case 2
winter January January summer february september spring october december
and if i am trying to enter again start month and end month . i should validate if the month i am giving is already present in the table for any of the season
i want a do the validate with a query so that i will not enter duplicates.
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:
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 need a query to whome i will pass month in number and that should return name of month. If i input 1 it should return Jan,2,Feb but don't use decode etc.
Is there any query with Julian format or any other date function?
i have an application for a medical laboratory, i insert patients every day , what i want is toget the number of patients for the past month ?2- for the past week ?3- the past day , and so on.
