June 10, 2010 at 7:43 am
Hi,
I am having a situation where the table has many records with same name and customer id but htey have different date column, and my problem is that i need to keep only one record per person with the newest date and send the records with older dates to a history table.
My table looks like this
---------------------------------------------------------------------------------------------------
Name cust_id date_completed
--------------------------------------------------------------------------------------------------
madan 1234567 04/01/2008
raj 5310 12/24/2009
madan 1234567 04/12/2008
madan 1234567 07/18/2008
raj 5310 01/23/2010
now I want the final out put like this
table 1(main table) table 2(history table)
--------------------------------- ----------------------------------------------
Name cust_id date_completed Name cust_id date_completed
---------------------------------- -----------------------------------------------
madan 1234567 07/18/2008 madan 1234567 04/01/2008
raj 5310 01/23/2010 raj 5310 12/24/2009
madan 1234567 04/12/2008
please can any one tell me how to achive this
Thanks In advance
Vasu
June 10, 2010 at 8:15 am
chereku - You have been on this site frequently enough to know that posting your question should include table definitions, and easily useable sample data.
For your next forum question, Please, please, post following the suggestions contained in the first link in my signature block.
This may be what you require:
CREATE TABLE #T (Name VARCHAR(20), cust_id INT, date_completed DATETIME)
INSERT INTO #T
SELECT 'madan', 1234567,'04/01/2008' UNION ALL
SELECT 'raj', 5310,'12/24/2009' UNION ALL
SELECT 'madan', 1234567,'04/12/2008' UNION ALL
SELECT 'madan', 1234567,'07/18/2008' UNION ALL
SELECT 'raj', 5310,'01/23/2010'
;with numbered as(SELECT rowno=row_number() over
(partition by Name, Cust_id order by date_Completed DESC),Name,Cust_Id,date_Completed from #T)
select * from numbered
Results:
rownoNameCust_Iddate_Completed
1madan12345672008-07-18 00:00:00.000
2madan12345672008-04-12 00:00:00.000
3madan12345672008-04-01 00:00:00.000
1raj53102010-01-23 00:00:00.000
2raj53102009-12-24 00:00:00.000
You keep the data where the rowno value is 1, higher values of rowno go to your history table.
June 10, 2010 at 8:25 am
Hi Vasu
There are several solutions to your problem. I assume that a primary key/unique constraint exists on cust_id and date_completed. I refer to the two tables as main and history.
Solution 1 (And probably the cleanest):
delete from
m
output
deleted.Name,
deleted.cust_id,
deleted.date_completed
into
history (Name, cust_id, date_completed)
from
main m
where
exists(select
1
from
main
where
cust_id = m.cust_id
group by
cust_id
having
m.date_completed < max(date_completed));
Solution 2:
delete from
m
output
deleted.Name,
deleted.cust_id,
deleted.date_completed
into
history (Name, cust_id, date_completed)
from
main m
cross apply
(select
latest_date_completed = max(date_completed)
from
main
where
cust_id = m.cust_id
group by
cust_id) x
where
m.date_completed < x.latest_date_completed;
Solution 3:
with cte as
(
select
*,
Newest = case
when row_number() over(partition by cust_id order by date_completed desc) = 1
then 1
else 0
end
from
main
)
delete from
m
output
deleted.Name,
deleted.cust_id,
deleted.date_completed
into
history (Name, cust_id, date_completed)
from
main m
inner join
cte
on
cte.cust_id = m.cust_id
and cte.date_completed = m.date_completed
and cte.Newest = 0;
I hope you find it useful.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply