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
ADVERTISEMENT
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
Mar 23, 2013
I have a complex requirement to be resolved, i have one table of quantities from where i want to show or select the data based on 2 criteria.
1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000.
2) The weight should not be more than 50,000, it should be less than 50000 if its more it should be displayed as 2 lines.
The following is the test case.
CREATE TABLE OW_STAG_SHIP (PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER);
INSERT INTO OW_STAG_SHIP VALUES ('A','AAA',400,30000); -- this will be displayed as its because qty and wt are ok
INSERT INTO OW_STAG_SHIP VALUES ('B','BBB',1100,4000); --Need to be displaed in two lines as qty is more than 1000
INSERT INTO OW_STAG_SHIP VALUES ('C','CCC',2500,6000); --Need to be displayed in three lines as qty is more
INSERT INTO OW_STAG_SHIP VALUES ('D','DDD',600,60000); --Need to be displaed in two lines as wt is more.
select * from ow_stag_ship
PS_CODEPS_DESCPS_QTYPS_WT
AAAA40030000
BBBB11004000
CCCC25006000
DDDD60060000
Output what i want is as below
PS_CODEPS_DESCPS_QTYPS_WT
AAAA 40030000
BBBB10002000
BBBB 1002000
CCCC10002000
CCCC10002000
CCCC 5002000
DDDD 500 50000
DDDD 100 10000
View 16 Replies
View Related
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
Dec 1, 2010
i want to show the date exactly in following format:
00:16:15 Wed 1st Dec 2010
I have tried :
select to_char(to_date('01/12/2010 00:16:15' , 'dd/mm/yyyy hh24:mi:ss'), 'hh24:mi:ss Day dd Mon yyyy')
from dual
Result it is giving
00:16:15 Wednesday 01 Dec 2010
How to show date in form 1st 2nd 3rd and so on?
How to show Abbreviated name of the day?
View 13 Replies
View Related
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
Jan 2, 2013
I have a date column , and i want to show the date in 'DD/MM/YYYY' format for eg days with 2 digit or comming properly but when it comes to single digit the 0 is not comming for example 4/11/2012 should come as 04/11/2012 like 16/11/2012 , i even did the conversion like to_char(rh_dt,'DD/MM/RRRR').
View 4 Replies
View Related
Jun 11, 2012
I'm using apex 4.1.1 And i want to use a Date Picker item on my page. This works, but.. When a user selects a date you can see the date highlighted. But in the "textarea"corresponding tot the datepicker is not showing the date. This shows the date after clicking the close button.
I would like to show the date directly after selecting a new date.
View 7 Replies
View Related
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
Apr 27, 2010
I have a script which is used to run a job based on the users choice. For example: I have two table, Files and Requests
User select the files to be executed for each request. This data will be stored in Requests table.
Table 1: Files
files
======
file-1
file-2
file-3
..
..
file-n
Table 2: Requests
request file lup_date
==================================
request-1 file1,file2,file3 04-JAN-2009
request-2 file1,file4,file5 06-JAN-2009
request-3 file6,file2 021-JAN-2009
request-4 file1,file2 04-FEB-2009
request-5 file1,file2 08-JAN-2009
request-6 file1,file2 04-MAR-2009
.......... ........... ................
request-n-1 file6,file2,file4 04-DEC-2009
request-n file6,file3,file4 04-DEC-2009
how to get the output in below format. Count how many times each file is selected in a month.
Output format should be like below..
==============================================
File_Name Jan Feb Mar Apr ---------- Dec
==============================================
file1 2 1 3 0 ---------- 2
file2 1 0 2 1 ---------- 3
file-n 8 2 3 0 ---------- 2
View 2 Replies
View Related
Jun 26, 2012
CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));
Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.
I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12
View 10 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
Mar 3, 2011
We have fact tables partitioned based on week.
We receive point of sale data on daily basis .
Some times we will receive data for previous week also.
In current system , we are analyzing only one partition(current partition).
Now , we want to analyze all old partitions also based on incoming old trading date.
how can i approach to find partitions for old data (trading_date s)
View 1 Replies
View Related
Sep 24, 2013
i need to be increasing the sequence no by 1 for every calender date.For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.
24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5
25/09/2013 1
View 6 Replies
View Related
Aug 14, 2011
is the definition of my table :
CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)
And these are the data that are available,(select * from DATEFETC)
IDDT
00108-09-2011
00208-10-2011
00308-11-2011
That's fine.
Now i am executing this query ,but this is returning no rows.Why ?
select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')
View 1 Replies
View Related
Dec 12, 2012
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
View 7 Replies
View Related
Oct 29, 2012
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI
View 10 Replies
View Related
Jul 30, 2012
My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.
Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:
It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01
So i started out my creating like this:
select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union
[Code]....
.and so on till i get 2010-01.
there has to be a better way to do this.
View 7 Replies
View Related
Mar 20, 2011
I have one view which displays the information of all the receipts with date now i want to make ageing report based on this date. the columns will be 0-30 , 30-60,60-180 and > 180 , i want to distribute the receipts qty based on number of days.
View 1 Replies
View Related
Jun 26, 2012
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies
View Related
Jul 26, 2012
i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:
exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";
is it possible to do like this, that it should prompt you to enter the start and end date?
then my import script:
imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;
the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.
View 12 Replies
View Related
Apr 3, 2012
I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.
drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........
View 10 Replies
View Related
Aug 26, 2013
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
View 4 Replies
View Related
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
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
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
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
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
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
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
