June 10, 2008 at 3:45 pm
Hi,
I have 2 tables.
1. current_rate
2. past_rates
my past rate table is really big and its slow when I query it so I want to clean it up ONCE a month by backing up useless/older rows from past rate to a 3rd table that I will call backuprates.
I have carrier, country,rates, effective_dates as the important columns in all 3 tables
you can have multiple countries for the same carrier and multiple effective_dates for the same carrier
I want to have only the 2 most effective_dates per country in the past_rates table.
example:
select carrier, country, rate, effective_date
from backupratetable
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc
gives me:
carrier country rate effective_date
asiatelecom france 0.51 2008-06-8
asiatelecom france 0.62 2008-05-12
asiatelecom france 0.72 2007-02-4
asiatelecom france 0.81 2005-03-24
asiatelecom egypt 1.5 2008-06-8
asiatelecom egypt 1.62 2008-01-22
.
.
.
zambiatele india 0.4 2008-05-4
zambiatele india 0.8 2007-06-2
zambiatele italy 0.6 2005-03-2
I want to keep the 2 most recent effective dates for all carriers and countrytype . .
If you could Please Help give a shot at my query, that will be fantastic!
If something is not clear , Please ask!
THANK YOU.
P.S: I have 97 carriers and 5012 countries (includes cellular) , so deleting the older effective dates manually will be wayyy too much:unsure:
June 10, 2008 at 4:07 pm
Here is some code to get you started:
create table dbo.backupratetable (
carrier varchar(50),
country varchar(50),
rate decimal(18,4),
effective_date datetime
);
insert into dbo.backupratetable
select 'asiatelecom', 'france', 0.51, '2008-06-8' union all
select 'asiatelecom', 'france', 0.62, '2008-05-12' union all
select 'asiatelecom', 'france', 0.72, '2007-02-4' union all
select 'asiatelecom', 'france', 0.81, '2005-03-24' union all
select 'asiatelecom', 'egypt' , 1.5 , '2008-06-8' union all
select 'asiatelecom', 'egypt' , 1.62, '2008-01-22' union all
select 'zambiatele' , 'india' , 0.4 , '2008-05-4' union all
select 'zambiatele' , 'india' , 0.8 , '2007-06-2' union all
select 'zambiatele' , 'italy' , 0.6 , '2005-03-2';
select carrier, country, rate, effective_date
from backupratetable
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc;
with ratetable (
rownumber,
carrier,
country,
effective_date
) as (
select
row_number() over (partition by carrier, country order by carrier, country, effective_date desc) as rownumber,
carrier,
country,
effective_date
from
backupratetable
)
delete from
backupratetable
from
backupratetable brt
inner join ratetable rt
on (brt.carrier = rt.carrier
and brt.country = rt.country
and brt.effective_date = rt.effective_date)
where
rt.rownumber > 2;
select carrier, country, rate, effective_date
from backupratetable
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc;
drop table dbo.backupratetable;
Happy Coding!
😎
June 10, 2008 at 7:16 pm
That did the trick!
Thanks a million Lynn!:D
June 10, 2008 at 7:44 pm
Now, you should post what you did to complete your process so that others can see it also. You never know, someone else may have a similiar problem that needs to be resolved. That's another reason for keeping things in your thread instead of PMing someone.
😎
June 10, 2008 at 8:31 pm
MY PM TO LYNN
Hi Lynn,
This is my first post, I dont know if Im suppose to keep it on the forum. I wanted to make sure you see it.. [BigGrin]
Thanks a million for helping me by the way.
My question is this. (im using mssql 2005)
1. Do I need to reinsert the data into backupratetable since its already there?
What I did was
select *
into backupratetable
from pastrate
Sorry if I was not clear, I just want to clean up my past_rate table
So do I just substitute backupratetable with pastrate in the code below?
and is when u say 'ratetable' do you mean 'current_rate' table?
with ratetable (
rownumber,
carrier,
country,
effective_date
) as (
select
row_number() over (partition by carrier, country order by carrier, country, effective_date desc) as rownumber,
carrier,
country,
effective_date
from
backupratetable
)
delete from
backupratetable
from
backupratetable brt
inner join ratetable rt
on (brt.carrier = rt.carrier
and brt.country = rt.country
and brt.effective_date = rt.effective_date)
where
rt.rownumber > 2;
select carrier, country, rate, effective_date
from backupratetable
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc;*
Thanks!!
LYNN'S REPLY
Yes, you really should have posted this back on the same thread. That way others could also help should they see your question before me.
In my query that I provided, ratetable is the CTE defined just before the delete statement.
No, you don't need to reinsert any data. You can expand on the delete statement to use the OUTPUT clause to capture the data deleted into your third table (can't remember what you were calling it).
Take some time to read BOL (Books On-Line). I haven't used the OUTPUT clause, but if you have questions about it after reading about it and playing with it, post your questions. I'm sure there are people out there who will be more than willing to assist.
If I used backratetable instead of past_rate, then yes, use past_rate in its place in both the DELETE statement and the CTE definition.
Let me know if you have any more questions.
[Cool]
June 14, 2008 at 2:43 pm
For what it's worth, assuming pastRate and BackupRate have the same structure, your reworked statement would look something like (with the OUTPUT statement).
;with ratetable (
rownumber,
carrier,
country,
effective_date
) as (
select
row_number() over (partition by carrier, country
order by carrier, country, effective_date desc) as rownumber,
carrier,
country,
effective_date
from
pastRateTable
)
delete pastRateTable
--changes are here
OUTPUT deleted.carrier,deleted.country,deleted.effective_date
INTO backupRateTable(carrier,country,effectivedate)
--changes end here
from
pastRateTable brt
inner join ratetable rt
on (brt.carrier = rt.carrier
and brt.country = rt.country
and brt.effective_date = rt.effective_date)
where
rt.rownumber > 2;
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply