Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: H/W regarding datetime command

    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
    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 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 Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This will count the number of minutes someone worked, for all employees:
    Code:
    select empid, EmpName, SUM(DATEDIFF(mi, EmpInTime, EmpOutTime))
    from emptime
    GROUP BY empid, EmpName
    ORDER BY empid, EmpName
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    tnks wim it works like a champ but plz check the data again it shows some diff result
    Code:
    empid empname  **
    1	Kashyap	71
    2	HK	181
    3	Sriram	51
    4	Kashyap	80
    5	Bharavi	151
    6	Pardhu	81
    7	Bhushan	31
    8	Pardhu	166
    9	Kumar	55
    10	Kashyap	197
    11	Sriram	155
    12	Bharavi	-545
    13	Bhushan	-555
    14	Kumar	165
    15	HK	         121
    16	Ravinder	121
    can you modify your code plz
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    can any help me i want to delete the reords between this code
    Code:
    delete t from
    (
    select empname, min(Empintime), max(empouttime) from emptime group by empname
    )t where empname<>1
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You want to delete all records where empname <> 1?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i tried to put only one empname condition
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not sure what you mean.
    Code:
    delete 
    from emptime 
    where empname<>1
    This looks like what I think you are trying to do but please do not run this unless you are sure it is correct. And make sure you have a backup. Ideally, test it in a development environment (but I just have a feeling you don't have one).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i am trying to delete all the data which in between the min(empintime) and max(empouttime)
    trying to put a condition which will delete the records but failed
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    can you help me pootle regarding the code for deleting plz
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm sorry but I don't understand the question.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i will explain you clearly wt my need is
    i had a table in which every employee intime and outime will be saved you can see the sample data in the above what i want is just i want min(empintime) and max(empouttime)
    want to delete all remaining data
    in a day so many records will be stored in the data so just need is first-in-time and last-out-time of the employee
    hope i expressed my need clearly
    plz help
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by mathukumali View Post
    tnks wim it works like a champ but plz check the data again it shows some diff result
    Code:
    empid empname  **
    1	Kashyap	71
    ...
    11	Sriram	155
    12	Bharavi	-545
    13	Bhushan	-555
    14	Kumar	165
    ..
    can you modify your code plz
    Those results are OK, given your data to work with. If this is not what you intended it to be, you will have to modify your data:
    Code:
    (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' )
    ...
    Those people stopped working before they even got started. Stop using AM/PM, stick to 24 hour display. This is how SQL Server interpreted your data, converted to 24 hour notation.
    Code:
    ...
    (12,'Bharavi','2010-01-01 12:00:12', '2010-01-01 02:55:34','ProjectManager' )
    insert into emptime values 
    (13,'Bhushan','2010-01-01 10:10:12', '2010-01-01 00:55:34','Sr.programmer' )
    ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    yes mr.wim you are right right i cant change the format but trying to delete the records between min() and max() i,e i want just first intime and lastout time remaining must be deleted trying to work it on but failed can you help i want just this result after deleting the remaining data
    Code:
    select empname, min(Empintime), max(empouttime) from emptime group by empname
    plz consider
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Are you sure you actually want to DELETE all those data? Are you allowed to destroy those historic data? In other words, are you 100% sure that no-one else presently is already using those data or will ever need those data for whatever purpose?

    I think you want to remove records to make your processing easier because you are new to SQL, but you should keep in mind those data may be needed for other purposes, now or in the future.
    Could it be that you want the lowest in time and highest out time per person per day and preferably in a way that it doesn't destroy any present data?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i am new but i am sure its a small task given to me and i had all the permissions too the records are exceeding and occupying huge space so need to modify all the data with only intime and outtime
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •