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
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.
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.
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
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 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).
I have table called Orders with lots of fields from Imports Fields are ConsigneeKey,Shipto,ProductCode. and another table called Product. Main fields are ProductCode,Price,Weight,StdCube. Now I have two list of Consineekey,One which is shipto X area and another is of Y area. I want to make query like separate list of Orders which is shipto X,Y and Common Products in Both X and Y. I will supply list of Consigneekey in where clause.
I have a table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .
I have a query in reports.My requirement is that report should fire everyday in a year.When an order is cancelled/copied and also that when an order is cancelled then the line amount should be set to zero and order_status should automatically set cancelled.
EX: order_no amt product_name quote_no order_status 45434545 200 dell 10022 active when order cancelled then order_no amt product_name quote_no order_status 45434545 0 dell 10022 cancelled
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.
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)
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?
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
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 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.
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.
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
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 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..
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 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
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..
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?