I have a requirement as follows :-
The user will submit the start date and end date. Based on the date parameters, the customized procedure should do some calculations month-wise.
For eg : start date - 01-Dec-2012 to end date - 31-Mar-2013
I want the break up as
Start date End date
01-dec-2012 31-dec-2012
01-jan-2013 31-jan-2013
01-feb-2013 28-feb-2013
01-mar-2013 31-mar-2013
How can i achieve this month-wise break ?
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
have a req like below
CREATE TABLE TEST_BRK
( EMP_ID VARCHAR2(20),
BEG_DT DATE,
END_DT DATE,
DEPT_CODE VARCHAR2(10),
[code]...
COMMIT;i need to break down the each member range by record per month and make a record as first day of that month . and dept and branch should have the same value as what the value it was in the range of source.
excepted ouput like below
EMP_ID MONTH_DAY_1 DEP_CODE BRANCH
AAAA 01-JAN-2010 02 A
AAAA 01-FEB-2010 02 A
AAAA 01-MAR-2010 02 A
AAAA 01-APR-2010 02 A
AAAA 01-MAY-2010 02 A
AAAA 01-JUN-2010 05 B
AAAA 01-JUL-2010 05 B
.how can i code the logic to get my expected output above
I want to get the current month day wise count. I dont want Procedure. I need SQL Query only.For example:
July 2011.
Sunday - 5
Monday - 4
Tuesday - 4
Wednesday- 4
Thursday - 4
Friday - 5
Saturday- 5
I have data something like this.
Employee_id date_loggedin
123 09/10/2012
134 09/03/2012
111 09/02/2012
123 08/27/2012
134 08/01/2012
123 07/06/2012
111 05/11/2012
123 07/04/2012
123 07/03/2012
123 03/15/2012
123 01/11/2012
The desired output for input of employee_id=123 and dates between 01/01/2012 and 09/30/2012
Month login_count
JAN_2012 1
FEB_2012 0
MAR_2012 1
APR_2012 0
MAY_2012 0
JUN_2012 0
JUL_2012 3
AUG_2012 1
SEP_2012 1
I have a table with starting date and ending date.
I want to fetch monthwise days for given two dates
IE starting 25/12/2009 ending 25/03/2010
Result should like below.
Dec-09 7
Jan-10 31
Feb-10 28
Mar-10 25
I have tried this but it is not giving me the result which want..
select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from
(SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366
[code].....
how to find out growth of database month wise?
according to me, v$datafile can be use to know growth in database in terms of datafiles added in past months as per creation_time. but hows about those existing datafiles whose size has been re size more .
Whats your inputs on this ??
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
I have a query with output as:
CUST PROD
Customer1Product1
Customer1Product2
Customer1Product3
Customer2Product1
Customer2Product2
Customer3Product1The output required is
Number CUST PROD
1 Customer1Product1
Customer1Product2
Customer1Product3
2 Customer4Product1
Customer4Product2
3 Customer5Product1
For every change in customer i need to put a new serial number.
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
I dosn't want to use the command break on <column name> / how to write sql to replace the break command.
View 8 Replies View RelatedThere is a problem with name field like ex:
ENTER(line break) between 'HHD DDD PRIVATE' and 'LTD.
how to put a check in code to avoid these type of scenarios.
I have a report with 4 groups one above the other and there is data such that each group has certain number of records for a particular value. Now what I want is, to have a page break after each set of data for all the groups. Can this be achieved using format triggers ?
View 3 Replies View Relatedhow can i break my rtf page after every 8 record.
View 2 Replies View RelatedI have following table
CREATE TABLE MAMALIK.DTE
(
FRM_DTE DATE,
TOO_DTE DATE
)
Insert into DTE
(FRM_DTE, TOO_DTE)
Values
[code]........
Result is
FRM_DTE TOO_DTE
01/07/201201/07/2012
03/07/201207/07/2012
11/07/201215/07/2012
31/07/201201/08/2012
i want to write query which should return data as
01-jul-2012
03-jul-2012
04-jul-2012
05-jul-2012
06-jul-2012
07-jul-2012
11-jul-2012
12-jul-2012
13-jul-2012
14-jul-2012
15-jul-2012
31-Jul-2012
01-Aug-2012
i want to generate dates between frm_Dte and too_dte in single column
,Is there a way to make Interactive reports break format work/look like classic reports break format? i.e.1st,2nd,3rd column option. The way IR breaks is that is reserves a whole row for the column used in the break, which is not what I want, I want the report to look like the following:
city emp sal
Chicago John Miller 1500
Mark Horton 2000
Denver Rob Martino 1200
I tried to use rollup queries which work fine except that it does not do exactly what I want when I sort columns through the front end , I am basically displaying a check box for grouped records ( and I want it to display at the first record of each group), so the order is important. for example:
checkbox City emp sal[ ]
Chicago John Miller 1500
Mark Horton 2000[ ]
Denver Rob Martino 1200
Is there a way to short a Matrix report? The query shorts correctly in PLSQL but not in the generated report file.
There are columns like Date,Device,Operator Id etc and the report should be able to short depending on the requirement. For example,
it should be able to short by device, or by Date or both. I tried sorting in the 'Break order' but doesn't come out as required.
I'm using a control break on 3 columns in my report, but when doing this Apex shows the columns comma-separated, like: Column A: value, Column B: value, Column C: value But I would like it to look like:
Column A: value
Column B: value
Column C: value
Is there any way of achieving this? I am currently working with the test environment Oracle supplied us, because we are testing if we can use APEX at our company. The version is Application Express 4.2.2.00.11 and it's running on Oracle 11g. Im using Firefox but it's just the same in IE.
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
[code]....
The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.
i have a little curiosity in result of a select statement.
One database has following characterset settings:
NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN
Another database has following characterset settings:
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
and the same select shows only a 2 line result:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
I have dynamic header in my rtf file. I have section break on start group of body. but it does not display dynamic header value. If i remove section break then it display dynamic header.
I have to display dynamic header and section break is also required there.
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.
I have a table which have three column.
1) empid
2) date
3) status
I want to show the value empid intime outime.
select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend
where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1,
(select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012'
order by empid,atttime desc) out
where in1.empid=out.empid and in1.empid='02256'
order by in1.atttime,out.atttime
But this query do .one value relation with all column.means first february in time with all out time.
For one of our requirement, we are asked to provided data in below manner.
Deptno Deptname emp_name_1 hire_date_1 emp_name_2 hire_date_2
10 Sales Raghu 25-Jan-2007 Abbhilash 29-Mar-2009
If a particular department has 10 employees it should have data upto emp_name_10,if department has only 5 employess it should data upto emp_name_5 and so on.I came up with below approach, in this approach I need to create new table to store the data in row wise.
In my actual requirement 4 tables needs to be joined and 2 of the tables are very large.Is there any other approach without creating a new table, something within pl/sql.
drop table emp_dept;
create table emp_dept(deptno number,dept_name varchar2(100),emp_name varchar2(100),hire_date date,seq_cnt number,total_cnt number);
insert into emp_dept
[code]...
I want output like given below. Using EMp table . if sal is <1000 means count of Emp's who are having lessthan 1000 like that.
Deptno sal sal sal sal
<=1000 <=2000 <=3000 <= 5000
10 2 4
20 2.
i want create a report like this
location sysdate refdate No_of_days SL1(0-90) SL2(91-180) SL3(>180)
So when no. of days between (0-90) data put in SL1
And when no. of days between (91-180) data put in sl2
I want to achieve this only in single query.
OS : Windows 2003 DB : 10.2.0.4 I am doing capture and replay first time.I want to take 2 captures at a time. 1st capture for 2 schemas and 2nd capture for other schemas.Is it possible?I have searched on internet but didn't get any clue about it..
View 4 Replies View Relatedi am getting the data like bellow i need the data with out null values
date col1col2col3col4
16-Nov-1123
17-Nov-1112
18-Nov-11321
19-Nov-1123
20-Nov-11132
16-Nov-112321
17-Nov-11112
18-Nov-11211
19-Nov-11132
20-Nov-1112
16-Nov-1145
17-Nov-11465
18-Nov-112123
19-Nov-11132
20-Nov-1165
16-Nov-11456
17-Nov-11546
18-Nov-114656
19-Nov-11566
20-Nov-11564
need out like below
date col1col2col3col4
16-Nov-1123232145456
17-Nov-1112112465546
18-Nov-1132121121234656
19-Nov-1123132132566
20-Nov-111321265564
how could i display india as i n d i a in rowize in oracle query
output
-----------
i
n
d
i
a
(i am newbiee to this forum ,i dont know on which category i should post this question is there any general category for orcle questions?)
