Delete date

#1

hello everyone,

Can someone help me how to create sql statements to delete the table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
where statistics_date < add_months(sysdate,-3);

and also how to create sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

I have no idea about it..

#2

Comments embedded.

On Oct 23, 9:47*pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how *to create sql statements to delete the *table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
*where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create *sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

#3

Comments embedded.

On Oct 23, 9:47*pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how *to create sql statements to delete the *table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
*where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create *sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

#4

Comments embedded.

On Oct 23, 9:47*pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how *to create sql statements to delete the *table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
*where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create *sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

#5

Comments embedded.

On Oct 23, 9:47*pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how *to create sql statements to delete the *table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
*where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create *sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

#6

"ddf" <oratune (AT) msn (DOT) com> schreef in bericht
news:d90b62a6-74a5-4f2b-8cfa-2349b0f45383 (AT) v72g2000hsv (DOT) googlegroups.com...
Comments embedded.

On Oct 23, 9:47 pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how to create sql statements to delete the table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

============================================
Commit maybe?

Shakespeare

#7

"ddf" <oratune (AT) msn (DOT) com> schreef in bericht
news:d90b62a6-74a5-4f2b-8cfa-2349b0f45383 (AT) v72g2000hsv (DOT) googlegroups.com...
Comments embedded.

On Oct 23, 9:47 pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how to create sql statements to delete the table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

============================================
Commit maybe?

Shakespeare

#8

"ddf" <oratune (AT) msn (DOT) com> schreef in bericht
news:d90b62a6-74a5-4f2b-8cfa-2349b0f45383 (AT) v72g2000hsv (DOT) googlegroups.com...
Comments embedded.

On Oct 23, 9:47 pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how to create sql statements to delete the table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

============================================
Commit maybe?

Shakespeare

#9

"ddf" <oratune (AT) msn (DOT) com> schreef in bericht
news:d90b62a6-74a5-4f2b-8cfa-2349b0f45383 (AT) v72g2000hsv (DOT) googlegroups.com...
Comments embedded.

On Oct 23, 9:47 pm, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

hello everyone,

Can someone help me how to create sql statements to delete the table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
where statistics_date < add_months(sysdate,-3);

Define 'its not working'.because it certainly appears to do the job
where I sit:

SQL> select count(*)
2 from gen_api_performance_daily
3 where statistics_date < add_months(sysdate, -3)
4 /

COUNT(*)
----------
1000

SQL> delete from gen_api_performance_daily
2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>

Quote:

and also how to create sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

A similar statement would do the job:

SQL> insert into gen_api_performance_daily
2 select add_months(statistics_date, -24), statistics_year - 2
3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
-24), 'RRRR'))
3 /

3000 rows deleted.

SQL>

Quote:

I have no idea about it..

David Fitzjarrell

============================================
Commit maybe?

Shakespeare