SQL Server Performance

Excess/Short

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, May 31, 2013.

  1. immad Member



    i have this type of table

    CREATE TABLE attendance
    (
    Date datetime,
    Timein datetime,
    Timeout datetime,
    Spend nvarchar(50),
    excessshort nvarchar(50)
    )

    my data look like this.

    Date
    2013-01-01 00:00:00.000

    Timein
    2013-01-01 09:14:00.000

    Timeout
    2013-01-01 19:06:00.000

    SpendTime
    09:52:00

    excess/short
    ?

    i want to calculate excesshort

    excess short comes from spent time mean a employee spent 9 hours 52 minute then its excess 00:52 minutes and if employee spent 8 hours 44 minutes then its short 00:16 minutes we have 9 to 6 hours timing shift.

    Thanks For Helping me
  2. Shehap MVP, MCTS, MCITP SQL Server

    If I can understand it well , you want to calculate the time spent compared to 9 hours , is it shorter or longer and how much it is shorter or longer in minutes..? If so you can try the below query:

    select datediff(MINUTE,Timeout,Timein) -540 from attendance

    Please let me know if this meets your need
  3. immad Member

    hi
    well this query is working fine but there is a problem actullay this is my mistake.in our factory there is a different
    duty hours some employee works 9 hours some work 7 hours so they make a shift table and insert a every shift hours. so 540 is 9 hours but what about those employee who have 7 or 8 hours.
  4. Shehap MVP, MCTS, MCITP SQL Server

    If it is the case, you may try the below query but I am presuming first you have a table for Employee's tme sheet named employees_shift
    select (datediff(MINUTE,Timeout,Timein)-employees_shift.hournumber ) as attendace_difference from attendance innerjoin employees_shift on attendance.Empid=employees_shift.empid

    Please let me know if any further help is needed´╗┐
  5. immad Member

    Dear Sir,

    this is my query

    select
    Date,
    [Time in],
    [Time out],
    CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
    CAST (ABS( 540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST (ABS( 540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar ) as excesstime,
    CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= 540 THEN 'Excess' ELSE 'Short' END
    ExcessShort
    FROM trans where employeecode = 17090

    and this is my shift table that i make as per your instruction but my senior developer told me that not to add employee id.

    CREATE TABLE SHIFT
    (
    CompanyID int,
    BranchID int,
    ShiftID int,
    ShiftName varchar(50),
    LongName varchar(50),
    SType varchar(50),
    TimeIn datetime,
    TimeOutdatetime,
    LTime datetime,
    HDTime datetime,
    Night int,
    TotalTime datetime,
    )

    and this is my data of shift Table

    CompanyID-----------1
    BranchID------------1
    ShiftID---------------1
    ShiftName-------------G
    LongName------------------GENERAL SHIFT
    Stype-------------------------------null
    timein------------------------------1/23/2013 9:00:00 AM
    Timeout---------------------------1/23/2013 6:00:00 PM
    Ltime--------------------------------1/23/2013 9:16:00 AM
    HdTime----------------------------1/23/2013 2:00:00 PM
    Night---------------------------------0
    TotalTime-------------------------6/3/2013 9:00:00 AM

    i want TotalTime column Replace 540 in above query so if we inserts more shifts in this table.so query automatically calculate its spend time and excess short.TotalTime Column in shift table is adding timein and timeout of shift table
  6. Shehap MVP, MCTS, MCITP SQL Server

    The Table shift is just a lookup table so if you want to do a join between Trans table and this table based on each employee , you should have to initiate another table called for ex: Shift_employee to match between each employee code and his related shift then you can conduct your query easily by inner join between tran table and Shift_employee based on employeecode like below

    select
    Date,
    [Time in],
    [Time out],
    CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
    CAST(ABS(( datediff(M, SHIFT.TimeIn,shift.TimeOut )- DATEDIFF(MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST(ABS( datediff(M, SHIFT.TimeIn,shift.TimeOut )) % 60 as varchar ) as excesstime,
    CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= datediff (M, SHIFT.TimeIn,shift.TimeOut ) THEN 'Excess' ELSE 'Short' END
    ExcessShort
    FROM trans inner join Shift_employee on trans. employeecode=Shift_employee. employeecodeinner join shift on Shift_employee.ShiftID=SHIFT.ShiftID where employeecode = 17090

    Please check out it and let me know your feedback
  7. immad Member

    this problem is solve i made some changes and query is working fine
    thanks for your help

Share This Page