PL/SQL :: Maintain Table With Last 13 Of A Month
Jun 15, 2012
I do need to mantain a table with the las 13 of a month, for example:
ID Month_ID MONTH_MAT
1 201206 201106
2 201206 201107
3 201206 201108
4 201206 201109
5 201206 201110
[Code]....
Kind of a year-to-date table but this one is 13 months one.
I have been trying to create the code from scratch
I have thought of using a Function:
Query: SELECT TRUNC(ADD_MONTHS(SYSDATE, -13),'MM') FROM dual;
Result: 01-MAY-11
View 6 Replies
ADVERTISEMENT
Jun 4, 2013
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,
View 2 Replies
View Related
Aug 4, 2012
I have one table as per the attachment, with id(Primary key),level,name and Srno.Now I want to maintain Sr. if user transfers level 2 of one block (level 1) to another block.is it possible through any oracle function.
View 8 Replies
View Related
Nov 16, 2012
I have a requirement like below
I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .
In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table
in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.
Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.
View 3 Replies
View Related
Aug 29, 2012
We need to maintain the day 0 value of special character "ÿÿ" for application. If i use the special character directly in sql file , it is getting inserted as junk character "?"
If i use workaround by using chr(255) function i am getting below behavior.
select chr(255) from dual
---------------------------
ÿ
However below query gives null value.
select chr(255)||chr(255) from dual
View 5 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
Nov 3, 2011
creating sql query for producing result as per below from dual table.
SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc
from above query the result is
NOV-2011
but i need the result as per below
APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH
View 4 Replies
View Related
Jun 10, 2011
I've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..
select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual
[Code]...
View 31 Replies
View Related
Oct 17, 2012
What is the good practice to maintain RMAN catalog ?
RMAN backup and recovery is fine but to delete old backups it takes a lot of time.
Even if I delete backups manually,RMAN takes a lot of time to delete expired backup command.
What can be done to increase the speed of delete obsolete backup and delete expired backup operations ?
View 2 Replies
View Related
May 16, 2013
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).
View 5 Replies
View Related
Feb 18, 2011
I was considering a solution to maintain a replicated copy of a database in a remote office. However we are using SE One edition of oracle, so native support for dataguard is not available. There definitely should be some scripting solutions for this task, but I can't find any to date.
View 7 Replies
View Related
Jan 11, 2013
There is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.
View 1 Replies
View Related
Nov 25, 2010
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
View 17 Replies
View Related
Mar 16, 2011
Is it Possible to Maintain real Time Backup using RMAN ?
View 39 Replies
View Related
May 18, 2011
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
View 5 Replies
View Related
Sep 5, 2013
My goal is to:
A) extract subscriber numbers from TCME153.SUB then
B) bounce those subscriber numbers off of a disconnect date i.e. DISCO_DTE_SBB then
C) bounce A) and B) off of an item table i.e. ITV_ITEM_VALUE
My hope is that the first query works as intended. Second and third queries throw error 'ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH'.
1) SELECT DISTINCT SUB_ACCT_NO_ITV
FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
WHERE EXISTS (SELECT SBB_BASE.DISCO_DTE_SBB FROM (SELECT SUB
FROM TCME153.SUB));
2) SELECT DISTINCT SUB_ACCT_NO_ITV
FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
[Code]....
View 1 Replies
View Related
Jan 21, 2011
I am trying to query on the dates in the table that are the last day of each month. The last day of the month may not be in the table. Example 07/30/2010 is the last day in the table for July 2010, but 07/31/2010 is the last day of the month. My query should give me 07/30/2010 data since it is the last day of the month in the table.
I just need a where clause that will only give me the data for the last day of each in the table.
View -1 Replies
View Related
Jun 10, 2010
i want to get the last month starting from today onwards.
1) select the record of last month that is if this month is june then select the record for 1st may to 31st of may
View 7 Replies
View Related
Sep 25, 2012
I am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer
select to_char(last_day(sysdate),'DD-MON-YY')from dual
ans : 30-SEP-2012
But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.
View 2 Replies
View Related
Sep 20, 2010
I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.
For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.
e.g. a range could be from today:Sept,20 to Apr,18.
so what i was trying is to select doing the following.
select TABLE_X_ID, TABLE_X_BIRTH_DATE
from TABLE_X
where to_date(TABLE_X_BIRTH_DATE, 'DD/MM')
between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM')
and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')
first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.
View 13 Replies
View Related
Dec 6, 2010
I need to calculate first business day of a given month . Below is complete explanation
Business day=sould not include weekends and holidays.
In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.
So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.
I'll be passing the month begin date as parameter..so the function should return the first business day for that month.
View 16 Replies
View Related
Oct 26, 2010
How to find the total number of Thursday in the current month ?
View 5 Replies
View Related
Jun 20, 2012
I've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:
SELECT DISTINCT
CASE
WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date)
[code]....
The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause
ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));
But I got an ORA-01866: the datetime class is invalid. I'm using Oracle 10g xe (outdated, I know). get the months to show up in order?
View 7 Replies
View Related
Jan 31, 2013
How I can see the order by employees,year wise and monthwise (month should start from january and so on..)
SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') YEAR,TO_CHAR(HIREDATE,'Month') MONTH ,count(*) total
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');
View 8 Replies
View Related
Aug 13, 2010
Past 13 MonthEnds from a input date
(i.e.: 6/13/2010 is supplied, then query should be able to retrieve 5/31/2010, 4/30/2010, 3/31/2010, 2/26/2010, 1/29/2010, 12/31/2009?.....5/29/2009)
If the month end date falls on a weekend (Sat or Sun), then it should have the prior Friday's date.
View 18 Replies
View Related
Apr 8, 2013
have this SQL query works fine but I want to calculate the balances for each month...I STARTING to do so: I have to:
JanuaryBalance = Initialbalance + January;
FebruaryBalance = JanuaryBalance+February;
MarchBalance = FebruaryBalance + March;
SELECT
Sum("F0902"."GBAPYC"/100) AS "initialBalance",
Sum("F0902"."GBAN01"/100) AS "January",
Sum("F0902"."GBAN02"/100) AS "February",
Sum("F0902"."GBAN03"/100) AS "March",
[code]....
View 10 Replies
View Related
Jun 25, 2010
can we take the maximum and minimum value of month and year
View 7 Replies
View Related
Feb 13, 2013
How to find out first thursday for each month in year.
View 7 Replies
View Related
Jan 28, 2011
getting the following kind of output.
I would like to get Each year and the number of employees joined in the corresponding month for jan, feb, mar and april from emp table.
A sample output looks like below.
YEAR JAN FEB MAR APRIL
2000 4 2 1 2
2001 2 1 1 5
2002 2 4 2 6
2004 2 4 1 4
View 7 Replies
View Related
Jul 1, 2011
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
View 3 Replies
View Related
