SQL & PL/SQL :: Which Projects Stored In Tables Not Updated On Consecutive Days
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
ADVERTISEMENT
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
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
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
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
Oct 7, 2012
I tried to find out recently updated table list. I couldn't find anything.
CODE>> JUST SAMPLE TRANSACTIONS >>
SQL> conn sam
Enter password:
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$y1TXhcjGApvgQAB/AQBOpA==$0 TABLE
TAB1 TABLE
[code]........
View 1 Replies
View Related
Aug 4, 2011
i want a query/function/procedure to check recently updated columns/tables in a database...
View -1 Replies
View Related
Jan 9, 2013
I am using Oracle XE with SQL Developer to teach a course on databases.
The students complete a number of small projects. How can a student separate the projects so as to be able to open in SQL Developer only the tables pertaining to a specific project? Ideally, the student would be able to assign the same name to two tables that belong to different projects.
I know that this can be done by naming conventions and filtering, but this is awkward.
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
Jul 22, 2010
Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.
Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?
View 5 Replies
View Related
Feb 22, 2012
I want to create a stored procedure where tables of two different databases are to be used.
View 4 Replies
View Related
Nov 20, 2012
if an API is available for deleting Bank statements stored in tables CE_STATEMENT_HEADERS and CE_STATEMENT_LINES.
View 3 Replies
View Related
Apr 14, 2009
I have following tables:
EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)
Create a view to show employee names, age and total number of projects they are assigned to
View 2 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
Oct 14, 2010
This is my first time running a stored procedure. The procedure is already written.
We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.
View 8 Replies
View Related
Jul 24, 2013
I have table A.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 56 USD AB 676
2 b 45 INR AB 143
3 c 32 JPY BA 200
and Table B.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 33 USD OB 676
9 b 21 NZD AC 143
8 c 19 GBP CA 200
build a Stored Proc which compares sepecific fields in the 2 tables and reports into a new table as:
A.Security.sec_aliasB.Security.sec_aliastable fieldA-valueB-value Compare
676 676 Security Currency USD USD Match
143 143 Security Currency INR NZD Not-Match
Fields to be compared are:
Quantity_type
Currency
Sec_type
The join between the tables A and B can be made on Sec_alias.Also, this is just a single pair of tables. Further I need to compare 5 more pairs of tables after this and report out of the new table.
View 23 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
