SQL & PL/SQL :: Value For Closest Or Equal Date

Aug 23, 2013

I have two tables e.g. test_bb and test_sub

I would like to select test_sub.price as per the below conditions. If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price

If there is no match then as above find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.

e.g.

select * from test_sub;
VEHICLE VALUE_DAT PRICE
---------- --------- ----------
10 12-APR-12 2
10 08-JAN-10 4
10 14-APR-14 6
10 06-AUG-47 8
20 24-JAN-14 10
20 20-FEB-06 12
20 18-FEB-04 14
20 28-FEB-06 16
20 22-DEC-07 19
8 rows selected.

select * from test_bb;
VEHICLE VALUE_DAT
---------- ---------
10 12-APR-12
10 10-FEB-10
20 24-JAN-14
20 22-FEB-08

Required output:
VEHICLE PRICE VALUE_DAT
---------- ---------- ---------
10 2 12-APR-12
10 4 08-JAN-10
20 10 24-JAN-14
20 19 22-DEC-07

create table test_bb(vehicle number, value_date date);

begin
insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy'));
insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy'));
insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy'));
insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy'));
end;
/

create table test_sub(vehicle number, value_date date,price number);

begin
insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2);
insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4);
insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6);
insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8);
insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10);
insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12);
insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14);
insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16);
insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19);
end;
/

I could write as below but I would like to know if there is a better way of doing it.

select bb.vehicle
,sub.price
,bb.value_date
from test_bb bb
,test_sub sub
where bb.vehicle=sub.vehicle
[code].........

View 8 Replies


ADVERTISEMENT

PL/SQL :: Get The Closest Max Date

Jun 27, 2012

I have a table named UFM with columns :

trans_ID,Plz_id,clas,trans_date
31106952318303-     005110-2-     6/24/2012 5:56:07.800000 AM
33706952318401-     005110-     2-     6/24/2012 5:56:23.873000 AM
34806952318304-     005110-     2-     6/24/2012 5:56:25.650000 AM
35406952318442-     005110-     2-     6/24/2012 5:59:20.260000 AM

[Code]...

I have another table named facility with columns:

plz_id, Eff_date
005110-     1/10/2012 10:00:00.000000 PM
005110-     1/11/2012 12:00:00.000000 AM
005110-     1/13/2012 12:00:00.000000 PM
005110-     6/24/2012 6:00:00.000000 AM

I want all and records from UFM joining to facility table, i need columns trans_ID,Plz_id,clas,trans_date,Eff_date in my output.

the output should look like ,

trans_ID,Plz_id,clas,trans_date,Eff_date
31106952318303-     005110- 2-     6/24/2012 5:56:07.800000 AM-1/13/2012 12:00:00.000000 PM
33706952318401-     005110-     2-     6/24/2012 5:56:23.873000 AM-1/13/2012 12:00:00.000000 PM
34806952318304-     005110-     2-     6/24/2012 5:56:25.650000 AM-1/13/2012 12:00:00.000000 PM
35406952318442-     005110-     2-     6/24/2012 5:59:20.260000 AM-1/13/2012 12:00:00.000000 PM

[Code]....

for a transaction that occured on 6/24/2012 after 6:00 AM the eff_date should be 6/24/2012 6:00:00.000000 AM and for a transaction before 6:00 AM on 6/24/2012 the eff_date should be 1/13/2012 12:00:00.000000 PM. and the transactions that occured before 1/13/2012 12:00:00.000000 PM the eff_date should be 1/11/2012 12:00:00.000000 AM and so on.

View 4 Replies View Related

PL/SQL :: Closest Date From A Group Of Dates

Dec 19, 2012

I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.

I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.

Here is an example of dates:

Visit Date      Actual Date
==========================
01-APR-09     19-MAR-10
16-NOV-09     19-MAR-10
17-MAR-10     19-MAR-10
21-MAR-10     19-MAR-10
04-APR-11     19-MAR-10
15-JUN-11     19-MAR-10
19-SEP-11     19-MAR-10
24-FEB-12     19-MAR-10

The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.

View 4 Replies View Related

PL/SQL :: Closest Date Missing Data

Jul 19, 2012

My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:

SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS

[Code]...

Output:

p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------

Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Problem:

For 1st and 2nd row,

I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :

Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.

It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .

let me know how to achieve this.

View 4 Replies View Related

Set View To Show Records Equal To Date?

Sep 14, 2007

I have set up a view that pulls news & events records.

SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.category, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType
FROM(so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID

[Code].....

For events I want only the events that have a p.startDate equal to today. I have tried setting that line up a few different ways.

p.startDate = sysDate didn't provide any records for events
p.startDate = TO_CHAR(sysDate, 'MM/DD/YYYY') freaked the system out and nothing appeared for either parts of the union

how to output only events that are for today?

View 3 Replies View Related

SQL & PL/SQL :: Selection Of Next Closest Day

May 5, 2011

I am trying to develop an ongoing interview schedule.

interviews may be held on FRIDAY, SATURDAY, TUESDAY or any other day or Days in a every week selected by the user and also at some specific time.

we need to schedule the closest comming day based on Application date.

for example: today is WEDNESDAY and our schedule interview day is FRIDAY and SUNDAY. If one application is received today we need to schedule interview on FRIDAY because this is the nearest/closest day of WEDNESDAY. and if the application is received on Saturday then of course Sunday will be scheduled.

if Application is received on same day e.g. FRIDAY is scheduled and we receive Application on FRIDAY then comes the concept of time. interview is schedules on FRIDAY 11:30 AM.

if application is received on before 11:30AM, the same day will be scheduled. If application is received after 11:30 AM then of course next closest day will be scheduled.

View 1 Replies View Related

SQL & PL/SQL :: How To Find Next Closest Number In Series

Jan 21, 2012

i have a data something like

010030
110495
210960
311425

[code]...

and user enters the figure like 13025now i would like to find the closest next value to the number entered by user, which is 713285 .how can i get this result, i have search a lot on Internet and i found some analytic functions can work out. but don't know how ..

View 14 Replies View Related

SQL & PL/SQL :: How To Find Nearest / Closest String For A Column Data

Jun 1, 2011

Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.

Miss
Mississ
Mississipp

I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)

If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.

create table names (name varchar2(20));

insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;

View 2 Replies View Related

ORA-01476 Divisor Equal To 0

Oct 29, 2011

I am working on an application that allows a user to create a KPI formula, and the app dynamically creates a view for this formula. However as we started using it we have found that we are encountering the ORA-10476 (divisor is equal to zero) often. I have read about options of using decode or case to prevent this but it doesn't cover all our options. As a result of the fact that the user can create any equation he wants , and it can be as complicated as they want ( 2 examples:

A/(B/C-D/E) - If C or E or (B/C-D/E) are 0 the whole select will fall
A/(B-C/(D-F)) - if (D-F) or ((B-C/(D-F)) is 0 then again the select will fall.

I have seen that in MS SQL they have an option of arithabort which allows the database to return the rows that don't fall under the "divisor by 0".

either by setting something similar to arithabort, or maybe a procedure to check an equation and return the arithmetical steps in order that athey are performed?

View 2 Replies View Related

SQL & PL/SQL :: Divisor Is Equal To Zero Error?

May 26, 2010

The below code gives me error (ORA-01476: divisor is equal to zero)

SELECT
((COUNT(DECODE(SUBSTR(A.ASSETNUM,6,3),'ACS','ACS',0,null))/COUNT(DECODE(SUBSTR(A.PMNUM,1,3),'ACS','ACS',0,null)))*100)
FROM WORKORDER A
WHERE TO_CHAR(A.REPORTDATE,'MON-YYYY') = :WO_DATE;

View 8 Replies View Related

PL/SQL :: GROUP By Equal Values

Oct 30, 2012

I have data

Type, Month, Amount
=========
4, 1, 43333.33333
4, 2, 43333.33333
4, 3, 43333.33333
8, 5, 16000
8, 6, 16000
8, 7, 16000
8, 8, 16000
4, 2, 100
4, 3, 100and

I want to group and SUM() equal amounts and find MIN(month) and MAX(month), so the output should look like

Type, MIN(month), MAX(month), SUM(amount)
=========
4, 1, 3, 130000
8, 5, 8, 64000
4, 2, 3, 200

View 2 Replies View Related

SQL & PL/SQL :: ORA-01476 - Divisor Is Equal To Zero Error

Jun 13, 2012

When running the query below, I receive this error: ERROR:ORA-01476: divisor is equal to zero.

Select Sum ( Decode ( ACCT_CLASS_NO
, 'TF', Round ( SUB_FUND_TNA_USD_IN_M, 2 )
, Null

[Code]....

how can I get around the ORA-01476: divisor is equal to zero error?

View 2 Replies View Related

SQL & PL/SQL :: Updating Another Table For Equal Strings

Jun 8, 2010

There are two tables:

create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);

im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.

here is what i've done so far:

drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);

What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist

View 4 Replies View Related

Performance Tuning :: Returning All Rows That Equal Max (value)?

Oct 14, 2010

I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.

Here's a sample of the inner query results:

"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13
"13:19:05" 4
"13:19:28" 4

[code]....

The max(sessions) is 13, so what I want the final output to be is:

"INTERVAL_VALUE""SESSIONS"
"13:14:47" 13
"13:14:52" 13
"13:14:54" 13

Here is the create sql for the test data:

CREATE TABLE "SESSION_TABLE"
(
"SESSIONKEY" NUMBER,
"SESSION_START_TIME" TIMESTAMP,
"SESSION_END_TIME" TIMESTAMP,
CONSTRAINT "PK_SESSIONKEY" PRIMARY KEY ("SESSIONKEY")
);

[code]....

Here is my query that works:

SELECT
maxval.interval_value,
allval.sessions,
licenselimit
FROM
(SELECT

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Outer Table Join With Not Equal Condition

Aug 20, 2010

I've created a query so I can easily compare two sets of data for two different instruments:

select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing,
cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing
from CalcAttribute a, CalcAttribute cvs
where a.NRef like '438815' and cvs.NRef like '438813'
and a.CalId *= cvs.CalId
and a.AtName *= cvs.AtName
union

[Code]...

This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.

I thought I should just be able to add an extra:
and a.ValLoat *<> cvs.ValLoat
and a.ValLoat <>* cvs.ValLoat

But it doesnt seem to like this (Incorrect syntax near '<'.)

View 3 Replies View Related

SQL & PL/SQL :: Checking If Month And Year Is Greater Or Equal To Current One?

Mar 7, 2013

This is the description for the procedure:

ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.

This is the procedure head:

create or replace
PROCEDURE ADD_REWARD_sp(
p_proj_id IN NUMBER,
p_pledgeAmt IN NUMBER,
p_rewardDesc IN VARCHAR2,

[code]...

Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.

View 3 Replies View Related

PL/SQL :: Create Constraint That Checks To Ensure (Year) Entered Is Less Or Equal To Current?

Aug 6, 2013

, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE). Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably. I am using Oracle 11g Express. 

CREATE TABLE TEST (Name VARCHAR2(7) PRIMARY KEY,  Year NUMBER(4),  CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL); 
*ERROR at line 4:ORA-00907: missing right parenthesis

View 7 Replies View Related

JDeveloper, Java & XML :: Java.sql.SQLException - Execute Batch - Exception Equal To 1?

Jul 14, 2011

I was getting java.sql.SQLException: executeBatch, Exception = 1when i tried to use executeBatch().could not understand why i am getting it.

View 1 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from  TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss')  it only returns  'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?

View 0 Replies View Related

Date Arithmetic - Formula To Subtract Years And Give Date

Sep 27, 2007

I am a novice in oracle

I have 2 columns in my table

->Col1 with experience in years entered as an integer
->Col2 with current date

I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date

I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work

View 8 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I am not able to load complete date along with time in the date column. here is my table desc

DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE

my control file is like this

LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)

the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

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

SQL & PL/SQL :: Declare Date Variable And Assign It To Current System Date

Apr 19, 2011

Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:

If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".

For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".

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

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related

Server Utilities :: Loading Date Value Into DATE Column?

Oct 18, 2012

I want to load data from a file using sqlldr.I have a table commissions
(
technician_id char(5)
, tech_name char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt number(10,2)
)

my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56
write a ctl file to load this data.

View 6 Replies View Related







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