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.
View 10 Replies
ADVERTISEMENT
Oct 20, 2011
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.
View 3 Replies
View Related
Nov 18, 2011
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
View 4 Replies
View Related
Aug 23, 2010
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
how to use in where condition
View 13 Replies
View Related
Mar 17, 2011
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]....
View 1 Replies
View Related
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.
View 3 Replies
View Related
Sep 26, 2012
I need query to find the missing dates between two columns in given month.
CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);
[code].....
Required output is
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
View 8 Replies
View Related
Apr 28, 2010
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
[code].....
View 3 Replies
View Related
Dec 29, 2012
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;
View 16 Replies
View Related
Sep 9, 2013
How can I get count of working days as of given date using SQL?
View 6 Replies
View Related
Apr 20, 2013
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
View 6 Replies
View Related
Aug 18, 2013
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
View 15 Replies
View Related
May 17, 2011
is there any query to find number of primary keys present in a table.
View 13 Replies
View Related
Apr 27, 2013
create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));
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';
Result :
date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10
[Code]....
View 5 Replies
View Related
Oct 9, 2013
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
View 33 Replies
View Related
Apr 8, 2013
I have a customers table like
ORDER_ID CUSTOMER_ID ORDER_DATE
__________________________________
1 | 100 |25-JAN-13
2 | 100 |25-JAN-13
3 | 100 |24-JAN-13
4 | 101 |23-JAN-13
5 | 101 |22-JAN-13
6 | 102 |21-JAN-13
7 | 102 |21-JAN-13
I would like to find the customer_ids who placed orders for two days.
View 3 Replies
View Related
Jan 14, 2008
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?
View 1 Replies
View Related
Apr 25, 2011
1) First Problem:
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?
SQL> select (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'))
3 from dual
4 ;
[code].....
1) Second Problem:
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.
SQL> SELECT MOD(((to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440),
3 60) AS duration,
4 (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
5 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440 as diffinmin
[code]....
View 2 Replies
View Related
Sep 25, 2012
I am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer
select to_char(last_day(sysdate),'DD-MON-YY')from dual
ans : 30-SEP-2012
But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.
View 2 Replies
View Related
Apr 18, 2013
i want to create function to show number of days between to days such as number of Friday days between to dates
View 13 Replies
View Related
Mar 10, 2012
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
declare
V_ID1 VARCHAR2(200) := '';
V_TEST1 VARCHAR2(200) := '';
V_DATE1 DATE := NULL;
[code]...
View 2 Replies
View Related
Feb 13, 2013
How to find out first thursday for each month in year.
View 7 Replies
View Related
Sep 17, 2013
find the last nth month name [Single month].
View 5 Replies
View Related
Sep 27, 2010
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)
ORDER BY Date_Diff ASC
View 6 Replies
View Related
Apr 3, 2011
if i have data like this
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.
View 5 Replies
View Related
Jun 12, 2012
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:
bbc 1/5/12 10:30
abc .....
then
cca
according to the no of attendannce
View 6 Replies
View Related
Jul 8, 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
View 4 Replies
View Related
May 22, 2010
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.
----------------------------------------------------------------------------------------- -------
Transaction Narration DebitCreditBalanceNo of DaysProduct
DateDifference
----------------------------------------------------------------------------------------- --------
01/04/2009Opening Balance 45020450277346654
17/06/2009ByAmt5044521253424
29/06/2009By Amt1004352156678912
02/12/2009By Amt424310119476000
[code]....
improve the above code and get the desired output result.
View 5 Replies
View Related
Feb 17, 2010
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 dont want to use ,Case and Decode ,level etc.
View 2 Replies
View Related
Nov 16, 2013
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.
View 11 Replies
View Related
