Home » SQL & PL/SQL » SQL & PL/SQL » last day of every month
last day of every month [message #186385] Mon, 07 August 2006 14:43 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
we have a huge table where data needs to be delteted on a particular condition.

This table has records for every month since January 2004. I want to delete all the records from this table but only keep the records of the last day of every month
since Jan 2004 to present.

The last day of the month is nothing but the max date for that particular month. If the last day
is a holiday data does not get loaded then. So say for april 2006 - data would have been loaded only till april 28th. So if i take the max the last day is april 28, I just want to keep data for that day and delete the other records of that month.

I want to do this for each month since starting Jan 2004 to Present.

How can I get around this writing an SQL? Appreciate your response.

Thanks
Re: last day of every month [message #186402 is a reply to message #186385] Mon, 07 August 2006 16:39 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
If you have more than one record that matches the latest day of the month and you don't want mulitple records, you will need to look into using row_number() over() or dense_rank() over() and figure out how to resolve which one you actually want.
SQL> SELECT *
  2  FROM t
  3  ORDER BY dt;

       VAL DT
---------- -----------
         2 8/8/2006
         ...
         2 8/30/2006
         5 8/31/2006
         6 9/1/2006
         2 9/2/2006
         ...
         8 9/29/2006
         4 9/30/2006
         9 10/1/2006
         8 10/2/2006
         ...
         3 10/30/2006
         5 10/31/2006
         8 11/1/2006
         6 11/2/2006
         ...
         4 11/29/2006
         2 11/30/2006
         7 12/1/2006
         9 12/2/2006
         ...
         4 12/30/2006
         9 12/31/2006
         7 1/1/2007
         2 1/2/2007
         ...
         1 1/30/2007
         4 1/31/2007
         4 2/1/2007
         2 2/2/2007
         ...
         8 2/27/2007
         2 2/28/2007
         4 3/1/2007
         6 3/2/2007
         ...
         1 3/30/2007
         8 3/31/2007
         9 4/1/2007
         3 4/2/2007
         ...

499 rows selected

SQL> CREATE TABLE tmp AS
  2  SELECT v.val
  3        ,v.dt
  4  FROM (SELECT t.*
  5              ,rank() over(PARTITION BY trunc(dt, 'MM') ORDER BY dt DESC) rn
  6        FROM t) v
  7  WHERE rn = 1;


SQL> SELECT * FROM tmp;

       VAL DT
---------- -----------
         5 8/31/2006
         4 9/30/2006
         5 10/31/2006
         2 11/30/2006
         9 12/31/2006
         4 1/31/2007
         2 2/28/2007
         8 3/31/2007
         7 4/30/2007
         8 5/31/2007
         6 6/30/2007
         1 7/31/2007
         6 8/31/2007
         4 9/30/2007
         3 10/31/2007
         4 11/30/2007
         9 12/19/2007

17 rows selected


SQL> DROP TABLE t;

Table dropped

SQL> ALTER TABLE tmp RENAME TO t;

Table altered
Re: last day of every month [message #186534 is a reply to message #186385] Tue, 08 August 2006 06:38 Go to previous messageGo to next message
sam_kasal
Messages: 1
Registered: August 2006
Location: NA
Junior Member
This query will give you the last day of every month.

select max(dt) from t1
group by to_date(to_char(dt,'mm'),'mm')

Bye
Sam:-)
Re: last day of every month [message #186552 is a reply to message #186534] Tue, 08 August 2006 08:17 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Both of these solutions overlooked one extra condition the OP threw in there, although I think OP meant weekend instead of holiday.
having to_char(max(dt),'D') not in ('1','7');

or
where to_char(dt,'D') not in ('1','7');

depnding on which solution is used.
Previous Topic: Updating Date value with Date Time
Next Topic: SQL Confirm
Goto Forum:
  


Current Time: Fri Dec 02 12:39:36 CST 2016

Total time taken to generate the page: 0.06938 seconds