Query For Getting Data For Every Quarter For Financial Year
Nov 11, 2013
My problem is I need to get the data for every quarter for financial year and also I need data for every week for financial year.For example for financial year 2012-13, Apr2012 to Jun2012 would be Q1, Jul2012 to Sep2012 would be Q2 and so on. Total 8quarters should come upto Apr2013.In the same way 1st apr 2012 to 7th apr 2012 would be week1, 8th apr to 15th apr would be week2 and son on. How to write a query for this scenario in oracle.
View 4 Replies
ADVERTISEMENT
Jun 12, 2012
Haw to sort the data based on financial year.
For example Indain Fiscal year 01-Apr-2010 to 31-Mar-2011
Apr2010 -1
May2010 -2
.
.
.
.
Feb2011-11
Mar2011-12
I had given a query..for Quarter idendification
SELECT distinct
'Qtr'
|| CASE
WHEN PERIOD BETWEEN TO_DATE ('01-04-2010', 'DD-MM-YYYY')
AND ADD_MONTHS (TO_DATE ('01-04-2010', 'DD-MM-YYYY')-1, 3)
[code].....
View 2 Replies
View Related
Feb 20, 2012
I need to query by fiscal year wise.
For example
1-Apr-2010 to 31-Mar-2011
View 11 Replies
View Related
Dec 9, 2012
I hava an requirement to get week number from particular date as per indian financial year(ie apr-01 to mar-31).I have tried with to_char(the_date_field,'w') and to_char(the_date_field,'iw') formats but I know its only shows the iso standard.
But I want the weeknumber 1 has to be started from april-01 not from jan-01.For more explanation see below,
Date week_no
apr 01 to 07 1
apr 08 to 14 2
apr 15 to 21 3
.
.
.
.
.
Next year mar - 31 n
how to do this am waiting for your reply.
View 4 Replies
View Related
Jul 20, 2011
i hav the following records i need to display the records of financial year wise (i.e april year-1 to mar year) here there are no records for months 9,10(september and october)
what i want is 0 to be displayed over there in the below table
s.no key id yyyymm ce1 ce2 ce3 t ihu pf mi pwd F
920110600000156431372010043424541500065457709539XXXXXXXXXXU
10201106000001565313720100534245415000009539XXXXXXXXXXU
11201106000001566313720100634245415000009539XXXXXXXXXXU
12201106000001567313720100738555415000009539XXXXXXXXXXU
13201106000001568313720100836445415000009539XXXXXXXXXXU
1420110600000157131372010110000009539XXXXXXXXXXU
15201106000001572313720101276881082200001888009539XXXXXXXXXXU
1620110600000157331372011010000009539XXXXXXXXXXU
View 2 Replies
View Related
Jan 26, 2013
IF THE USER ENTER parameter for date(yymm) in a report as 201301
select vndr#,sum(net_sales_value) from mnthly_sales_value where vndr# = 111 and yymm = :yymm group by vndr#;
but I need result of 3 months, but in my table data is stored for one month so how to get 3 months then again 3 months so on for Q1,Q2,Q3,Q4
yymm between 201301 and 201303 and
yymm between 201304 and 201306 and
yymm between 201307 and 201309 and
yymm between 201310 and 201312
in the same query
what i have to do for this
View 3 Replies
View Related
Jul 1, 2010
I have form (table GL_PV)in this form I want to generate voucher no financial year wise.
I have Four columns
Year varchar2(4)
Month number(2)
Pv_no number(6)
Pv_date date
--- I use pre-insert tirgger---
when Insert record year save pv_date 'YYYY' (2010)
Month save as 'MM' (7) and pv_date I enter manulay
now I want Pv_no it should generate auto according to financial year.
I have 2 financial year
2009-2010 June-July
2010-2011 June-July
I want that every financial year PV_no should be separate. both financial year pv_no start from 1. when I enter data any financial year first it check the MAX(PV_no) then generate next Pv_no No. MAX(pv_No)+1
View 1 Replies
View Related
Oct 5, 2010
I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies
View Related
Jul 16, 2012
I need to display quarter of current year in select list.
for example current year is 2012 so, i want Jan-Mar 2012,Apr-Jun 2012,July-Sept 2012,Oct-Dec 2012 in select list.
View 3 Replies
View Related
Jun 19, 2012
I have a table for exampl,
emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012
i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.
my output like this.....
emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)
View 2 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
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
May 27, 2013
I have the following table structure with values
CREATE TABLE DUMMY
(
SR_NUMBER VARCHAR2(100 CHAR),
ASSIGNMENT_GROUP VARCHAR2(100 CHAR),
REASSIGNMENT_COUNT VARCHAR2(100 CHAR),
CREATED DATE,
[code]...
?I have the following requirement, the output should be:
Ticket count (sr_number)
% of tickets inside DL
Number of tickets inside DL
Average cycle time (cycle time = closed date - created date)
Total cycle time (cycle time = closed date - created date)
Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
View 10 Replies
View Related
Dec 30, 2011
I am writing a query where I'd like to pull one year's worth of data. Ideally I want to prompt for the END DATE and have the query go back in time one year from that date.
Here is what I've got after doing some research online... but It's not quite working for me.
select *
from mrtcustomer.profile
where reg_type = 'B'
and contact_type = 0
and active_ind = 'Y'
[code].....
View 4 Replies
View Related
Sep 15, 2011
Is there any way to tell the total data growth increase of some tables when compared with last year.
View 4 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
Jul 14, 2011
this is how do I calculate end of quarter ignoring days off.
with
calendar1 as
(select * from
(select to_date('29.03.2012','DD.MM.YYYY') as arcdate, 0 as dayoff from dual union all
select to_date('30.03.2012','DD.MM.YYYY') as arcdate, 0 as dayoff from dual union all
[code]...
I need to create list of end_of_quarter for period "between sysdate MINUS two years and sysdate PLUS two years". dayoff is not only Saturday and Sunday, but it's also state holiday, etc. In real life table calendar1 is updated by central bank.
View 4 Replies
View Related
Jan 25, 2013
My below query is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..
select add_months(trunc('20-dec-2012','q'),3) - 1 from dualERROR
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
even after adding the to_date function between trunc and date.
View 5 Replies
View Related
Nov 30, 2010
I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate
OrderId
Subtotal
CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL")
AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
View 1 Replies
View Related
Jul 23, 2010
as there is function for Quarter in sql but my requirement is i need to start the first quarter from april-jun.as it show first quarter as jan-march.
View 8 Replies
View Related
Feb 28, 2013
I need to have a function which will round to previous quarter
For ex :
if the value is 1.95 it should give 1.75
if the value is 1.60 it should give 1.50
View 2 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
Apr 26, 2013
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Nov 26, 2006
For e.g.
if my data is as follows
EX_START_DATE EX_END_DATE EX_CHARGE
5/1/2005 5/5/2005 11:59:59 PM 10
5/6/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20
the record
5/6/2005 5/7/2005 11:59:59 PM 10
is in continuation for
5/1/2005 5/5/2005 11:59:59 PM 10 with the same ex_charge
so i need the output as
5/1/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20
and the remaining rows as it is
(Note :date in mm/dd/yyyy format)
View 6 Replies
View Related
Apr 20, 2011
my propble is supose if today is Tuesday 01�32011 which is week 16 of this year i need to get same day tuesday on week 16 of last year
View 12 Replies
View Related
Jun 18, 2008
i am sure it may be wrong because it doesn't mention year however i have been told to get a list of all students who enrolled in MAY. and i have tried doing
select * from table where enrolDate > to_date('01-05','DD-MM') and enrolDate < to_date('31-05','DD-MM') and it doesnt work (says no rows selected when in fact there are some rows!
i can do it fine when using YEAR ie '01-05-2000','DD-MM-YYYY' another problem.
I am trying to do this:
Select to_char(to_date(01012008,DD-MM- YYYY),DAY) from dual;
and it is giving me the error however if i do this:
Select to_char(to_date(16012008,DD-MM-YYYY),DAY) from dual;
View 3 Replies
View Related
Sep 27, 2010
I got a table with a date-typed column called: "Birth_date", and I wanna write a function that retrieves all the records that "got a birthday" this week.
in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),
but I don't know how..
View 39 Replies
View Related
Jul 2, 2011
In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).
example
----------
jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5
feb ------------do---------------------------------
like this for all 12 months at a single query.
View 3 Replies
View Related
Nov 23, 2009
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
View 4 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
