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


ADVERTISEMENT

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 :: 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 :: 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

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 :: 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 :: 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 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

SQL & PL/SQL :: Max And Min Values Of Month And Year

Jun 25, 2010

can we take the maximum and minimum value of month and year

View 7 Replies View Related

SQL & PL/SQL :: Find Out First Thursday For Each Month

Feb 13, 2013

How to find out first thursday for each month in year.

View 7 Replies View Related

SQL & PL/SQL :: Employees Joined In Each Month

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

SQL & PL/SQL :: Day Wise Count In A Month

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

SQL & PL/SQL :: How To Get Month Wise Data

Sep 11, 2012

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

View 2 Replies View Related

SQL & PL/SQL :: Get Count Of Records By Month

Aug 17, 2011

I have the following table. In this table i'm trying to get the count of records by month when its value (SIGNAGE_ONE_LANE_ROAD_AHEAD) is 'Yes' and its giving me the wrong count.

AUDIT_DATE SIGNAGE_ONE_LANE_ROAD_AHEAD SIGNAGE_LANE_CLOSED_AHEAD

1/5/2011 9:30 N/A N/A
2/9/2011 11:50 Yes Yes
2/21/2011 10:00 Yes Yes
2/22/2011 7:00 N/A N/A
3/7/2011 13:35 Yes N/A
3/8/2011 9:15 N/A N/A
3/9/2011 8:40 N/A N/A
3/10/2011 10:00 N/A N/A
[code]....

View 15 Replies View Related

SQL & PL/SQL :: Getting Data From Previous Month

Mar 8, 2011

I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).

I've already achieve getting data from the previous month by using this formula:

(to_char(CRE_DTTM,'MON')) = UPPER(to_char(add_months(trunc(sysdate,'MONTH'),-1), 'Mon'))

My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?

View 10 Replies View Related

SQL & PL/SQL :: Year And Month Query

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

SQL & PL/SQL :: ORA-01843 / Not A Valid Month

Jul 25, 2011

I have a date field with varchar datatype. My data look like :

ID Amend_DATE
1 4/24/1974
1 4/18/2001
1 8/2/1971
2 8/4/1965
3 11/9/2001
4 8/15/2001

I need to find the minimum amend_date for each ID.When i calculate min(amend_date), it gives me error:

ORA-01843 : not a valid month

Query i am using is :

select min(to_date(amend_date ,'mm/dd/yyyy') ) from t group by id;

My data on application appears as 24 April 1974.

View 2 Replies View Related

ORA-01843 / Not A Valid Month

Sep 14, 2012

I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).

I pass these values to Cursor 2.

v_promotion_id promotion.promotion_id%TYPE;
v_promotion_start_date promotion.start_date%TYPE;
v_promotion_end_date promotion.end_date%TYPE;
CURSOR c_promotion_list IS
(SELECT DISTINCT p.promotion_id,
p.start_date - 5,
p.end_date + 5
FROM promotion p,

[code]....

I am getting this error: ORA-01843: not a valid month

Initially I was passing just the variables without To_date. But I got the same error.

View 2 Replies View Related

PL/SQL :: Function To Find Nth Month Name (V 10g)?

Sep 17, 2013

find the last nth month name [Single month].

View 5 Replies View Related

PL/SQL :: How To Get Min Date Of Every Month For Six Months

Mar 25, 2013

I have data like this.

Process_date SEQ_No
------------- ---------
16-MAR-13     733
09-MAR-13     732
02-MAR-13     731
24-FEB-13     730
16-FEB-13     728
09-FEB-13     727
02-FEB-13     726
26-JAN-13     725
21-JAN-13     724
12-JAN-13     723
05-JAN-13     722
29-DEC-12     721
24-DEC-12     720
15-DEC-12     719
08-DEC-12     718
03-DEC-12     717
22-NOV-12     716
17-NOV-12     715
10-NOV-12     714
03-NOV-12     713
29-OCT-12     712
23-OCT-12     711
13-OCT-12     710
05-OCT-12     709
28-SEP-12     708
22-SEP-12     707
15-SEP-12     706
08-SEP-12     705
01-SEP-12     704

every month admin will refresh actual data table and automatically this above table will update with unique seq_no and process_date.

I need to extract min date of every month(First refresh of last 6 months - excluding current month) and also seq_no related to that month so using joins(using seq_no - that is available in main table) i can combine actual data.

I need result like:

02-MAR-13     731 ( I don't need MAR as it should not take current month data)

so i need final result like below:

02-FEB-13     726
05-JAN-13     722
08-DEC-12     718
03-NOV-12     713
05-OCT-12     709
01-SEP-12     704

View 8 Replies View Related

PL/SQL :: Query By Current Month

Jul 12, 2013

I have an excel spreadsheet that queries an oracle data for some information.  I am trying to get the information shown to only be from the current month.  I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out.   Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".

View 6 Replies View Related

PL/SQL :: Month Wise Break

May 4, 2013

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 ?

View 6 Replies View Related

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 View Related

How To Delete One Month Old Archive Log File

Sep 16, 2010

I am not able to delete one month old archive log file on production database. it already applied on standby database. While deleting the files, it throwing errors as "Another program is using it".

Version:10.2.0.4
OS: Windows server 2003.

View 5 Replies View Related







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