June 12, 2011 at 6:27 pm
Hi,
I want to Delete the record number 3 and 4 which is incorrect . Those are coming by doing joins as final result and I can't bound the effective date. This is just an example. I have more than 100 incorrect records in my resultset.
I can bound them by start date but I don't know how to get the next start date by using loop, cursor or storing them in a variable to compare.
Please , suggest me the solution .
START DATE , EFFECTIVE DATE
1 8/6/07 12:00 AM , 8/6/07 12:00 AM
2 8/6/07 12:00 AM , 8/28/09 12:00 AM
3 8/6/07 12:00 AM , 7/7/10 12:00 AM
4 8/6/07 12:00 AM , 10/19/10 12:00 AM
5 10/5/09 12:00 AM , 10/5/09 12:00 AM
6 10/5/09 12:00 AM , 7/7/10 12:00 AM
7 10/5/09 12:00 AM , 8/20/10 12:00 AM
8 10/5/09 12:00 AM, 10/19/10 12:00 AM
Thanks.
June 13, 2011 at 1:25 am
following will remove only duplicate records
here is example
DECLARE @tmpTable table(
--CREATE TABLE @phonebook (
[id] [varchar] (30),
[firstname] [varchar] (30),
[lastname] [varchar] (30),
[company] [varchar] (100)
)
INSERT @tmpTable SELECT '902','suresh','bangalore','sm soft'
INSERT @tmpTable SELECT '905','ramesh','xyz','company llc'
INSERT @tmpTable SELECT '909','steve','sydney','united'
-- Duplicate insert 1
INSERT @tmpTable SELECT '902','suresh','bangalore','sm soft'
INSERT @tmpTable SELECT '909','steve','sydney','united'
--duplicate insert 2
INSERT @tmpTable SELECT '909','steve','sydney','united'
--Show Duplicate records
SELECT id, COUNT(*) FROM @tmpTable
GROUP BY id HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
SET ROWCOUNT 1
SELECT @@rowcount
WHILE @@rowcount > 0
DELETE pb FROM @tmpTable as pb
INNER JOIN
(SELECT id
FROM @tmpTable
GROUP BY id HAVING count(*) > 1)
AS c ON c.id = pb.id
SET ROWCOUNT 0
SELECT * FROM @tmpTable
June 13, 2011 at 5:35 am
Hi
Following is another way to get rid of duplicate data
DECLARE @tmpTable table(
[id] [varchar] (30),
[firstname] [varchar] (30),
[lastname] [varchar] (30),
[company] [varchar] (100)
)
INSERT @tmpTable SELECT '902','suresh','bangalore','sm soft'
INSERT @tmpTable SELECT '905','ramesh','xyz','company llc'
INSERT @tmpTable SELECT '909','steve','sydney','united'
-- Duplicate insert 1
INSERT @tmpTable SELECT '902','suresh','bangalore','sm soft'
INSERT @tmpTable SELECT '909','steve','sydney','united'
--duplicate insert 2
INSERT @tmpTable SELECT '909','steve','sydney','united'
-- original data
select * from @tmptable
DECLARE @tmpoutputTable table
(
[id] [varchar] (30),
[firstname] [varchar] (30),
[lastname] [varchar] (30),
[company] [varchar] (100)
)
insert into @tmpoutputTable
select id,firstname,lastname,company from(
SELECT id,firstname,lastname,company,
row_number() over(order by id,firstname,lastname,company ) rn,
rank() over(order by id,firstname,lastname,company ) rnk
FROM @tmpTable
) a where rn-rnk=0
-- required output
select * from @tmpoutputTable
Regards
Siva Kumar J.
June 13, 2011 at 9:00 pm
Thanks Shiva and Suresh for your Response .
But I don't want to remove duplicate rows .
There are duplicate startdates and effective dates according to student status .
If you can suggest me the logic:
The effective date should be between startdates of each student.
AND Each student have 2 or 3 startdates.
Thanks.
June 13, 2011 at 11:53 pm
Please provide the DDL for the table(s) (CREATE TABLE stetment(s)), sample data for the table(s) (a series of INSERT INTO statements s for the table(s)), expected results based on the sample data provide, and the code you have written to try and solve your problem.
If you provide all this information you will get better assistance plus tested code in return.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply