SQL & PL/SQL :: Query Using Analytical Functions-date Range
Nov 30, 2010
I have a table which has the attached data.
Sample data is here
LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........
I am trying to do a report like this.
Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM
Output should be
11/28/2010 25 11 49 27
11/29/2010...
I am using analytical function to do this, It throws an error range cannot be used for dates.
View 5 Replies
ADVERTISEMENT
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
Dec 6, 2012
Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .
For Ex: Departments wise count including all employees record :
SQL> select count(*) over(partition by deptno) dept_Count, ce.*
2 from scott.emp ce
3 order by deptno, job;
DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10
3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
3 7839 KING PRESIDENT 11/17/1981 5000.00 10
5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
[code]....
View 4 Replies
View Related
Sep 27, 2012
I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:
Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2
I split this out to values per year like this
Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2
Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?
I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?
View 2 Replies
View Related
Jan 3, 2012
what is the purpose of over and partition by keywords in analytical functions
View 3 Replies
View Related
Feb 16, 2013
i have employee table i want to update salary with all employee 5 percent
View 4 Replies
View Related
Aug 14, 2013
I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:
/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~';
v_Replace1:=2013;
v_Replace2:=7;
pos := 1;
WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP
IF (pos = 1) THEN
[code]....
Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"
But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"
View 5 Replies
View Related
Dec 26, 2012
Getting problem with below query.
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error starting at line 1 in command:
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
View 1 Replies
View Related
Apr 27, 2013
create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));
insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
Result :
date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10
[Code]....
View 5 Replies
View Related
Aug 16, 2012
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
STARTDT_ ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012
View 6 Replies
View Related
Aug 10, 2013
I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....
View 17 Replies
View Related
Nov 18, 2011
We've got a query which returns one row, but uses an IN statement. The IN statement links to more than one row in the subquery. When we use a combination of DISTINCT and an ANALYTICAL sum, the sum total is multiplied by the number of rows in the sub query. Remove the DISTINCT and we get a single value.
A simplified example of the problem is below.
I can't see how a query which returns a single row then returns multiple values with the addition of a DISTINCT. Removing the analytical sum also provides a single row, but we need this in the actual query we're running. So it seems to be some combination of DISTINCT, ANALYTICAL SUM and IN query is causing multiple values to be returned.
CREATE TABLE go_test_distinct1
(gtd_value NUMBER);
-- Three identical values
-- To replicate the three identical values returned by
[code].....
View 12 Replies
View Related
Aug 8, 2013
select to_date('28-FEB-2013') - TO_DATE('01-FEB-2013') FROM DUAL
gives me 27 days, what should I do to get 28 ? meaning include both the start and end dates in the range ?
View 3 Replies
View Related
Jan 22, 2013
I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.
SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'
[Code]...
This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?
View 9 Replies
View Related
Dec 3, 2012
Trying to understand how Oracle DATE works and how it differs to TO_DATE in the below manner...
SELECT DATE '2012-10-25' FROM DUAL;
SELECT TO_DATE('2012-10-25','YYYY-MM-DD') FROM DUAL;
I've had a look through the Oracle Docs but can't find much on this. Basically I want to know how safe the first option is and if it does the same as the second. Also, where does DATE get the Format String from (NLS setting?). Can it be employed to compare a literal with a date that has a TIME portion set to something other than 12:00:00?
View 2 Replies
View Related
Jan 20, 2011
the data in the column is in below format
---------------------
2/10/2009 9:28:41 PM
mm/dd/yyyy
my requirement is
--------------------
20090210
YYYYMMDD
View 5 Replies
View Related
Jan 2, 2013
writing a manual date validation function without using inbuilt oracle functions.I am planning to write this function in PL/SQL.
View 17 Replies
View Related
Feb 4, 2013
I have a cost data for effective date range as below.
MODEL_NOTIER_COSTEFFECTIVE_START_DATEEFFECTIVE_END_DATE
I3 3770 265 2/3/2013 3/31/2013
I3 3770 269 4/1/2013 5/4/2013
This data needs to be represented in a SQL report as below. The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.
MODEL NOFEB FY13MAR FY13APR FY13MAY FY13
I3 3770 265 265 269 269
Currently I am using a procedural logic to populate data into a different table in the above format. Is there any method to do with a single SQL using PIVOT. Below given is the table structure and Insert scripts.
CREATE TABLE ITEM_TAG(MODEL_NO VARCHAR2(50), TIER_COST NUMBER(13,4), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE );
Insert statements
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 265, TO_DATE('02/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 269, TO_DATE('04/01/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
View 10 Replies
View Related
May 7, 2012
I want my user to be restricted for entering duplicate time within two times.
create table asd(dt_frm date,dt_to date);
insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi'));
insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));
now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.
View 8 Replies
View Related
Dec 16, 2010
Other than the obvious to me, where interval partitioning creates partitions as needed. Is there any performance benefit from using interval partitions vs date range partitions.
One draw back for me is that developers do access the partition name in some of their queries, so if I use date range partitioning this will not break their code. I could not find a way to assign a name to a partition when using intervals, is this always system generated or can this be over-ridden.
I am running Oracle 11.1.0.7 soon to be running on 11.2.0.0
View 6 Replies
View Related
Jul 14, 2010
How to display date every monday within date range
DEsc start date end date
XXXX 1/2/2010 31/10/2010
output
date every monday
View 25 Replies
View Related
Apr 5, 2011
I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?
View 2 Replies
View Related
Dec 16, 2011
Problem:
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943)
select strm until 1 week before the start of the next term (4027) (strm would equal 3943)
select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011
select strm
from term
where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943
12/17/2011 - 01/01/2012
select strm
from term
where ?
Output: 3943
01/02/2012 - 5/4/2012
select strm
from term
where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM
(
STRM VARCHAR2(4 BYTE),
DESCR VARCHAR2(20 BYTE),
TERM_BEGIN_DT DATE,
TERM_END_DT DATE
)
Insert into TERM
(STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT)
Values
('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TERM
[code].....
View 2 Replies
View Related
Nov 15, 2010
I need to know the counts(into buckets) for each event_date for a given event_id.
I have 4 buckets that I'm trying to calculate
1. Past (from Today)
2. Today
3. > Today but within 3 days in the future
4. More than 3 days in the future
WITH event_data AS
(SELECT '1' event_id, SYSDATE - 1 event_date
FROM dual
UNION ALL
SELECT '1' event_id, SYSDATE
FROM dual
[code]....
Sample Output:
EVENT_IDPASTTODAYWITHIN_3_DAY_WINDOWOUTSIDE_3_DAY_WINDOW
1 111 1
2 102 0
View 3 Replies
View Related
Nov 1, 2011
I am trying to write part of an SQL where it gives me a count of bookings in any 6 month period made from the first booking.Example of records
enquirynumberenquiryaddresssubjectcodebookingdate
613651 Burberry AvenueBCHR20/10/2008 07:00:00
613801 Burberry AvenueBCHR20/11/2008 07:00:00693021 Barberry AvenueBCHR07/09/2009 07:00:00
I am so far getting 3 as a count result based on SQL below. I want the count to return 2 (because its inside the 6 month range):
SELECT
ce1.enquirynumber,
ce1.enquiryaddress,
es1.subjectcode,
b1.bookingdate,
(SELECT count(b2.bookingdate)
[code]....
View 2 Replies
View Related
May 16, 2013
I would like to design a date range query where the beginning of the week is always sunday and the end of the week is always monday.
View 3 Replies
View Related
Nov 28, 2012
I'm trying to compare a date and time within a certain range.
If the sysdate date/time range falls within the range of the values within the database tables then show a "Yes", otherwise, show a "No". The date works but the time doesn't seem to work. Maybe you can't use a "between" operator for time?
CREATE TABLE REGISTRATION
(
EARLY_REGISTRATION_START_DATE DATE,
EARLY_REGISTRATION_END_DATE DATE,
EARLY_REGISTRATION_START_TIME DATE,
EARLY_REGISTRATION_END_TIME DATE
[code]....
View 5 Replies
View Related
Jul 7, 2012
I have a table which has 2 range partitions on a date column currently.
CREATE TABLE TABLEA (
RUN_TIME INT NOT NULL
)
PARTITION BY RANGE (RUN_TIME)
(
PARTITION DATAONE VALUES LESS THAN (20110101000000) TABLESPACE SPACE1
PARTITION DATATWO VALUES LESS THAN (MAXVALUE) TABLESPACE SPACE2
);
I am planning to drop one partition i.e DATAONE. So table will have one partition left for MAXVALUE. Does it make sense to have a partition with MAXVALUE? Isn't it same as TABLEA in terms of number of records? TABLEA is also in tablespace SPACE2. Should i remove partition DATATWO also? If i have to what is the best way to move all DATATWO records to base table TABLEA?
View 6 Replies
View Related
Feb 3, 2011
We have a table with timestamp column and having millions of records.We want to create a materialized view or query, which can give count based
-on some group by columns from table and
-group by on condition (if count > 1000) and
-group by on condition (if timestamp range for that group is > 1hr)
View 4 Replies
View Related
May 8, 2013
I want to select a specific date/time range in a query. I want to select from 6 AM yesterday through 6 AM today. I know that CURRENT_DATE - 1 will give me yesterday, and I can search between that and the current_date. However, how do I incorporate the specific time in the query?
View 4 Replies
View Related
