SQL & PL/SQL :: Extract Absent Days From The Month
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
ADVERTISEMENT
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
Sep 9, 2013
How can I get count of working days as of given date using SQL?
View 6 Replies
View Related
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
View Related
Oct 18, 2012
I have a requirement that I need to create a report for absent employee, we have "Hand Punch" scanning system for attendance, when a employee scan his hand it put a record with the employee id, date, shift, & time.
Now I need to create a report for absent employees in date range
View 2 Replies
View Related
Sep 2, 2011
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).
View 7 Replies
View Related
Apr 5, 2011
want to write Procedure to return weeks and days from given set of days.
let suppose we have 72 days to return weeks and days then return should be 7 weeks and 2 days.Can i use date function Or ?
View 5 Replies
View Related
Jun 4, 2013
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
May 16, 2013
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
View 5 Replies
View Related
Nov 25, 2010
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
View 17 Replies
View Related
Oct 18, 2010
I need to know if a customer appears at least 4 consecutive days. Here's an example of data
CLI_ID , DATE
-------------------------
123 , 2010/10/01
123 , 2010/10/04
123 , 2010/10/05
123 , 2010/10/06
123 , 2010/10/07
123 , 2010/10/08
123 , 2010/10/10
456 , 2010/10/01
456 , 2010/10/02
456 , 2010/10/03
456 , 2010/10/06
456 , 2010/10/07
456 , 2010/10/08
456 , 2010/10/11
In the example the client 123 appears from 2010/10/04 to 2010/10//08 (5 consecutive days), so this client must appear in the output. In the example customer 456 does not appear at least 4 consecutive days, so should not appear in the output.
View 12 Replies
View Related
Mar 14, 2012
I have not defined the table ( I only have privileges to query data).
I am unable to copy and paste my real code here, and the actual results from the run, as my company will fire me if I do so... so here is how things approximately look like (tried to keep it as real as possible).
Let's say that the table CYCLE has client numbers (clientid), cycle number (cycleno), date of visit (visdt).
I am trying to create a query to calculate how many days there are between each two consecutive visits/cycles for a single client(let's say 1200004)
clientid / cycleno / visdt
---------------------------
1200004 / 1 / 10OCT2011
1200004 / 2 / 31OCT2011
1200004 / 3 / 21NOV2011
1200004 / 4 / 05DEC2011
1200004 / 5 / 03JAN2012
...
1000005 / 1 / 04NOV2011
1000005 / 2 / 03DEC2011
1200004 / 1 / 10JAN2012
1200004 / 2 / 15FEB2012
.
.
.
The code below is the only one that kind of seemed to work, but it is definitely not giving me the right results.
SELECT cycleno1, visdt1, cycleno2, visdt2, to_date(visdt1) - to_date(visdt2) days
FROM (SELECT clientid, cycleno cycleno1, visdt visdt1,
LEAD (visdt, 1) OVER (ORDER BY cycleno) visdt2
FROM CYCLE) a
[Code]....
I am getting a mess of a result of the kind:
cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 1/ 18OCT2011 / -8
1 / 10OCT2011 / 2/ 18OCT2011 / -8
1 / 10OCT2011 / 3/ 18OCT2011 / -8
1 / 10OCT2011 / 4/ 18OCT2011 / -8
1 / 10OCT2011 / 5/ 18OCT2011 / -8
I need my result to look like:
cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 2/ 31OCT2011 / 21
2 / 31OCT2011 / 3/ 21NOV2011 / 22
3 / 21NOV2011 / 4/ 05DEC2011 / 15
4 / 05DEC2011 / 5/ 03JAN2012 / 30
5 / 03JAN2012 / / /
.
.
.
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
Aug 2, 2012
My Table structure is
SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM
Question
How to select last 10 days rows only (from sysdate)
Error
1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)
2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected
[Code]...
View 5 Replies
View Related
May 11, 2012
Our "client" is delivering data that contains an "age" field. However, they've put the AGE in DAYS instead of years, while our system can only work with years. So I needed to convert the age to years.
We are getting 2 parameters: LOWAGE and HIGHAGE (both in days). However, for the same "test" (laboratory test), we are also getting some other records
eg:
TEST 1 lowage = 0 upperage = 5 OTHER values = xxx
TEST 1 lowage = 6 upperage = 100 OTHER values = yyy
So if the person would be 5 years or less, the values xxx would apply. If they are 6 years and older, the yyy values apply. Now, the problem is that we are not getting this data in years, but in days. And whichever function I try, the upperage from the first test also seems to be equal to the lowerage from the second test. And this gives an error, because both would apply to this person....
I've tried stuff like:
age/365
age/365.25 (leap years)
trunc(months_between(sysdate+ranges.lowerage,sysdate)/12)
View 6 Replies
View Related
Jun 8, 2010
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.
View 17 Replies
View Related
Nov 21, 2011
I have the following in my select statement and I am getting the days like 1130, 50, 60 etc
round(MAX (TO_DATE ('31-OCT-2011 23:59:59','dd-mon-yyyy hh24:mi:ss') - a.tx_dtime),0) DAYS
Now I want this to convert these days into months like
1.10 this denotes (one month 10 days)
1.25 this denotes (one month 25 days)
2.05 this denotes (two months 5 days)
I know I am using old Oracle but what to do my company is not spending to upgrade.
View 6 Replies
View Related
Nov 2, 2010
i want create a report like this
location sysdate refdate No_of_days SL1(0-90) SL2(91-180) SL3(>180)
So when no. of days between (0-90) data put in SL1
And when no. of days between (91-180) data put in sl2
I want to achieve this only in single query.
View 11 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
Jan 8, 2013
I would the query to do the following:
1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer
select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...
View 3 Replies
View Related
Aug 5, 2010
I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.
Test Case
--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);
--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...
View 8 Replies
View Related
Jul 7, 2012
Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00
How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color
query out put should give as below
start_date + next record ,end_date - Previous records should give the difference of
6
1
View 12 Replies
View Related
Jan 18, 2012
I have a table like this below one.
CCCID MCODECNODDEADLINEDATEDDEADLINETIME
IN00100213201201219/01/20121050
IN00100213201201118/01/20121050
IN00100213201201320/01/20121050
IN00100213111219821/01/20121050
I need a query which will fetch only 3 rows for a day. For eg if today is 18-jan-2012, then i have to show rows whose deadline date is 18-jan-2011, 19-jan-2011 and 20-jan-2011. And on 18-Jan-2011, after 10.50 a.m it should not consider deadline date 18-jan-2011, instead it show deadline date is 21-jan-2011.
This deadlinedate will not have all dates, only exchange working dates, so at one time we should show only the probable 3 deadline dates..
View 10 Replies
View Related
Nov 13, 2011
I want function return me how many days Thursday and Friday between two dates.
View 1 Replies
View Related
Oct 21, 2010
I intend to get for every client the start date and end of a contiguous range of days. Example for the same client have two records, in the first goes from day 1 to day 5 and the second from day 3 to day 9, i intend to get a record for this client where indicated that the start date is on day 1 and ending on Day 9.
SELECT 123 as CLI_ID, TO_DATE('20100101', 'YYYYMMDD') as DT_START, TO_DATE('20100105', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100208', 'YYYYMMDD') as DT_START, TO_DATE('20100321', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100219', 'YYYYMMDD') as DT_START, TO_DATE('20100228', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100227', 'YYYYMMDD') as DT_START, TO_DATE('20100405', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100901', 'YYYYMMDD') as DT_START, TO_DATE('20101013', 'YYYYMMDD') as DT_END FROM DUAL
Expected :
CLI_ID,DT_START,DT_END
123,01/01/2010,05/01/2010
123,08/02/2010,05/04/2010
123,01/09/2010,13/10/2010
View 2 Replies
View Related
Sep 19, 2010
format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request
Don't worry about process request..
View 4 Replies
View Related
May 24, 2010
I want to write a query that lists last 11 days. I tried the following query using CONNECT BY...
SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM DUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;
DAYS
--------------
20100514000000
For some reason it gives only the first day.. But this one works -
SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
WITH MYDUAL AS (
SELECT 1 FROM DUAL
)
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM MYDUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;
DAYS
--------------
20100514000000
20100515000000
20100516000000
20100517000000
20100518000000
20100519000000
20100520000000
20100521000000
20100522000000
20100523000000
20100524000000
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?
View 5 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
Oct 15, 2012
we are having a table in following format
day | grpID | pktID
--------------------
sun | 1 | 001
sun | 1 | 002
sun | 1 | 003
sun | 2 | 007
sun | 2 | 008
sun | 2 | 009
[code]...
1. We have a combination of pkdIDs related with a specific grpID, for a particular day.Ex: For Sunday, we have two combination list for grpID=1 is (001,002,003) and for group id = 2 is (007,008,009)
2. We need to get all the available combined pktid for each group id for all the days .
Eg the the expected result that is needed from the above table
(001,002,003)
(007,008,009)
(007,010)
I need all the available combination for all the days .
View 4 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