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
 H/w regarding datetime command

Author  Topic 

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 01:57:19
hai to all i had a table which contains the intime & out time
i was trying to know how to get the worked hours of the emps in the company

create table emptime
(
empid int not null,
EmpName varchar(40) not null,
EmpInTime datetime, 
EmpOutTime datetime,
Title varchar(40) not null
)

insert into emptime Values
( 1,'Kashyap','2010-01-01 9:34:12 AM', '2010-01-01 10:45:34 AM','DbDeveloper') 
insert into emptime values 
(2,'HK','2010-01-01 10:34:12 AM', '2010-01-01 1:35:34 PM','MD')
insert into emptime values 
(3,'Sriram','2010-01-01 9:34:12 AM', '2010-01-01 10:25:34 AM','HR' )
insert into emptime values 
(4,'Kashyap','2010-01-01 10:55:12 AM', '2010-01-01 12:15:34 PM','DbDeveloper')
insert into emptime values
(5,'Bharavi','2010-01-01 8:34:12 AM', '2010-01-01 11:05:34 AM','ProjectManager')
insert into emptime values 
(6,'Pardhu','2010-01-01 9:34:12 AM', '2010-01-01 10:55:34 AM','Accountant' )
insert into emptime values
(7,'Bhushan','2010-01-01 9:34:12 AM', '2010-01-01 10:05:34 AM','Sr.programmer' )
insert into emptime values 
(8,'Pardhu','2010-01-01 11:49:12 AM', '2010-01-01 02:35:34 PM','Accountant' )
insert into emptime values 
(9,'Kumar','2010-01-01 9:30:00 AM', '2010-01-01 10:25:04 AM','Programmer' )
insert into emptime values 
(10,'Kashyap','2010-01-01 12:58:12 PM', '2010-01-01 4:15:34 PM','DbDeveloper' )
insert into emptime values 
(11,'Sriram','2010-01-01 11:00:12 AM', '2010-01-01 1:35:34 PM','HR' )
insert into emptime values 
(12,'Bharavi','2010-01-01 12:00:12 PM', '2010-01-01 02:55:34 AM','ProjectManager' )
insert into emptime values 
(13,'Bhushan','2010-01-01 10:10:12 AM', '2010-01-01 12:55:34 AM','Sr.programmer' )
insert into emptime values 
(14,'Kumar','2010-01-01 10:40:00 AM', '2010-01-01 01:25:04 PM','Programmer' )
insert into emptime values 
(15,'HK','2010-01-01 01:34:12 PM', '2010-01-01 03:35:34 PM','MD')
drop table emptime

i hope some one can help me from this

With Regards
Kashyap M

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts
Posted - 2010-10-05 : 04:12:53
Have a look at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74645
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 04:28:39
tnks for your response i had already watched the poll but fail to understand

With Regards
Kashyap M
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-10-05 : 04:38:48
Maybe this

create table emptime
(
empid int not null,
EmpName varchar(40) not null,
EmpInTime datetime, 
EmpOutTime datetime,
Title varchar(40) not null
)

insert into emptime Values
( 1,'Kashyap','2010-01-01 9:34:12 AM', '2010-01-01 10:45:34 AM','DbDeveloper') 
insert into emptime values 
(2,'HK','2010-01-01 10:34:12 AM', '2010-01-01 1:35:34 PM','MD')
insert into emptime values 
(3,'Sriram','2010-01-01 9:34:12 AM', '2010-01-01 10:25:34 AM','HR' )
insert into emptime values 
(4,'Kashyap','2010-01-01 10:55:12 AM', '2010-01-01 12:15:34 PM','DbDeveloper')
insert into emptime values
(5,'Bharavi','2010-01-01 8:34:12 AM', '2010-01-01 11:05:34 AM','ProjectManager')
insert into emptime values 
(6,'Pardhu','2010-01-01 9:34:12 AM', '2010-01-01 10:55:34 AM','Accountant' )
insert into emptime values
(7,'Bhushan','2010-01-01 9:34:12 AM', '2010-01-01 10:05:34 AM','Sr.programmer' )
insert into emptime values 
(8,'Pardhu','2010-01-01 11:49:12 AM', '2010-01-01 02:35:34 PM','Accountant' )
insert into emptime values 
(9,'Kumar','2010-01-01 9:30:00 AM', '2010-01-01 10:25:04 AM','Programmer' )
insert into emptime values 
(10,'Kashyap','2010-01-01 12:58:12 PM', '2010-01-01 4:15:34 PM','DbDeveloper' )
insert into emptime values 
(11,'Sriram','2010-01-01 11:00:12 AM', '2010-01-01 1:35:34 PM','HR' )
insert into emptime values 
(12,'Bharavi','2010-01-01 11:00:12 PM', '2010-01-01 02:55:34 AM','ProjectManager' )
insert into emptime values 
(13,'Bhushan','2010-01-01 10:10:12 AM', '2010-01-01 12:55:34 AM','Sr.programmer' )
insert into emptime values 
(14,'Kumar','2010-01-01 10:40:00 AM', '2010-01-01 01:25:04 PM','Programmer' )
insert into emptime values 
(15,'HK','2010-01-01 01:34:12 PM', '2010-01-01 03:35:34 PM','MD')

select empname,
       abs(sum(Hrs)/60)Hrs,
       abs(SUM(mins)%60)Mins,
       abs(SUM(secs)%60)Secs from
(
	select EmpName,
		  datediff(MI,EmpinTime,EmpoutTime)as Hrs,
		  datediff(Mi,EmpinTime,EmpoutTime)as Mins,
		  datediff(ss,EmpinTime,EmpoutTime)as secs
	 from emptime
)T
group by empname
order by empname
drop table emptime



PBUH
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 04:43:03
what a code sachit it rocks the output cheers to you and hearty thanks

With Regards
Kashyap M
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 05:01:38
one question is over and the second one raised
i want a output which must have only the last loggin and loggout in the table emptime which must delete the previous records too i,e
for ex:-

1,'Kashyap','2010-01-01 9:34:12 AM', '2010-01-01 10:45:34 AM','DbDeveloper
4,'Kashyap','2010-01-01 10:55:12 AM', '2010-01-01 12:15:34 PM','DbDeveloper'
10,'Kashyap','2010-01-01 12:58:12 PM', '2010-01-01 4:15:34 PM','DbDeveloper'


result expected is


empid  empname  hrs mns  sec
10     kashyap  ... ..   ..

3 rows effected .. .. ...

With Regards
Kashyap M
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-10-05 : 05:03:11
In the same resulset of previous query?

PBUH
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 05:05:01
no it was the first question which you helped me in getting the result but
then started the second question in mind

With Regards
Kashyap M
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-10-05 : 05:12:38
[code]
create table emptime
(
empid int not null,
EmpName varchar(40) not null,
EmpInTime datetime,
EmpOutTime datetime,
Title varchar(40) not null
)

insert into emptime Values
( 1,'Kashyap','2010-01-01 9:34:12 AM', '2010-01-01 10:45:34 AM','DbDeveloper')
insert into emptime values
(2,'HK','2010-01-01 10:34:12 AM', '2010-01-01 1:35:34 PM','MD')
insert into emptime values
(3,'Sriram','2010-01-01 9:34:12 AM', '2010-01-01 10:25:34 AM','HR' )
insert into emptime values
(4,'Kashyap','2010-01-01 10:55:12 AM', '2010-01-01 12:15:34 PM','DbDeveloper')
insert into emptime values
(5,'Bharavi','2010-01-01 8:34:12 AM', '2010-01-01 11:05:34 AM','ProjectManager')
insert into emptime values
(6,'Pardhu','2010-01-01 9:34:12 AM', '2010-01-01 10:55:34 AM','Accountant' )
insert into emptime values
(7,'Bhushan','2010-01-01 9:34:12 AM', '2010-01-01 10:05:34 AM','Sr.programmer' )
insert into emptime values
(8,'Pardhu','2010-01-01 11:49:12 AM', '2010-01-01 02:35:34 PM','Accountant' )
insert into emptime values
(9,'Kumar','2010-01-01 9:30:00 AM', '2010-01-01 10:25:04 AM','Programmer' )
insert into emptime values
(10,'Kashyap','2010-01-01 12:58:12 PM', '2010-01-01 4:15:34 PM','DbDeveloper' )
insert into emptime values
(11,'Sriram','2010-01-01 11:00:12 AM', '2010-01-01 1:35:34 PM','HR' )
insert into emptime values
(12,'Bharavi','2010-01-01 11:00:12 PM', '2010-01-01 02:55:34 AM','ProjectManager' )
insert into emptime values
(13,'Bhushan','2010-01-01 10:10:12 AM', '2010-01-01 12:55:34 AM','Sr.programmer' )
insert into emptime values
(14,'Kumar','2010-01-01 10:40:00 AM', '2010-01-01 01:25:04 PM','Programmer' )
insert into emptime values
(15,'HK','2010-01-01 01:34:12 PM', '2010-01-01 03:35:34 PM','MD')

select empid, EmpName,EmpInTime,EmpOutTime from
(
select *,row_number()over(partition by empname order by empintime desc)rid from emptime
)t where rid=1

drop table emptime
[/code]

Please make an attempt to learn more about this windowed functions from BOL or msdn.Dont be satisfied with all these spoon fed answers.Its for your own good.

PBUH
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 06:24:01
tnks sachit but it is not deleting the old records.
when card is inserted into the machine a now of records will be inserted and it will be some
hard to store all the data so in this terms so i had to delete all the old records
i will consider wt you say i was trying from past few hours and unable to retrieve the o/p

With Regards
Kashyap M
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-10-05 : 06:27:28
Show us what you have tried till now.
My query will just get the list of the records that should remain in the table & all the other records should be deleted.


PBUH
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 06:28:50
ok

With Regards
Kashyap M
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 07:00:21
i am unable to use perfect condition in my stuff like 

delete
from Emptime
where Empname not in
(
select max('Empintime'and'empouttime')
from Emptime
group by Empname, empouttime, Empintime)


With Regards
Kashyap M
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-10-05 : 07:12:32
[code]
create table emptime
(
empid int not null,
EmpName varchar(40) not null,
EmpInTime datetime,
EmpOutTime datetime,
Title varchar(40) not null
)

insert into emptime Values
( 1,'Kashyap','2010-01-01 9:34:12 AM', '2010-01-01 10:45:34 AM','DbDeveloper')
insert into emptime values
(2,'HK','2010-01-01 10:34:12 AM', '2010-01-01 1:35:34 PM','MD')
insert into emptime values
(3,'Sriram','2010-01-01 9:34:12 AM', '2010-01-01 10:25:34 AM','HR' )
insert into emptime values
(4,'Kashyap','2010-01-01 10:55:12 AM', '2010-01-01 12:15:34 PM','DbDeveloper')
insert into emptime values
(5,'Bharavi','2010-01-01 8:34:12 AM', '2010-01-01 11:05:34 AM','ProjectManager')
insert into emptime values
(6,'Pardhu','2010-01-01 9:34:12 AM', '2010-01-01 10:55:34 AM','Accountant' )
insert into emptime values
(7,'Bhushan','2010-01-01 9:34:12 AM', '2010-01-01 10:05:34 AM','Sr.programmer' )
insert into emptime values
(8,'Pardhu','2010-01-01 11:49:12 AM', '2010-01-01 02:35:34 PM','Accountant' )
insert into emptime values
(9,'Kumar','2010-01-01 9:30:00 AM', '2010-01-01 10:25:04 AM','Programmer' )
insert into emptime values
(10,'Kashyap','2010-01-01 12:58:12 PM', '2010-01-01 4:15:34 PM','DbDeveloper' )
insert into emptime values
(11,'Sriram','2010-01-01 11:00:12 AM', '2010-01-01 1:35:34 PM','HR' )
insert into emptime values
(12,'Bharavi','2010-01-01 11:00:12 PM', '2010-01-01 02:55:34 AM','ProjectManager' )
insert into emptime values
(13,'Bhushan','2010-01-01 10:10:12 AM', '2010-01-01 12:55:34 AM','Sr.programmer' )
insert into emptime values
(14,'Kumar','2010-01-01 10:40:00 AM', '2010-01-01 01:25:04 PM','Programmer' )
insert into emptime values
(15,'HK','2010-01-01 01:34:12 PM', '2010-01-01 03:35:34 PM','MD')

select * from emptime

delete t from
(
select *,row_number()over(partition by empname order by empintime desc)rid from emptime

)t where rid<>1

select * from emptime

drop table emptime

[/code]

PBUH
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 07:47:47
its so simply not even harder as i think tnks sachin need to improve my logical thinking

With Regards
Kashyap M
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-10-05 : 07:54:50
i was trying to use this command 

delete t from
(
select empname, min(Empintime), max(empouttime) from emptime group by empname
)t where empname<>1

but failed to delete the remaining records rather than excluding the output values

With Regards
Kashyap M
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site