how to delete recs,that is duplicate&not in DT ran

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to delete recs,that is duplicate&not in DT ran

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2008-11-10 : 14:51:34

Hello All,

i have a leaveTemp table with below data

UserID, UserName, fromdate,todate
101, wiran, 11/7/2008, 11/30/2008
102, Madison,11/1/2008, 12/1/2008
103, Sinka , 11/3/2008, 11/11/2008
104, Padis, 10/1/2008, 11/21/2008
105, Jikki, 9/10/2008, 9/25/2008
105, Jikki, 10/6/2008, 10/18/2008

from the above data how can i remove the duplicate record(based on userid)
and needs to remove the one, where getdate not in FromDate & toDate
example: in the above data 5th record needs to be delete because today (10/10/2008) not between 9/10/2008 and 9/25/2008

please give me an idea

Thanks & Best Regards
asin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 15:16:23

DELETE
FROM leaveTemp
WHERE GETDATE() NOT BETWEEN fromDate AND toDate

E 12°55'05.63"
N 56°04'39.26"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-11-10 : 15:27:47

code:


declare @temp table
(UserID int 
,UserName nvarchar(100)
,fromdate datetime
,todate datetime
)

insert into @temp 
select 101, 'wiran', '11/7/2008', '11/30/2008' union all
select 102, 'Madison','11/1/2008', '12/1/2008' union all
select 103, 'Sinka', '11/3/2008', '11/11/2008' union all
select 104, 'Padis', '10/1/2008', '11/21/2008' union all
select 105, 'Jikki', '9/10/2008', '9/25/2008' union all
select 105, 'Jikki', '11/6/2008', '11/18/2008'

select * from @temp 

delete from @temp
where
exists (select t2.userid
			from @temp as t2
			where userid = t2.userid
			group by t2.userid
			having count(userid) > 1)
and getdate() not  between fromdate and todate

select * from @temp

dhani
Posting Yak Master

132 Posts

Posted - 2008-11-10 : 16:12:19

quote:
DELETE
FROM leaveTemp
WHERE GETDATE() NOT BETWEEN fromDate AND toDate

Hello Pesso,

Thank you for your reply
however, your suggestion will reomve rows of all table records, i need to apply this condition only for duplicate records
not for whole tables

Best Regards
asin

dhani
Posting Yak Master

132 Posts

Posted - 2008-11-10 : 16:13:17

Hello Slimt,

thank you for your reply,

with your query, it is removing all duplicate rows, regardless of fromdate & todate

Can you please advice

Best Regards
asin

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-11-10 : 16:16:30

DELETE
FROM leaveTemp
WHERE GETDATE() NOT BETWEEN fromDate AND toDate
and userID in (select userID from leaveTemp
group by userID
having count(*) > 1

)

dhani
Posting Yak Master

132 Posts

Posted - 2008-11-10 : 16:25:21

quote:
Originally posted by GhantaBro

DELETE
FROM leaveTemp
WHERE GETDATE() NOT BETWEEN fromDate AND toDate
and userID in (select userID from leaveTemp
group by userID
having count(*) > 1

)

Hello GhantaBro

it is worked very well

thank you for your reply

Best Regards
asin

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-10 : 16:36:55

quote:
Originally posted by dhani

Hello Slimt,

thank you for your reply,

with your query, it is removing all duplicate rows, regardless of fromdate & todate

Can you please advice

Best Regards
asin

Slimt's sql works the same logic as Ghanta's and better. You must copied wrong.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 18:32:30

For SQL 2005:

Delete T from
(select row_number() over(partition by UserID order by UserID) as row_number, * from table
where getdate() not between fromdate and todate)T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 23:06:18

quote:
Originally posted by sodeep

For SQL 2005:

Delete T from
(select row_number() over(partition by UserID order by UserID) as row_number, * from table
where getdate() not between fromdate and todate)T

whats the purpose of row_numebr here? you're not using it for anything.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 23:11:25

quote:
Originally posted by sodeep

For SQL 2005:

Delete T from
(select ~~row_number() over(partition by UserID order by UserID) as row_number,~~ * from table
where getdate() not between fromdate and todate)T

You are right Visakh!! Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 23:12:23

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-11 : 01:50:03

quote:
Originally posted by sodeep

For SQL 2005:

Delete T from
(select row_number() over(partition by UserID order by UserID) as row_number, * from table
where getdate() not between fromdate and todate)T

Did you mean something like point 6?
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 01:53:51

But as per OPs initial post i dont think we need row number here. It seems like he just need to get record between the correct date range. Only if there's a chance of duplicate record on same date range, we require this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources