SQL & PL/SQL :: Past 13 Month Ends

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


ADVERTISEMENT

SQL & PL/SQL :: Month Ends Not Coming Correct?

Sep 7, 2011

I am using following query to get last 3 month ends. Here If month end falls on weekend(sat/sun) then it should have prior date

Where
COB- Table name
COB_DTE_ID_C- Date in numeric format (YYYYMMDD)
COB_DAY_N- Days
COB_MO_C- Month Number

I am not getting how to modify below query without much case when statements so that if I pass monthend date (20110531), It should give me

20110228
20110331
20110429

Its giving me

20110331
20110429
20110530

select * from (
select DISTINCT MAX (COB_DTE_ID_C) OVER (PARTITION BY COB_MO_C)
from COB
where COB_DTE_ID_C < 20110531
and COB_DTE_ID_C > to_char(add_months(to_date(20110531,'YYYYMMDD'),-3),'YYYYMMDD')
and upper(COB_DAY_N) NOT IN ('SATURDAY','SUNDAY')
ORDER BY 1
) where rownum < 4

View 13 Replies View Related

PL/SQL :: Number Of Patients Of The Past Month?

Nov 16, 2013

i have an application for a medical laboratory, i insert patients every day , what i want is toget the number of patients for the past month ?2- for the past week ?3- the past day , and so on.

View 11 Replies View Related

SQL & PL/SQL :: Date - How To Get Past 12 Months From Current Month

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

SQL & PL/SQL :: Loading Past And Future Month Data From Different Source

Jul 31, 2013

I have an requirement to load past and future data from different source table to one tgt table.Say now we are in July

Past Data
Apr May Jun

Future Data
Aug Sep Oct

I HAVE actual sales for past month and present month which is in Table A & expected sales in table B.for every month i have to load 7 months data TARGRT TABLE..

In future the requirement may change to 6 months also. in that case the procedure has to load past 6 month + current month + 6month future so totally 13 month should be loaded.

View 3 Replies View Related

SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table

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

PL/SQL :: ORA-03127 - No New Operations Allowed Until Active Ends?

May 15, 2013

I'm a software developer, not an Oracle DBA

Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.

View 7 Replies View Related

SQL & PL/SQL :: Recursive Query - Each Field Starts Where Previous Ends

Dec 6, 2011

I have a table:

create table FIELDS
(
FIELD_NAME VARCHAR2(30) not null,
PRG_FIELD NUMBER not null,
LENGTH NUMBER
);

with

INSERT INTO FIELDS VALUES('FIELD1', 1, 3);
INSERT INTO FIELDS VALUES('FIELD2', 2, 3);
INSERT INTO FIELDS VALUES('FIELD3', 3, 4);
INSERT INTO FIELDS VALUES('FIELD4', 4, 2);
INSERT INTO FIELDS VALUES('FIELD5', 5, 1);

I need to insert in a table:

create table STUFF
(
FIELD_NAME VARCHAR2(30) not null,
FSTART NUMBER not null,
LENGTH NUMBER
);

And the output I want is:

INSERT INTO STUFF VALUES('FIELD1',0,3);
INSERT INTO STUFF VALUES('FIELD2',3,3);
INSERT INTO STUFF VALUES('FIELD3',6,4);
INSERT INTO STUFF VALUES('FIELD4',10,2);
INSERT INTO STUFF VALUES('FIELD5',12,1);

So each field starts where the previous (ordered by PRG_FIELD asc) ends.

I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.

PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out

View 1 Replies View Related

SQL & PL/SQL :: Date Format Picture Ends Before Converting Entire Input String

Jun 29, 2011

I am trying to insert a row in a table and getting the below error.

SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string

Table Structure is

Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>

View 11 Replies View Related

SQL & PL/SQL :: ORA-01830 / Date Format Picture Ends Before Converting Entire Input String

Feb 26, 2013

when i run this query i am facing date format error.

select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');

ORA-01830: date format picture ends before converting entire input string

View 1 Replies View Related

SQL & PL/SQL :: JSP Limitations - Date Format Picture Ends Before Converting Entire Input String

Feb 21, 2010

I am using

SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;

its givinig right result for

1023411->One Million Twenty-Three Thousand Four Hundred Eleven

but if i will change it to 10234111 then this query giving an error

ORA-01830: date format picture ends before converting entire input string.

Is there any limitation or is there any other built in function to get the number to a word.

View 2 Replies View Related

SQL & PL/SQL :: Getting Past 13 Weekdays From Supplied Date

Aug 20, 2010

I want to get a past 13 weekdays (Exclude weekends) from the supplied date

Like if 20100613 is the supplied date, then it should give me following dates

DatesSr No
11-Jun1
10-Jun2
9-Jun3
8-Jun4
7-Jun5
4-Jun6
3-Jun7
2-Jun8
1-Jun9
31-May10
28-May11
27-May12
26-May13

I am trying following way but giving me same date 11 june

SELECT
dt from
(
select
CASE when to_char(To_Date(To_Char('20100613'),'YYYYMMDD'),'fmDy') in ('Sat','Sun')
then next_day(To_Date(To_Char('20100613'),'YYYYMMDD')-7,'Friday')
else To_Date(To_Char('20100613'),'YYYYMMDD')end as dt

[Code]...

View 3 Replies View Related

Server Administration :: How To See What Happened On Past 10 Days In DB

Jul 19, 2010

I am working in development environment which has only 150 gb db size but suddenly it is raised to 250gb within 10 days i want to see which user did what in my database.How we can see what task made what in past 10 days.

View 3 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

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

SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies View Related

Security :: How To Get User Login / Logout Information For Past 30 Days

Feb 3, 2012

I just want to know the user login / log out information for the past 31days. how can i get that?

View 11 Replies View Related

Performance Tuning :: How To Find Out Session Consuming High Resource In Past

Mar 28, 2013

How can I find out the particular oracle session which was consuming high memory in the past?

I can't get the data in v$sessstat
Unable to get the information in AWR

dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history

which approach I shall take to find out the session which consumed most memory in the past?

View 8 Replies View Related

PL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function

Nov 22, 2012

resolve issue while modified the user function code for returns the values as timestamps

---function code

create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin

[Code].....

Input:-
select fun_test_timestamp('5') from dual;

Output:-

SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;

Modified Fun Code:

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);

[Code]...

Input:-
select fun_test_timestamp('5') from dual;

Output:-

ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"

*Cause:    identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.

*Action: 

View 6 Replies View Related

SQL & PL/SQL :: For Each Month Sum Values From Month Before?

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

Backup & Recovery :: Restoring Later Backup Control File And Recovering Till Past Scn

Jul 11, 2012

I want to ask if I restore a control file from backup of say day2 and I recover till day1 using RMAN script, then what will be the state of my database? I mean day2 control file may be having SCN-Day2, and I am instructing RMAN to recover till past point in time that is SCN-Day1.

View 3 Replies View Related

Day Of Month Must Be Between 1 And Last Day Of Month

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

SQL & PL/SQL :: Last Day Of Each Month

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

SQL & PL/SQL :: How To Get Last Month Record

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

SQL & PL/SQL :: Find Last Day Of Given Month?

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

SQL & PL/SQL :: Range By Day And Month

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

SQL & PL/SQL :: First Business Day Of A Given Month?

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

SQL & PL/SQL :: Last Thursday Of A Month

Oct 26, 2010

How to find the total number of Thursday in the current month ?

View 5 Replies View Related

PL/SQL :: Order By Month Name

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

SQL & PL/SQL :: Order By Month

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

SQL & PL/SQL :: Calculate Balances For Each Month

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







Copyrights 2005-15 www.BigResource.com, All rights reserved