SQL & PL/SQL :: Query To Get Consecutive Working Days From A Table
Jul 15, 2011
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.
View 5 Replies
ADVERTISEMENT
Dec 10, 2011
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.
View 7 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
Aug 7, 2013
I have a table (tblProject) with the following fields:
Project_ID, Project_Name, Update_Date
I need to look back over the last week to find out which of the projects stored in the above tables haven't been updated on consecutive days day.
For example i would expect to see:
14, Streamline, 01/08/2013
14, Streamline, 02/08/2013
14, Streamline, 03/08/2013
14, Streamline, 04/08/2013
14, Streamline, 05/08/2013
14, Streamline, 06/08/2013
14, Streamline, 07/08/2013
15, Bonus, 01/08/2013
15, Bonus, 03/08/2013
15, Bonus, 04/08/2013
15, Bonus, 05/08/2013
15, Bonus, 07/08/2013
The code should identify that the 'Bonus' project missed updates on 02/08/2013 and 06/08/2013. Should be a simple enough piece of code for an experienced developer i'm sure
View 5 Replies
View Related
Sep 5, 2012
Is there an Oracle date function that ignores public bank holidays and calculates working days only?
View 4 Replies
View Related
Mar 13, 2009
how do i can make oracle function for the following vb6 function.
This function is used for calculating working days between two date.
****************************************
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
[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
Jul 5, 2011
I need to identify a due date for a transaction (date) that should be completed within 15 working days from the date of the transaction date. For example, a request is submitted on Wednesday, December 29th 2010. This request should be then 'answered' on Thursday, January 20th 2011 at the latest.
I have a table that identify the status of every calendar day between 1960 to 2060; fields are: calendar day, calendar year, ..., day of week, week day flag, holiday flag.
In my example above, January 1st and 2nd are weekend days and Monday, January 3rd is an holiday.
How do I go getting all (calendar day + 15 working days) for all days of the year?
View 2 Replies
View Related
Feb 17, 2010
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.
View 7 Replies
View Related
Nov 11, 2011
I have table T with 50,000 rows
create table T
(student_id number,
class_id number,
quiz_id number,
marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, 29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, 34);
INSERT INTO T VALUES (2,2, 9, 11);
each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)
[Code]....
SQL> /
QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11
15 rows selected.
With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
my sample output is:
Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?
Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.
View 7 Replies
View Related
Mar 22, 2013
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
View 1 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
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
May 20, 2013
i have table name order table . in that i have column name install date,order date.
the difference bet ween order date and install date should be greate than or equal to 3 working days.
fri and sat are holidays.
i need one query to find the difference betwwen orderdate and install date is greate than 3 working days
View 3 Replies
View Related
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
Mar 13, 2013
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
Here is my query, dont mind the ****,$$$ or ####
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, [code]..........
View 16 Replies
View Related
Dec 18, 2012
I have a following requirement in SQL -
Requirement -
I need to list the purchase Ordrers which are not closed.
my sql query should pull if the PO is not closed in 30,90 and 150 days.
It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.
View 1 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
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
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
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
Jan 5, 2013
I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).
The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;
returns nothing.
Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;
returns character '?'.
View 4 Replies
View Related
Aug 14, 2011
is the definition of my table :
CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)
And these are the data that are available,(select * from DATEFETC)
IDDT
00108-09-2011
00208-10-2011
00308-11-2011
That's fine.
Now i am executing this query ,but this is returning no rows.Why ?
select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')
View 1 Replies
View Related
Jul 10, 2012
At once when I open the form when I press F11 it is not entering query mode. But if I type something in the text box placed in the form then I press F11 means it is asking "Do you want save the changes you have made?" if I press yes or no, no matter it is going for query mode. How to go to query mode without typing something in it?
View 13 Replies
View Related
May 11, 2012
I have a table with three columns: terminal, place and batch. How can I check for missing batches?
select * from transactions;
terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825
View 5 Replies
View Related
Aug 23, 2012
I have a table T with columns
Col1 Col2
1 A
2 B
3 C
4 D
[code]..
I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.
So my desired output is:
Col1 Col2 Col3
1 A 1
2 B 1
3 C 1
4 D 1
5 D 2
6 A 2
[code]...
Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.
I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this.
View 3 Replies
View Related
Feb 23, 2010
I am pulling information from a view to get the current results and placing them into a cursor which then inserts the results into a temp table.
Current Results:
JOB_NAMESCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00Failed
JOB_1 02/22/2010 13:25:00Failed
JOB_1 02/21/2010 13:25:00Failed
JOB_1 02/19/2010 13:25:00Failed
JOB_2 02/21/2010 13:25:00Failed
JOB_2 02/18/2010 13:25:00Failed
JOB_2 02/17/2010 13:25:00Error
JOB_2 02/15/2010 13:25:00Error
Needed Results:
JOB_NAMESCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00Failed
JOB_1 02/22/2010 13:25:00Failed
JOB_1 02/21/2010 13:25:00Failed
JOB_2 02/18/2010 13:25:00Failed
JOB_2 02/17/2010 13:25:00Error
View 5 Replies
View Related
Nov 14, 2010
i want to restrict the user if he/she enters any 3 consecutive sequence of numbers,characters,alphanumerics and special characters for example aaa, aAa, @@@, ---- , 111, 123 are not valid.
valid sequences are a1w,?1A,aa1,WW2,78a,-#a
i want to show the invalid sequence in a single query using regular expression function. suppose for example if user enters aaa,$$$,123 then the query output is aaa,$$$,123.
i have written two different queries for that but i want a single query
SELECT REGEXP_SUBSTR('EEE','([a-z])\1\1',1,1,'i') FROM DUAL;
SELECT REGEXP_SUBSTR('111','([0-9])\1\1',1,1,'i') FROM DUAL;
SELECT REGEXP_SUBSTR('@@@','([^-$])\1\1',1,1,'i') FROM DUAL; -it is not checking for -(hypen) characters
any 3 consecutive characters from key board.
View 2 Replies
View Related
Aug 23, 2007
SELECT * FROM table WHERE id = $x
$x being a range of non-consecutive values like so:
1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?
View 3 Replies
View Related
