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].....
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 View RelatedI need to query by fiscal year wise.
For example
1-Apr-2010 to 31-Mar-2011
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.
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
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
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
Query result gives out put like in the following order
CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO
How to get output in the following sort order
LONDON
CHENNAI
DELHI
RIO DE GENARO
i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.
I am using oracle database 11g.My use case is I do have a table with following valuesTable name -test
product id productsortdescription
H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK My query is
select * from test order by productsortdescription; this query gives result as is like
product id productsortdescription
H58098 ACETA
product id productsortdescription
H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK
MIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK
but Expected output/result should be like below:
product id productsortdescription
043994 Alloy .MM.INTHICKH58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE as All and ACE
in productsortdescriptionl is in small case than C. NLS Session parameters are as following
SELECT * from NLS_SESSION_PARAMETERS;
NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE
Below Query I understand is improper wrt syntax. Need to modify query to fetch correct result for me.
select * from tableA order by
case when a is not null then
a DESC
else
b DESC, c DESC END
Note: DESC or ASC is dynamic coming from java-code.
How can i sort data in the execute Query mode during runtime,i have a columns which has number, char and date field.
This below data is fetched in execute query from a table;
x1 x2 x3
1343 adfa 11/14/2012
1353 adfa 11/11/2012
1333 adfa 11/10/2012
1333 adfa 11/12/2012
1353 adfa 11/09/2012
now i like to sort like this below;
x1 x2 x3
1343 adfa 11/09/2012
1353 adfa 11/10/2012
1333 adfa 11/11/2012
1333 adfa 11/12/2012
1353 adfa 11/13/2012
how to do it?
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
....
...
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)
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
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.
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 RelatedI want to get the SQL queries based on SQL_ID.I have tried the following query,but I am not getting full query.
SET linesize 132 pagesize 999
column sql_fulltext format a60 word_wrap
break on sql_text skip 1
SELECT REPLACE (TRANSLATE (sql_text, '0123456789', '999999999'), '9', ''),sql_id
FROM dba_hist_sqltext s
WHERE s.sql_id = '7tvurftg8zryb';
One of my friend said use grid to get full query text. how to use grid ,else any other method to get the full query based on SQL_ID.
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')
I have two tables. By joining these two tables, I need to update a field in table1.
UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')
I keep getting error 'table1.lastname' is invalid identifier.
I can't understand the error message. I made sure that the fields exist.
In the below Table Structure, TB_Vehicles is the Master table and TB_Cars, TB_Bikes, TB_Cars are Satellite tables which hold more info about respective vehicles.
I want users to search using the Name. So, when users enter Name as 'Access', my query should give all information about that vehicle, including that from Satellite Tables (using both TB_Vehicles and TB_Scooters).
SELECT *
FROM TB_Vehicles
WHERE V_NAME = 'Access';
Similarly, if user enters 'Linea', it should give info from TB_Vehicles and TB_Cars tables.
So, with V_NAME as input, I'll find V_TYPE from TB_Vehicles table, and using that, I need to identify which satellite table, I need to join to retrieve more info.
Do I need to use CASE or DECODE to achieve this?
One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan? let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.
View 3 Replies View RelatedI'm using Oracle 10g.
Question: How can I write query to return just ID only if all the codes for that ID end in 6.
If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.
TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400
Desired Result
ID
==
2
3
I have a table with following values in a column
Table A
col1
10
35
20
25
I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.
Table B- 90 records (10 + 35 + 20 + 25)
Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,
with first group having start value -1 and end value -10
second group - start value -11 and end value-45
third group - start value -46 and end value -65
fourth group - start value - 66 and end value-90
one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.
note that the values in Table A is dynamically changing, so not able to hard code values.
find the logic to query data based on a condition like..I am having a table dummy
USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
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
I have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
I am using Apex 4.1 and have a requirement to create an interactive report based on a dynamic query. This option is available in Classic report but in Interactive this feature is not there. Tried using collections or just a view (thought of changing the view definition during "On load", but structure of the report is already defined based on the definition of the view at design time).
Is there any way of achieving this?
which table do I have to use to get the Inventory On Hand Packed Quantity based on Oracle EBS Suite using backend?
View 2 Replies View RelatedI have this situation:
a tabular form with two "select list (query based LOV)" fields. The first one must affect elements in the second one. And so the second query based LOV field should has a "where attr1 = first_list_selected_value" in the query.
Table A
Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
