deleting - SQL Server Forums

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

deleting

Author

Topic

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-07 : 07:57:47

[code]
create table emp
(
empno int,
intime datetime null,
outtime datetime null
)

insert into emp values('100','01-01-2010 9:34:12 AM',NULL)
insert into emp values('101','01-01-2010 8:40:12 AM',NULL)
insert into emp values('102','01-01-2010 9:10:12 AM',NULL)
insert into emp values('100',NULL,'01-01-2010 10:45:12 AM')
insert into emp values('101',NULL,'01-01-2010 09:55:48 AM')
insert into emp values('102',NULL,'01-01-2010 10:15:16 AM')
insert into emp values('100','01-01-2010 11:00:02 AM',NULL)
insert into emp values('101','01-01-2010 10:05:42 AM',NULL)
insert into emp values('102','01-01-2010 10:35:02 AM',NULL)
insert into emp values('100',NULL,'01-01-2010 01:35:51 PM')
insert into emp values('101',NULL,'01-01-2010 12:35:48 PM')
insert into emp values('102',NULL,'01-01-2010 12:05:36 PM')

select * from emp
drop table emp
[/code]
i want only first intime and last outtime.
it is nothing just min(intime) and max(outtime)
the remaining data must be deleted i was trying this code to execute but fails
[code]
delete T where ('empno'and'intime'and'outtime') not in
(
select empno,min(intime),max(outtime) from emp group by empno
)T
[code]
can hope some one will help thanks for considering
o/p after deleting is

[code]
empno firstintime lastouttime
100 2010-01-01 09:34:12.000 2010-01-01 13:35:51.000
101 2010-01-01 08:40:12.000 2010-01-01 12:35:48.000
102 2010-01-01 09:10:12.000 2010-01-01 12:05:36.000
[/code]

With Regards
Kashyap M

Devart
Posting Yak Master

102 Posts

Posted - 2010-10-07 : 08:36:23

Hi,

For example:

delete from emp
from emp e
inner join (select empno, min(intime) as intime,max(outtime) as outtime from emp group by empno) x
on e.empno=x.empno
where
(e.intime is null and e.outtime<x.outtime) or
(e.outtime is null and e.intime>x.intime)

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-07 : 09:12:45

hi devart this work very nicely but it is not giving the final o/p i mean the both must be in one column it helps very much if you can modify your code

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-07 : 09:14:07

when i execute select * from emp the o/p must be like this hope you will understand thanks for considering


empno         firstintime               lastouttime
100    	2010-01-01 09:34:12.000      	2010-01-01 13:35:51.000
101    	2010-01-01 08:40:12.000	        2010-01-01 12:35:48.000
102    	2010-01-01 09:10:12.000      	2010-01-01 12:05:36.000

With Regards
Kashyap M

Devart
Posting Yak Master

102 Posts

Posted - 2010-10-07 : 09:27:33

Hi

As well? :)

update e
set outtime=(select max(outtime) from emp where empno=e.empno)
from emp e
where
e.intime is not null and
intime=(select min(intime) from emp where empno=e.empno)

delete from emp where intime is null or outtime is null

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-08 : 00:25:35

thanks Devart it worth's me a lot cheers to you thanks again

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-10-08 : 01:26:33

Your requirement does not make sense.
Why would you want to update an existing physical table?Are you sure there wont be any future records inserted in the table?
Can't you just use the query to display the records from the table in the required format?

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-08 : 02:36:10

no the query is given because in a day so much data is added to the table the size of the DB is getting very much so we need just to delete all except the first i/p time and last o/p time this is for a scratch system which stores the card details in the DB

With Regards
Kashyap M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources