Specify Month Start And End Date In Query?
Oct 27, 2008
I want a monthly report where the month wise sum of qty should be displayed in a row for last 12 months. I need to specify the month start date and end date in the query to pick the sum for the particular month. How can i do it in a SQL query?
SUM(decode(to_char(t1.trx_date,'mm/rrrr'),
to_char(add_months(SYSDATE,
-1),'mm/rrrr'),
nvl(t2.quantity_invoiced,
0))) AS qty_01
from t1,t2
where t1.col1=t2.col1
instead of sysdate i have to use start and end of month. Also i am using group by clause on some columns.
View 1 Replies
ADVERTISEMENT
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
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
Apr 30, 2012
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
View 3 Replies
View Related
Mar 1, 2012
I have a sql like this
select inv_dtime where inv_dtime between trunc(sysdate,'YYYY') and last_day(trunc(sysdate,'YYYY')) from temp;
I want to have the start date of the year and end date of the year in my condition. like between 01-JAN-2012 AND 31-DEC-2012. I tried the above but it doesn't come.
View 4 Replies
View Related
Mar 25, 2013
I have data like this.
Process_date SEQ_No
------------- ---------
16-MAR-13 733
09-MAR-13 732
02-MAR-13 731
24-FEB-13 730
16-FEB-13 728
09-FEB-13 727
02-FEB-13 726
26-JAN-13 725
21-JAN-13 724
12-JAN-13 723
05-JAN-13 722
29-DEC-12 721
24-DEC-12 720
15-DEC-12 719
08-DEC-12 718
03-DEC-12 717
22-NOV-12 716
17-NOV-12 715
10-NOV-12 714
03-NOV-12 713
29-OCT-12 712
23-OCT-12 711
13-OCT-12 710
05-OCT-12 709
28-SEP-12 708
22-SEP-12 707
15-SEP-12 706
08-SEP-12 705
01-SEP-12 704
every month admin will refresh actual data table and automatically this above table will update with unique seq_no and process_date.
I need to extract min date of every month(First refresh of last 6 months - excluding current month) and also seq_no related to that month so using joins(using seq_no - that is available in main table) i can combine actual data.
I need result like:
02-MAR-13 731 ( I don't need MAR as it should not take current month data)
so i need final result like below:
02-FEB-13 726
05-JAN-13 722
08-DEC-12 718
03-NOV-12 713
05-OCT-12 709
01-SEP-12 704
View 8 Replies
View Related
Feb 5, 2011
I am creating report for this i want to make one query..
Query like this
My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...
Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...
View 10 Replies
View Related
Jul 7, 2012
Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00
How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color
query out put should give as below
start_date + next record ,end_date - Previous records should give the difference of
6
1
View 12 Replies
View Related
Nov 2, 2011
I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.
What I need is one for month end and one for last 7 days.
here's what I have so far.
select ap.name
to_char(ap.opendate,'MM-DD-YY') "Open Date"
from [databasname]
where ap.opendate between databasename-7 and databasename-1
I really don't want to change the date myself I want the system to know when the 7 days or month is.
View 4 Replies
View Related
Sep 20, 2010
i have a requirement where i have to exclude the zero from the month e.g jun=06 but i require 6 only not zerodec=12 i require 12
Do we have any format in oracle which support this.
View 3 Replies
View Related
Nov 1, 2013
our web application has a field DATETO which has no validation (end date). So user can put garbage there. In this field should be SYSDATE + max. 2 years. But also SYSDATE + 1 month OK.
I need to make a script that sets for example "31.12.9999" to null. Because so long end date is non sense.
SQL> SELECT EXTRACT (YEAR FROM (TO_DATE (DATETO, 'DD.MM.YYYY')))
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
ERROR:
ORA-01839: date not valid for month specified
no rows selected
Littlefoot writes about ORA-01839 "It usually happens when invalid dates are used, such as 30th of February"[URL]..
I don't see invalid date???
SQL> SELECT DATETO
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
DATETO
--------------------------------------------------------------------------------
23.09.2013
[code]...
DATETO
--------------------------------------------------------------------------------
31.12.2014
31.12.2016
31.12.2013
31.08.2014
[code]...
View 7 Replies
View Related
Mar 28, 2013
I need a pl/sql function to calculate the financial month from an date... For eg. If i enter input date as '21-sep-2012' the function should return me the output value in months as 6. so it should calculate from april to september of that year...
View 13 Replies
View Related
Oct 31, 2012
I am trying to get the month number from this date format '19-OCT-12' . when i try to run the below statement i am getting an error like "not a valid month"
select to_date('19-OCT-12','MM') from dual
i need to get value as 10
View 4 Replies
View Related
Feb 11, 2011
I have a table having a code column A and a date column D1 which represents the days when the code was inserted. I can have more then 1 date for a code.
The idea is to create another column (an id) which will correspond to all the inserted days of a code for a period of 4 month. if, after 4 month of the first insert, another records appears, then another id will be generated for my code. If a code has the date included in one of the intervals existing already, then it will just receive the corresponding id.
Example:
id code date
id1 cd1 01/01/2010
id1 cd1 04/03/2010
id1 cd1 22/04/2010
id2 cd1 27/05/2010
id2 cd1 21/06/2010
If a cd1 appears now on 22/05/2010, then i have to give it id2.
View 3 Replies
View Related
Dec 17, 2011
I need to get the past 12 months from the current month
for example
input:
march2010
output:
apr2009
may2009
june2009
july2009
augest2009
september2009
october2009
november2009
december2009
january2010
february2010
View 23 Replies
View Related
Jun 4, 2010
I've done this once before, but can't seem to find the sql.
How can I sort by month and year on a column called ex: TEST_dATE
JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010
View 15 Replies
View Related
Jul 8, 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
View 4 Replies
View Related
Jun 16, 2010
I see month aging buckets in the Oracle Application I am using (Keystone time and billing). I need to do a query in directly in Oracle (Toad front end to Keystone database) using month aging buckets . I use the following in Access that matches the results in Oracle, but I need to work directly in Toad because I want to avoid having to bring over all the dates when I want to summarize by buckets.:
2-4 Months: Sum(IIf(DateDiff("m",[invoice date],forms!Reporting_Standard!txtlastdayofmonth) In (2,3),[Outstanding Gross],0))
I know about getting the difference between two dates and that works fine for day aging buckets, but I need months, which can deal with months that are different sizes.
View 3 Replies
View Related
Oct 8, 2012
select b.penjara_id, p.penj_lokasi, a.no_daftar, b.episod, b.nama1,to_char(a.trkh_mula_prl,'dd/mm/yyyy') as trkh_mula_prl, to_char(bulan_proses,'mm/yyyy') as bulan_proses,
b.epd, b.lpd
from prl_daftar_proses a, senarai_pesalah b, penjara p
where a.no_daftar=b.no_daftar
and a.episod=b.episod
and b.penjara_id = p.penjara_id
and a.setuju_jplp is null
and a.bulan_proses between to_date(:FROM,'dd/mm/yyyy') and to_date(:TO,'dd/mm/yyyy')
order by b.penjara_id, a.bulan_proses,a.no_daftarHi,
how i can insert only month and year from the value that have full date in the database?
for example,the date is 09/18/2012, but i just want to insert 09/2012 as parameter. If i want to insert only one parameter, i can do that..But i have problem when I want to insert two parameters..
View 16 Replies
View Related
Jul 25, 2013
The problem I am facing analyzing a certain table s trying to get a proper start and end date for a specific field such as TICK_COL, because there are so many other fields are being updated in this table - all using MTC_DATE - this is causing iterations of TICK_COL.
So first step was to just use lead to get the end date for all iterations so I could picture how this might look with a start and end date
CODE A (see below)
FROM_DATE TO_DATE
SKU TICK_COL MTC_DATE LEAD (MTC_DATE)
21524804 RIBG 10101 20080615
21524804 RIBG 20080615 20080625
21524804 RIBG 20080625 20080628
21524804 RIBG 20080628 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20080930
[Code] .......
My first bright idea? I tried using Rank as well, hoping to rank each of this tick_color changes as 1, which works for the exception of when tick_col changes to RIWH or RIGR again.
The ranking function doesn't see the 2nd change to RIWH as entirely unique and assigns it a 2 and 2nd change to RIGR a 3. If I could rank each of those as 1 I could query these results as an in-line view where rank = 1 and do lead to get the start and end date, finished,
CODE B (see below)
21524804 RIBS 20130725 20130725 8
21524804 RIBS 20130327 20130725 7
21524804 RIBS 20130317 20130327 6
21524804 RIBS 20130312 20130317 5
21524804 RIBS 20120813 20130312 4
21524804 RIBS 20100916 20120813 3
21524804 RIBS 20100518 20100916 2
21524804 RIBS 20091120 20100518 1
[Code] ........
I am Expecting to see this below:
21524804 RIBG 10101 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20081031
21524804 RIWH 20081031 20090311
21524804 RIGR 20090311 20091120
21524804 RIBS 20091120 20130725
The code I used to generate the first table was which obviously, does not get me as far as I�d like.
CODE A
SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].......
CODE B
SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].........
View 3 Replies
View Related
Nov 18, 2011
I have a sub query (already dervived from several other tables) that has a list of children in with the date they started their course (tableofchildren). Child IDs may be duplicated in this query but each record will have unique start dates per child ID.
I have a second sub query which lists workers involved with the children in the first query (tableofworkers). A worker may be responsible for more than one child but has the unique child in the record to identify involvement with the child.
I need to join these queries together to return the child's record with the WorkerName and the AllocatedStartDate of the worker who was most recently involved with the child prior to the date (EnteredCourseDate) the child started their course (OutputWanted) - the worker associated with the child when they started their course.
A couple of points - I need to deploy this into another reporting application that doesn't support cursors etc or the 'With' operator. Also, I tend to join tables/queries in the Where clause so if it's possible that way that would be great.
OC.
create table tableofchildren
(ChildID varchar(20),
ChildName varchar (50),
[Code].....
View 13 Replies
View Related
Sep 20, 2011
i have a requirement to create a query which gets the same month from the previous year of the selected date .
Example : i select SEP-2011 ( as sysdate )
it will return the SEP-2010 as well .
here is the code which works fine with sysdate, dual :
SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;
in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)
am unable to apply the above code on it , i guess its the data type pf the field .
View 5 Replies
View Related
Mar 21, 2013
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
View 16 Replies
View Related
Apr 29, 2013
I am using the cloud version of APEX for a college course. The database script I am trying to load has dates formatted in "DD-MON-RR" and I receive 'not a valid month' and 'a non-numeric character was found where a numeric was expected'. Is there a workaround in APEX for this? I'm trying to avoid changing thousands of lines of data.
View 4 Replies
View Related
Sep 23, 2013
how to write pl/sql to create date o/p like .
since we wnat to design pl/sql report wich will generate monthwise sale from 01jan2009 to 01-sep-2013.
we have created funtion wich will accept argument as employee no, from date and to date.
from_date to_date
01-JAN-2009 01-FEB-2009
01-FEB-2009 01-MAR-2009
01-MAR-2009 01-APR-2009
01-APR-2009 01-MAY-2009
01-may-2009 01-JUN-2009
01-JUN-2009 01-JUL-2009
View 4 Replies
View Related
Mar 20, 2011
I have two Queries.
Query 1:
SELECT DISTINCT YR FROM(
SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'YYYY'), 1*LEVEL -1) , 'YYYY')
YR FROM Dual
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1
ORDER BY YR
)
Gives the Output as
YR
****
2007
2008
2009
2010
2011
Query 2
*******
SELECT DISTINCT MONTH FROM(
SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'MM'), 1*LEVEL -1) , 'MM')
MONTH FROM Dual
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1
ORDER BY MONTH
)
Gives the Output as
MONTH
***
01
02
03
04
05
06
07
08
09
10
11
12
I want to combine these two. I need the output as
2007-01
2007-02
2007-03
....
...
View 8 Replies
View Related
Jul 12, 2013
I have an excel spreadsheet that queries an oracle data for some information. I am trying to get the information shown to only be from the current month. I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out. Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".
View 6 Replies
View Related
Apr 19, 2010
How do you specify the value in START WITH dynamically during runtime. For example consider the bewlo scenario:
CREATE TABLE ISCT
(
ITEM_NO VARCHAR2(15 CHAR) NOT NULL,
ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL,
ITEM_TYPE_SCO VARCHAR2(3 CHAR) NOT NULL,
[Code].....
Now if you create a view:
create or replace view test_v
(ITEM_NO, ITEM_TYPE, ITEM_NO_SCO,ITEM_TYPE_SCO)
as
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
[Code] .......
Then run the below query:
select * from test_v where item_no_sco = '00245905'
--
the output is got within a second. but the real scenario is that both table icont and isct have millions of rows. In that case even this small query of the view takes 10minutes as there a FULL TABLE JOIN.
How do I give the START WITH in the view to make it faster i.e. even with millions of rows the output of this comes in seconds:
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
(
SELECT NVL(C.ITEM_NO_CHILD, I.ITEM_NO) ITEM_NO, NVL(C.ITEM_TYPE_CHILD,
I.ITEM_TYPE) ITEM_TYPE, I.ITEM_TYPE_SCO, I.ITEM_NO_SCO
[Code] .......
MC: Remove code tags on INSERT, CM take care to not exceed 80 characters when you add coce tags: reformat yourself or do not do it.
View 15 Replies
View Related
Aug 23, 2010
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
how to use in where condition
View 13 Replies
View Related
Oct 19, 2010
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
View 2 Replies
View Related
