SQL & PL/SQL :: Min_date Set Flag 1 And Max Dates Set Flag As 2
Oct 7, 2011
EMP_ID IN_DATE QTY2
1070410/6/11 6:00 AM
1070410/6/11 3:00 PM
1070410/6/11 9:00 PM
1070410/7/11 6:00 AM
1070410/7/11 3:00 PM
1070410/7/11 9:00 PM
1070510/7/11 6:00 AM
1070510/7/11 3:00 PM
1070510/7/11 9:00 PM
following is the table where every employee have 3 records we need to put the flag datewise and employee id wise minimum in_date always be set to 1 and remaining 2 maximum dates for each employee date wise always set to 2 in qty2 field.
Output requirement is as follows:
EMP_ID IN_DATE QTY2
1070410/6/11 6:00 AM 1
1070410/6/11 3:00 PM 2
1070410/6/11 9:00 PM 2
1070410/7/11 6:00 AM 1
1070410/7/11 3:00 PM 2
1070410/7/11 9:00 PM 2
1070510/7/11 6:00 AM 1
1070510/7/11 3:00 PM 2
1070510/7/11 9:00 PM 2
View 4 Replies
ADVERTISEMENT
Dec 22, 2010
I have to run a resource extensive SQL. It utilizes CPU to a great extent. I've created a new user just to run this particular report. I need to ensure that this particular user doesn't uses more CPU.
Hence, I need to generate an alarm (email) only if a threshold is exceeded for a certain period of time (above x% for y minutes),(example more than 80% for 10 min.), so that I can either kill the process or continue running it depending on CPU usage.
Can I do it using scripts or OEM. I couldn't find any specific metrics to do it in latter. (I'd prefer scripts though. )
My system details are as follows:
1. Database: Oracle 10g R2
2. Unix: Sun OS 5.10
View 2 Replies
View Related
Sep 17, 2013
I am not a DBA, we are using Oracle 10g DB for our PLM applictaion Enovia, we are now trying to do 4GT in our windows 2003 server where our DB kept, while reading articles about this. we are informed that /LARGEADDRESSAWARE (IMAGE_FILE_LARGE_ADDRESS_AWARE) flag needed to be set to True in the application (here it is Oracle) to use more than 2GB in the RAM.
So My question is "Whether the corresponding flag is set to TRUE or not in Oracle 10g"? Is there any way to check that?"
View 1 Replies
View Related
Jun 7, 2012
I have records like the following
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
View 3 Replies
View Related
Oct 18, 2012
I have a scenario, where I need to find the bucket values with the flag as "Y". The problem is i have 5 buckets
Bkt1 Bkt2 Bkt3 Bkt4 Bkt5 Flag
------------------------------------------
1000 500 230 100 677 Y
789 100 122 666 888 N
783 110 142 166 788 N
781 110 112 616 988 Y
709 130 121 661 878 N
Here I need to write a SQL query in Oracle 10g fetching the respective bucket value(for all the 5 buckets) along with the flag as "Y" and more importantly should have the bucket name as well.This is needed as in the UI we need to display the respective bkt value which is "Y" as bold.
So in the final List I need to have the VO which should contain the respective bucket name, respective value and flag as "Y"
Some thing like this I need to get in my final ArrayList in java
Bkt1 1000 Y
Bkt1 781 Y
Bkt2 500 Y
Bkt2 110 Y
Bkt3 230 Y
Bkt3 112 Y
Bkt4 100 Y
Bkt4 616 Y
Bkt5 677 Y
Bkt5 988 Y
how we can do it using the SQL query.
View 16 Replies
View Related
Jan 29, 2011
I am working on an archiving strategy. I want to roll off transactions that are older than seven days, but only if they are flagged as Completed. The numbers of transactions are very large so this is a worthwhile venture.
The only strategy I have been able to come up with so far is to partiton on date. Then when 7 days comes up, sweep the about-to-be archived day for the few remaining not Completed transactions, put those into a new table (a new version of this partiton) and switch partitions. Each day I do this until the older parititions are empty.
View 7 Replies
View Related
Mar 5, 2008
I have a SQL link table called student_monitor containing the follwing attributes and e.g. data
student_id, Class_id, Predicted_Grade, Actual_Grade
1 1 A C
2 1 B B
3 1 C B
I need to be able to flag which students are under achieving on their target grades and dont know how to do this or the best way to do this as i cannot calculate the diff between a char?
View 1 Replies
View Related
Jul 29, 2013
I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.
The table has fields like:
category - string,
item - string,
creation_date - string ('YYYYMMDDHHMI'),
complete_flag - boolean,
etc.
Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?
View 8 Replies
View Related
Dec 27, 2012
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....
View 17 Replies
View Related
Jan 9, 2012
The stand alone stored procedure has 2 parameter, an IN and OUT...
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,
tablename OUT VARCHAR2)
This procedure has multiple inner blocks. Intention is to control execution of each of the inner blocks basing on the value of flag field obtained from a "processlogtable". This processlog table has structure as below.
jobname varchar2(100)
controldate (date)
controlflag varchar2(1)
I have the below code snippet at the beginning of each inner block that checks value of the flag and then proceeds with execution of that block. The intent is to avoid redundant call of a block that has successfully executed first time. That is, once a specific inner block fails for some reason, the re-execution of the stored procedure should AVOID re-executing the PRIOR successed steps.
I've the code set up as below, but the prior successed block(s) code gets re-executed again once the procedure is re-executed after a failure.
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,tablename OUT VARCHAR2)
CURSOR c_missingtablename
IS
SELECT datatablename
FROM (
SELECT UPPER(datatablename ) TABLE
FROM
WHERE datatableName IN ('Aaa','BbB','CcC');
[code]....
View 5 Replies
View Related
Jul 15, 2013
I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?
set heading off
set feedback off
set term off
set long 1000000
set longchunksize 500000
set line 32767
set trimspool on
set pagesize 50000
spool file.txt
@--this is my select statement.
spool off
exit
View 1 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
Oct 22, 2003
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003
12/3/2003
12/4/2003
12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
View 14 Replies
View Related
Aug 5, 2010
I am newbie to oracle and using oracle 10g as database. I want to get dates between two dates .... let me give an example
suppose a user enters 1-Aug-2010 - 31-Aug-2010 , so i should get all dates in between from date and to date.
something like "select date..or whatever from dual where date between 1-Aug-2010 and 31-Aug-2010 " like this type or other.
View 2 Replies
View Related
Nov 9, 2010
I want the dates between 2 dates. Suppose i give the dates 01-jan-2010 and 31-jan-2010 and i need the following output.
01-jan-2010
02-jan-2010
03-jan-2010
.
.
.
.
31-jan-2010
View 11 Replies
View Related
Sep 17, 2008
I got all the month_numbers when i did this
[/b]select distinct t.f_month_number
from time_dim t
where
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)[b]
but when i add
select distinct t.f_month_number,p.start_date,round(replace(p.total_sal,',','')/12,2) as Monthly_sal
from time_dim t, employee p
where
t.f_date = p.start_date and
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)
i got only one month value.
View 6 Replies
View Related
Jan 23, 2013
I am looking to subtract two columns and get the difference.
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number
View 9 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
Dec 7, 2012
I am looking for a query to find out minimun and maximum rates of an item with corresponding dates. findout a query to get the required result.
Here is sample data
CREATE TABLE scott.item_rate
(
code VARCHAR2(3),
rate NUMBER(10,4),
vdate DATE
);
[Code]..
COD RATE VDATE
--- ---------- ---------
001 108.97 25-MAY-12
001 108.97 07-APR-12
001 105 05-DEC-12
001 105 11-OCT-12
001 91 02-JUL-10
001 1 05-JUL-10
001 1 31-AUG-10
7 rows selected.
The required result is
CODE MIN_RATE MIN_DATE MAX_RATE MAX_DATE
001 1 05-JUL-10 108.97 25-MAY-12
View 11 Replies
View Related
Jul 13, 2010
I need to aggregate the data based the two dates criteria.
I have two tables
1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on
Output:
Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010
View 14 Replies
View Related
Dec 21, 2011
below is the table and data
create table bday (name varchar2(30),bdate varchar2(10));
insert into bday values('jeffery','0110');
insert into bday values('boss','1231');
insert into bday values('raj','1225');
[Code]...
the BDATE column is in the form "mmdd".
I just want to select the name between 2 dates ( not including years). lets say between sysdate and sysdate+20, i.e
select to_char(sysdate,'dd-mon'),to_char(sysdate+25,'dd-mon') from dual;
TO_CHAR(SYSDATE,'DD-MON') TO_CHAR(SYSDATE+25,'DD-MON')
21-dec 15-jan
but when I run the below query, it is not showing me 'january' data
select name from bday
where to_date(bdate,'mm/dd') between sysdate and sysdate+25;
o/p comes as:
NAME BDATE
boss 1231
raj 1225
but the actual o/p should be:
NAME BDATE
boss 1231
raj 1225
jeffery 0110
it seems to me that because of year change the rows are not displayed.how to handle this in single SQL
View 8 Replies
View Related
Sep 24, 2012
for the below table
create table RM_TR_INVESTMENT
(
AS_ON_DT DATE not null,
EXP_ID NUMBER(10) not null,
BO_REF_ID VARCHAR2(30),
FO_REF_ID VARCHAR2(30),
BK_PRODUCT_ID VARCHAR2(20),
BK_INV_TYPE_ID VARCHAR2(20),
[code]....
I want to find the sum of NP_AMT_CCY where difference between Start_Dt and Maturity_Dt >= 14 days and <=28 days.How can I build the query for the above statement ?
View 6 Replies
View Related
Jan 28, 2013
I'm trying to use a PIVOT on the following data set:
ID STATUS_DESC PAY_STATUS PAID_DATE TRANSACTION_TYPE TRANSACTION_DESC DEBIT TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 Card Payment Payment 2 349 349
9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 Card Payment Payment 1 100 100
However I'm still getting two rows as per the below. Ideally all data should be on a single row.
ID STATUS_DESC PAY_STATUS PAYMENT_1_DATE PAYMENT_1_AMT PAYMENT_2_DATE PAYMENT_2_AMT TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 100 100
9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 349 349
I have constructed my pivot using the following on the outer select:
PIVOT (MAX (insert_timestamp) AS paid_date
,SUM (debit) AS amt
FOR transaction_desc IN ('Payment 1' AS payment_1,
'Payment 2' AS payment_2)) ;
I've used MAX to pivot the date and also tried using NVL on the insert_timestamp but still no luck.
View 7 Replies
View Related
Jun 25, 2012
I'm trying to run a report that has a moving date and I need to find data that's within 12 months of that certain date.
So for example... customers come in everyday all year long. I wanted to find the number of unique customers in a year. But the year is moving... So 1 year from 1/15/2011 is 1/14/2012. And 1 year from 1/16/2011 is 1/15/12. So I had something like this but doesn't quite work..
SELECT ...
NVL(COUNT(DISTINCT CASE WHEN TX.DATE_OF_FIRST_VISIT BETWEEN TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY') AND ADD_MONTHS(TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY'),12) THEN (TX.CLINIC_ID||TX.PATIENT_UNIQUE_ID)END),0) AS "YEAR_1"
View 10 Replies
View Related
Mar 10, 2012
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
View 4 Replies
View Related
Mar 9, 2013
ii have written a query like thi:
select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags"
from p_in where order_date between '1-apr-11 and '31-mar-12'
group by to_char(order_date,'mon-yyyy')
the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE
View 1 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
Apr 26, 2012
I need to store a date that will always be x years before the current date. Is doing something like
select sysdate -x from dual
Going to result in any weird issues down the road? Also, because I haven't explicitly specified the date, when queried, will the value always be sysdate - x (What I want to happen) as opposed to dateRowWasCreated - x?
View 1 Replies
View Related
Sep 21, 2004
I am wondering how can I select data between two dates (ie from 08/12/2004 to today?)
Name DateS
Bob 08/11/2004
Tom 08/12/2004
Bill 09/21/2004 <-- today
return
Tom 08/12/2004
Bill 09/21/2004
View 4 Replies
View Related
