SQL & PL/SQL :: Selection Based On Date Range

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


ADVERTISEMENT

Application Express :: Highlighting Accordion Based On Selection

Nov 19, 2012

I have created three accordians where there will be sub tabs under each Accordian. There will be two logins. Where when i login with Admin all the 3 accordians will be shown. But in the user logins 1st Accordian will be hidden and only 2nd & 3rd will be shown with their sub tabs.

In order to highlight the tab & also for Accordian to be open based on the selection, i wrote code as follows

$("#R16463536713741058563").accordion('activate',0);

in the page under the Execute When Page Loads region where ('activate',0) means the position of the accordian which will be taken as 0,1,2..etc.But when i login as user then as the 1st accordian will be hidden & the 2nd and 3rd accordians will become 1st & 2nd. So 1st & 2nd accordian wil be consider as 0,1 positions here.

So when i click on the 2nd accordian tabs then the accordian is not opening because in the pages of this accordian tabs i have given the code as

$("#R16463536713741058563").accordion('activate',1);

as the active is 1 so the accordian in position 1 is opening but i want the accordian in 0 position to be opened as i selected the tab under that accordion only.

View 0 Replies View Related

Application Express :: Populate Text Field Based On LOV Selection With AJAX

Apr 24, 2013

I would like to populate a text box based on selection from a LOV. If someone selects a LOV value and then tabs off off of that element, I would like the text box populated from a sql statement based on the LOV value in the predicate.Application Express 4.1.1.00.23

View 1 Replies View Related

PL/SQL :: Retrieve Date Range From 2 Search Input Date Fields

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

PL/SQL :: Date Difference - Include Both Start And End Date In Range

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

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

I need to pick a value based on range like if the range is as below

[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]

Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.

View 2 Replies View Related

SQL & PL/SQL :: Generate Range Based On Start And End Number Dynamically?

Jul 14, 2011

I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.

e.g Start Number : 1 and End Number : 1001.

Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.

Means

Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001

Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.

SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(

[Code].....

I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.

View 22 Replies View Related

PL/SQL :: Group By Data Based On Start And End Time In Range

Mar 28, 2013

formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.

create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));

--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U

[Code]...

--Required output

header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U

View 6 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

SQL & PL/SQL :: Report With Date Range

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

SQL & PL/SQL :: Restriction In Date Range?

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

Date Range Vs Interval Partitioning

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

SQL & PL/SQL :: Display 1st Day Of Week Within Date Range

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

SQL & PL/SQL :: Find Years Between Any Given Date Range

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

SQL & PL/SQL :: Count Data Within Date Range?

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

SQL & PL/SQL :: Counting Records Within A Date Range?

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

PL/SQL :: Date Range Sunday To Monday

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

SQL & PL/SQL :: Replace Date Range In Query?

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

SQL & PL/SQL :: Compare Date And Time Within Certain Range?

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

Range Partitions On A Date Column

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

SQL & PL/SQL :: Group By Count And Date Time Range

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

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

SQL & PL/SQL :: Selecting Specific Date / Time Range?

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

SQL & PL/SQL :: Date Query - Input Year In Specified Range

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

SQL & PL/SQL :: Conditional Date Range Using CASE Statement In WHERE Clause

Mar 28, 2011

Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.
.....
FROM
table
WHERE
CASE
WHEN TO_CHAR(SYSDATE,'AM') = 'AM'
THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE
ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE

View 6 Replies View Related

Forms :: Search Data On Basis Of Date Range?

Sep 13, 2010

i create form on which 3 window, 3 Canvas, 3 blocks are there.

1)first block contain 3 items from_date and to_date(nondatabase)and find button

2)second block contains 3 items segment1, quantity and payment_terms

when i enter 2 dates and click on find button 2nd window open. segment1 and quantity between this 2 dates comes from base table like

select segment1,quantity
from po_lines_all pll, mtl_system_items mts
where pll.ITEM_ID=mts.INVENTORY_ITEM_ID
and creation_date between :FROM_DATE and :TO_DATE

and payment_terms should enter manually

3)2nd block contains summary button when i click on it, it save segment1, quantity, payment_terms and open new window which contain segment1 ,qty, payment_terms and when i click on summary button data comes from custom table which group clause means

suppose

item qty terms
a 2 2days
a 3 2days
b 3 3days

then on third form i shows like this
a 5 2days
b 3 3days

i done all this things but problem is that

suppose once data between 21-aug-2010 and 24-aug-2010 comes on second form , i enter payment_terms and save it. sumaary form also run properly but if another user enter date between 22-aug-2010 and 30-aug-2010 then it will shows data between 21-aug-2010 to 24-aug-2010 from custom table i.e with payment terms as earlier it save and from 25-aug-2010 to 30-aug-2010 it shows data from base table

like

item qty terms date
a 2 2days 21aug
a 3 2days 22aug
b 3 3days 24aug
b 4 NULL(ENTER MANUALLY) 26aug
c 5 NULL 27aug

View 1 Replies View Related

PL/SQL :: Selecting Missing Date Range With Previous Records Value

Jun 8, 2012

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - ProductionSET DEFINE OFF;
[code]....

10 rows selected.I want the output like as follows, all those missing date i need to carry on the last one's number

  NBR_OF_S   NBR_OF_C S_DATE
---------- ---------- ---------
        34         40 01-MAY-12
        27         29 01-APR-12
        27         29 01-MAR-12
        21         23 01-FEB-12
        21         23 01-JAN-12
        21         23 01-DEC-11
[code]....
       
The date value I have created for this sample is monthly, based on the condition the data value I may need to generate weekly also. That's Monthly or weekly either one.

View 9 Replies View Related

PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range

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

SQL & PL/SQL :: Show End Date Based On Next?

May 19, 2010

How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.

For Example:I have the data as shown below,

Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10

I wanted to output as shown below

Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10

View 10 Replies View Related

SQL & PL/SQL :: How To Get Records Based On Max Date

May 18, 2011

I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL

and if it is not null ,i shud get the records of MAX date.

How would i do this. Below is my query,by which i can get records based on either maxdate or Null date

select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........

View 10 Replies View Related







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