SQL & PL/SQL :: Oracle Query For Days Output

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


ADVERTISEMENT

Oracle - SQL Query For Number Of Days Since Last Backup

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

SQL & PL/SQL :: How To Convert Days Into Weeks And Days (if Any) Oracle 9i

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

SQL & PL/SQL :: Query To List Last 11 Days - Connect By

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

PL/SQL :: Query To Calculate Business Days

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

SQL & PL/SQL :: Query To Get Last 7 Days Of Record From Today Date

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

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 View Related

SQL & PL/SQL :: Query To Retrieve Records In Frequency Of Days?

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

PL/SQL :: Query To Find Number Of Days Across Each Month

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

SQL & PL/SQL :: Query To Return Results From Three Previous Non-consecutive Days?

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

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

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

Function To Find Business Days Between Two Dates - Query Tuning

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

Query To List Employee Number And Amount Of Days Sick

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

Groupby Query - Not Getting Output?

Jun 25, 2013

I have a table with data as follows

Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N

I am trying to get output as follows

Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0

View 2 Replies View Related

SQL & PL/SQL :: Combine Query Output In One Row

Aug 13, 2010

If I have below two sql statment.

select count(*) from table_a;
select count(*) from table_b;

How can I output it in one row together like below

Table_a | Table_b|
10 | 20|

give me detailed example ....

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Output In Particular Format

Mar 8, 2010

I have attached an SQL script.I would like to have the output in the fashion given below -

PERIOD_START_DATEPERIOD_END_DATEEMPLOYEE_NUMBERFULL_NAMELoan AmountLoan TypePay Value Outstanding Amount
1-Jul-0931-Jul-0924200Jonathan48000048004000
1-Jul-0931-Jul-0924200Jonathan32600022702990

the one in bold and underlined is the Column heading.

View 5 Replies View Related

SQL & PL/SQL :: Can Add Description To Output Of Query

May 10, 2011

i have created a table called table2,with only one column as text,

SQL> select regexp_replace(text,'[:]',chr(10)) text from table2;

TEXT
--------------------------------------------------------------------------------
1
amar
11-jan-2011
15000

2
manju
22-feb-2011
20000
and i have to get output like this,
TEXT
--------------------------------------------------------------------------------
ID
1
NAME
amar
DATEOFJOINING
11-jan-2011
SALARY
150000

is it possible to get output like above?

View 20 Replies View Related

Sqlplus Query Output Into File?

Feb 4, 2004

How can I put an Oracle sqlplus query output into file?

SQL> select myfield from userDB where IMSI like '22803%' and rownum < 11;

I would like to put the output in a file, say '/tmp/mydata.dat'

View 6 Replies View Related

SQL & PL/SQL :: Column Renaming From Query Output?

Jul 14, 2010

I want to be able to name a column created from my query.

Query is:

select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId)
from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%'
group by A.OrigRef, A.DisplayName, A.ExtCode
order by A.SusRef

and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?

i.e. I want "column4" to read "CountofCalId"

View 4 Replies View Related

SQL & PL/SQL :: Query Output Into Text File

Mar 9, 2011

I am trying to get query output into textfile.The following procedure is working fine by creating directory as follows in sys user.The output is getting onto text file and it seen on server machine even.My question is I want to see the text file on local machine also instead of everytime connecting to server machine drive.How can I perform that?

create or replace directory INFO_DIR as 'D:Swapna';
grant read, write on directory INFO_DIR to fairpoint;
CREATE OR REPLACE PROCEDURE FAIRPOINT.utl_file_test_write_xls(filename in VARCHAR2 )
IS
output_fileutl_file.file_type;
v_pathVARCHAR2(500);
v_stringVARCHAR2(4000);
v_sqlstrVARCHAR2(2000);
BEGIN
BEGIN
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Query In Vs Exists - Both Are Giving Different Output?

Feb 3, 2012

SQL> select count(*) from emp where empno not in ( select mgr from emp );

COUNT(*)
----------
0

SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );

COUNT(*)
----------
9

I fired above query and I totally confused that why both are giving different output.

View 6 Replies View Related

Forms :: To Output Query In Vertical Way

Dec 27, 2011

i have output query in 1 row, for example

Id Name Salary
100 John 2000

i need to output the same query in vertical way like:

100
John
2000

View 3 Replies View Related

PL/SQL :: Oracle Dates And Working Days

Sep 5, 2012

Is there an Oracle date function that ignores public bank holidays and calculates working days only?

View 4 Replies View Related

SQL & PL/SQL :: Remove Blank Spaces In The Query Output

May 6, 2010

I write a Query to get Deptno wise Employee Names as fallows..

SQL> select max(decode(d.deptno,10,e.ename)) dept10,
2 max(decode(d.deptno,20,e.ename)) dept20,
3 max(decode(d.deptno,30,e.ename)) dept30
4 from emp e,
5 dept d
6 where e.deptno = d.deptno

[code]....

But i don't want to include the blank(null) spaces in the query output.

The Required output should be like this..

DEPT10 DEPT20 DEPT30
------ ------ ------
CLARKADAMSALLEN
KINGFORDBLAKE
MILLERJONESJAMES
SCOTTMARTIN
SMITH TURNER
WARD

View 5 Replies View Related

SQL & PL/SQL :: Print Output Of Query In Excel Format?

Aug 4, 2010

Is it possible to print the output of a sql query in Excel format without using third party tools like Toad, Hora or any front end Application.

View 10 Replies View Related

PL/SQL :: Output Like Using Sql Query Without Using Dense-rank Function?

May 1, 2013

Here table - tac has

row1
-----
X
X
X
B

[code]...

I want ouptut like using sql query with out using dense_rank function,

row1 row2

X 1
X 1
X 1
B 2

[code]...

View 7 Replies View Related

Query Is Returning Output For Both Case Statements

Jun 2, 2011

below query is returning two rows.The thing now happening is the query is returning the output for both the case statements.But what is need only when the first case staement is NULL then it should go for second case.

SELECT DISTINCT CASE
WHEN esc.x1 = Substr(inp.y, 0, 3)
AND esc.x2 = Substr(inp.y, 4, 2)THEN esc.cc
WHEN esc.mcc = Substr(inp.y, 0, 3)
AND esc.mnc = Substr(inp.y, 4, 3)THEN esc.cc
[code]....

I tried using rownum=1 but it filters out valid records.correcting the above query so that if the first case is null then only it should go for second case.

View 2 Replies View Related

Calculate Working Days With Oracle Function

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

How To Set Exact Page Size For Query Output Format

Oct 2, 2012

I have following shell script :-

In our testing DB local Server , we are using following script ... We are connecting Via putty ...

CODEexport ORACLE_SID=testdb
sqlplus /nolog <<eof
conn sys/sys as sysdba

[Code].....

QUOTE 1. How can i set pagesize to get good format Our problem is output format is not good .. we set set pagesize from 0 to 120 .. no improvement .. " Again Re-Installed VMware tools also ... no improvbement ....

2. We are planning to set alert message if "Archive destination crossed 60 % Script automatically will generate alert message via mobile or mail.. JUst we will configure crontab ....

View 6 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved