SQL & PL/SQL :: Get Consecutive Days?
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
ADVERTISEMENT
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
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
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
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
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
Mar 23, 2010
SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days
FROM tmsh_timesheet a
INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id
INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id
INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
[Code]...
this query results
EMP_IDCAL_IDTSDATE
048283404/10/2010
048283502/11/2010
048283503/11/2010
048283504/11/2010
048283508/11/2010
i need the ts date to be in like this
04/10/2010
02/11/2010 - 04/11/2010
08/11/2010
View 16 Replies
View Related
Apr 14, 2011
create table test_table(
rn varchar2(10),
col1 varchar2(10),
col2 varchar2(10),
[code]...
I want update col1 whis is null to max(col1) ++ in a row, order by cr_date like
1,1,20110102
2,2,20110101
3,null,20110105 => 3,5,20110105 because this row is after 20110103
4,3,20110104
5,null,20110103 => 5,4,20110103 because this row is before 20110105
update test_table
set col1 = (select max(col1) from test_table) + rownum
where col1 is null;
this gives ora-00933
View 6 Replies
View Related
Jun 4, 2013
version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
i want to ,remove consecutive occurance from string
Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL'
to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method
WITH T
[Code]....
View 8 Replies
View Related
Oct 23, 2011
provide SQL query (not PL/SQL block) for the following. I have data in table as shown below
Serial No value
========= ========
123
2-99
3-99
4-99
534
656
77
88
990
1065
1167
12-99
13-99
14-99
15-99
16-99
17-99
182
1945
206
The output of the SQL will be the largest consecutive series of the value -99 is 6 and total count of the value -99 is 9
View 21 Replies
View Related
Apr 7, 2011
how to query a list to see if a person had events in consectutive months within the past year. We call a person a LongTermResident if they had a review in any two consectutive months within a reporting period. I wrote a function isResidentLongTerm, passing in FacilityID, ResidentID, ReportPeriodStart, and ReportPeriodStop and returning a 'Y' or 'N'. It works, but the performance is slow.
So if I have a list of reviewers, facilities, reviewees I want to select only those SNF/NF residents who have had routine reviews in any two consectutive months at the same facility.
This is my query:
select ConsultantID, ResidentID
from (
select distinct ConsultantID, FacilityID, ResidentID
from Reviews
where BedType = 17820 -- SNF/NF bed
and ReviewType = 17474 -- routine review
)
where isResidentLongTerm( FacilityID, ResidentID, :startDate, :stopDate ) = 'Y'
View 1 Replies
View Related
Apr 14, 2012
I have a table with numbers like this in a column
sr no. value
1 17
2 0
3 0
4 38
5 32
6 24
7 12
8 51
9 42
10 1
11 1
12 0
13 0
14 1
15 1
.
.
.
so on....
I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.
For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.
my desired output is:
sr no. value Rank
1 17 1
2 0 1
3 0 1
4 38 2
5 32 2
6 24 2
7 12 2
8 51 3
9 42 3
10 1 3
11 1 3
12 0 3
13 0 3
14 1 4
15 1 4
.
.
.
View 3 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
Jul 6, 2012
oracle version 11gr2.
In the below sample data column a,b if there is Two (2) consecutive numbers are transposed in column B from A it should return string 'true'.
with t as
(
select '123456789' a ,'123476581' b from dual
union all
select '123456789' ,'123465789' from dual
union all
[Code]....
i need a output as below.
a b val
123456789 123476581 FALSE
123456789 123465789 TRUE
332211 332121 TRUE
54321 54312 TRUE
78901 79801 TRUE
65432 63452 FALSE
View 29 Replies
View Related
Aug 16, 2011
How to Check for party names contains standalone four length keywords of consecutive letters(both upper and lower case)
For.e.g. "VMWARE ABCD"
"Jack XWyz jon"
output shoud be: abcd
ABCD,
XWYZ,
like this i want to remove the consecutive characters.
View 39 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
