SQL & PL/SQL :: Query To Find Continuous Holidays From Holiday Master
Nov 9, 2011
I've a holiday master in which date of holiday is present. It also contain all Sundays.
I've a process which is not executed on Sundays and Holidays. After holidays it must process all data.
E.g.
01-NOV-2011 --Holiday
02-NOV-2011 --Holiday
03-NOV-2011 --Sunday
On 31-OCT-2011 Process is executed for same day data. Since 1,2-Nov are holidays and 3-nov is Sunday. The process will be executed on 04-Nov-2011 for data between 01-Nov-2011 to 04-Nov-2011. On 05-nov-2011 process will be executed for same day data. Then the process will be executed on 07-nov for 6,7-nov data, because 6-nov was Sunday.
It's like
SELECT *
FROM TABLE_NAME
WHERE BUSINESS_DATE BETWEEN LV_START_DATE AND LV_END_DATE ;
How to select this LV_START_DATE, LV_END_DATE i.e. MIN & MAX date for a topmost continuous range less than the business date.
Sample Data.
.
.
01-NOV-2011
02-NOV-2011
03-NOV-2011
06-NOV-2011
.
.
View 6 Replies
ADVERTISEMENT
Jan 16, 2013
I need to find a query to create a gap in continuous series. So that i can fill this gap with a new record.
Attached here is the test sql.
Sample Data is
test@orcl>select * from sales_mst;
NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N
[code]...
10 rows selected.
test@orcl>select * from sales_dtl;
NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1249 12 55 450
[code]...
16 rows selected.
Required Out Put
test@orcl>select * from sales_mst;
NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N
1251 08-JAN-13 N
1250 08-JAN-13 R
1253 08-JAN-13 N
1252 09-JAN-13 R
1254 09-JAN-13 N
1255 09-JAN-13 N
10 rows selected.
test@orcl>select * from sales_dtl;
NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1251 12 55 450
1251 11 45 600
[code]...
16 rows selected.
Update or move only those records which have Type 'N'. I have tried the following query but it is giving me an error.
test@orcl>ALTER TABLE
sales_dtl
DISABLE CONSTRAINT
fk_sales_dtl_no;
test@orcl>update sales_mst set no=no+1 where type='N' and vdate > '07-JAN-13';
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_SALES_MST_NO) violated
View 15 Replies
View Related
Nov 26, 2006
For e.g.
if my data is as follows
EX_START_DATE EX_END_DATE EX_CHARGE
5/1/2005 5/5/2005 11:59:59 PM 10
5/6/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20
the record
5/6/2005 5/7/2005 11:59:59 PM 10
is in continuation for
5/1/2005 5/5/2005 11:59:59 PM 10 with the same ex_charge
so i need the output as
5/1/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20
and the remaining rows as it is
(Note :date in mm/dd/yyyy format)
View 6 Replies
View Related
Dec 12, 2012
i have to get holidays count including sturday and sunday using HOLIDAYDATEMASTER if between stardate and enddate having any leave below is my procedure if run this procedure
startdate:12/21/2012 -- mm/dd/yyyy endate:12/26/2012 iam getting result=2 only actual not
included 25 holidaydate(22 sat ,23 sun)
CREATE TABLE HOLIDAYDATEMASTER(HOLIDAYDATEID NUMBER not null,HOLIDAYNAME VARCHAR2(100),
HOLIDAYDATE DATE,HOLIDAYYEAR NUMBER(10));
insert into HOLIDAYDATEMASTER (HOLIDAYDATEID, HOLIDAYNAME, TO_CHAR(HOLIDAYDATE,'DD-MON-YY, HOLIDAYDATE, HOLIDAYYEAR)
values (461, 'Christmas Day', '25-DEC-2012', to_date('25-12-2012', 'dd-mm-yyyy'), 2012);
CREATE OR REPLACE PROCEDURE p_Holiday_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE,OC_DUR OUT NUMBER)
IS
[Code]....
View 5 Replies
View Related
Feb 20, 2012
I am trying to query a form having two data blocks. one is a master and the other is a detail block. the problem is when i insert data in the form, save it, and press enter query key, the data in the master block fields are still there. while the data in the detail block field are cleared and i can insert values. how to clear data from master block field so i can query
View 2 Replies
View Related
Jan 18, 2012
I have two table master and detail i want to update both tables with update command in master i want to update voc_date and in detail i want to update item_code in one command but i am not understand how to control this query for example when i use this command
1). update master set voc_date=sysdate it works very well
2). update detail set item_code='12345' it also work very well but i want to update master,detail table in one query
pls guide me with some query example
View 2 Replies
View Related
Sep 16, 2008
I want to remove the master site which is in the multi master replication environment.
I have a doubt here. When I try to suspend the master activity,I need to give the gname.
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'NAGADMIN');
END;
/
Where gname=master group name.But I have 9 master groups in my database. If i give one of the gname present in the master site will suspend the replication of the entire database from the replication.
eg; i am only giving NAGADMIN gname in the suspend activity script.. I have other gnames like, NAGUSER,NAGAUTH, etc....
View 3 Replies
View Related
Feb 23, 2012
I'm looking for a way to get continuous dates as a period in the result.My tabledata contains a date and a status, I want to get continuous dates with the same status value.
That's my tabledata:
select * from (
select to_date('01022012', 'ddmmyyyy') as thedate, 1 as status from dual
union select to_date('02022012', 'ddmmyyyy') as thedate, 1 as status from dual
union select to_date('03022012', 'ddmmyyyy') as thedate, 2 as status from dual
[code]...
THEDATE STATUS
---------- ------
01.02.2012 1
02.02.2012 1
03.02.2012 2
04.02.2012 1
05.02.2012 1
[code]...
And that's the result I am looking for:
select * from (
select to_date('01022012', 'ddmmyyyy') as startdate,
to_date('02022012', 'ddmmyyyy') as enddate from dual
union select to_date('04022012', 'ddmmyyyy') as startdate,
[code]....
View 6 Replies
View Related
May 3, 2013
I have in my database table 2 important date columns: StartDate (Not null) and EndDate(Allowed Null).I want to ensure that all records in the table would always create perfect contiues date ranges with no holes inside.Wor example there may not be records [1-may..1-may, 3-may-...] because there would be a hole [2-may...2-may]. Holes are not allowed.
And overlapping is not allowed, for example [1-may..1-may, 1-may-2may, 3-may-...] is not allowed because overlapping occures on day 1-may. Overlapping and holes are not allowed. But it is allowed that table has no records at all. But all DML manipulations with existing records must ensure that overlapping and holes won't occur.How to write such check? How to ensure that data ranges would stay continous with no holes and no overlaps?
View 10 Replies
View Related
Nov 26, 2010
I am having past data in a table say for one year. each row is having a timestamp column. Now i need to calcute avg of data for every time interval betwwen start date and end date.
time interval can be in minute, hours, days or months..in case of hours,days and months , i need to convert to minutes and group based on that interval.
View 11 Replies
View Related
Aug 6, 2013
I have seen in probably VB or Dot net software being used at some premises that there "formula computation is live and interactive".
What i means that suppose a cashier has made invoice having total of 83$. Mow in the text box for entering amount received, the cashier is about to enter 100 but surprisingly the returned value/remaining value (total invoice - amount received) is being computer LIVE.
This is what happens.
1) The cashier entered 1 and the remaining value instantly showed 82 (83-1)
2) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 73 (83-10)
3) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 17 (83-100)
Hence a continuous updation/changing of the remaining value on every key stroke is observed.
View 3 Replies
View Related
Jan 26, 2011
Is it possible to read streaming continuous binary data from a web site using PL/SQL?
View 8 Replies
View Related
Nov 29, 2011
I have a Invoice Report, i will give the parameters.
From_Inv_No:
To_Inv_No:
From_Inv_Date:
To_Inv_Date:
so i will give 10 invoices with dates in the above parameters to generate the report, actually the report prints in a pre-printed sheet, well it prints the first page correctly but the second page and continuous pages not printing in the exact pre-printed area. example see below;
(Page2)
506176
28/11/2011
(Preprinted text)INVOICE NO :
INVOICE DATE:
how to give spaces between pages actually every pages it should print 3 to 4 inches gap between the first page but it prints just one inch above the preprinted area. how to make it print without gaps. Additional Information: Preprinted sheet Height 17 inches.
Paper Layout Width 8.5
Height 12
Character Mode
Report Width 80
Report Height 72
for header section, main and trailer sections above readings only given.
View 3 Replies
View Related
Jan 2, 2013
We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.
MERGE INTO HDFCMPR.MPR_TB_MPRMASTER
USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */
MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF,
A.ID
[code]....
View 5 Replies
View Related
Apr 16, 2010
from the below string i want to find the count of the "<-" using a single query.
string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"
If not the query, pointer on using SQL functions.
View 3 Replies
View Related
Nov 5, 2010
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
OUTPUT:
CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010
View 6 Replies
View Related
Oct 23, 2012
the below requirement.
I have 2 tables, first one MAIN_T, which contains, user_id and username.
The second table is a relationship table which contains the referal details.
The requirement is to convert the user_id and frnd_id from RELATION_T to their respctive names using MAIN_T.
-- Test Case
CREATE TABLE main_t (user_id NUMBER, username VARCHAR2(10));
CREATE TABLE relation_t(user_id NUMBER, frnd_id NUMBER);
INSERT INTO main_t VALUES(1, 'u1');
INSERT INTO main_t VALUES(2, 'u2');
INSERT INTO main_t VALUES(3, 'u3');
INSERT INTO main_t VALUES(4, 'u4');
INSERT INTO main_t VALUES(5, 'u5');
[Code]....
I have written this query, which gives me the expected output.
SELECT membr.username member_name , frnd.username friend_name
FROM
main_t membr,
main_t frnd,
relation_t rltn
WHERE
rltn.user_id=membr.user_id
AND rltn.frnd_id=frnd.user_id
/
MEMBER_NAM FRIEND_NAM
---------- ----------
u1 u2
u2 u3
u5 u4
u1 u5
4 rows selected.
a better way for this requirement.
View 2 Replies
View Related
Oct 5, 2012
i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.
ID Course
103812 CFH
102968 REP
103812 DFH
102968 DFH
103071 DFH
102968 CFH
View 5 Replies
View Related
Aug 3, 2010
I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.
I need a query that should return the column which is empty but its next and previous column is not empty.
View 13 Replies
View Related
Aug 23, 2010
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
how to use in where condition
View 13 Replies
View Related
Apr 20, 2013
I need to find out that assume i have a table having 2 column
Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets
View 6 Replies
View Related
Jan 16, 2013
i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:
EMP
EMP_1
EMP_2
EMP_3
EMP_4
i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.
View 10 Replies
View Related
Oct 24, 2013
I am working on a form, whose one block is query_find, so on clicking find button it will open another block.the find block contain 4 column and second block contain around 10 column.but the column present in find block are associated to standard tables and the column present in block-2 are from custom tables.there is a query which link those column.
I am not sure where to use that query, so that it will take input from the find block and then populate the result into the block-2.
View 13 Replies
View Related
Jul 4, 2011
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies
View Related
Jul 2, 2013
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
My table looks like this.
Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14
Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query :
----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))
[code].....
Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);
[code].....
View 1 Replies
View Related
Aug 21, 2010
I need to query a table to get the time difference based on different dates.
SAMPLE DATA:
JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
View 2 Replies
View Related
Jul 29, 2011
find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?
SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,
[code]....
View 3 Replies
View Related
Feb 23, 2013
I have a table as follows:
customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012
I Have to write a single query to calculate the sum of credit and sum of debit value separately.
View 8 Replies
View Related
Feb 26, 2013
Refer the table below and provide me the query to find given employee is manager or not.
Refer the table below, emp#100 is manager, emp#300 is not a manager.
<emp> <emp_name> < manager >
100Smith null
200Ram 500
300Shankar 500
400Madhur 200
500Suman 100
600Sundar 100
View 2 Replies
View Related
Sep 3, 2013
I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are
where owner NOT IN ('SYS','SYSTEM') and data_type IN ('CHAR','VARCHAR2','NUMBER')
My query as follows
select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner
View 3 Replies
View Related
