SQL Server Performance

How to get the maximum of time the employee falls in which shift.

Discussion in 'SQL Server 2005 General Developer Questions' started by baburk, Aug 25, 2010.

  1. baburk New Member

    Hi,
    DECLARE @CheckInTime AS TABLE(InTime DATETIME)

    INSERT INTO @CheckInTime
    SELECT '1900-01-01 11:50:00.000' UNION ALL
    SELECT '1900-01-01 06:45:00.000' UNION ALL
    SELECT '1900-01-01 16:20:00.000' UNION ALL
    SELECT '1900-01-01 21:30:00.000'

    SELECT * FROM @CheckInTime


    DECLARE @ShiftMaster AS TABLE([ID] [int],
    [Name] [varchar](50) NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL)

    INSERT INTO @ShiftMaster
    SELECT 1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALL
    SELECT 7, 'F', '1900-01-01 06:00:00.000', '1900-01-01 14:00:00.000' UNION ALL
    SELECT 8, 'S', '1900-01-01 14:00:00.000', '1900-01-01 21:30:00.000' UNION ALL
    SELECT 9, 'T', '1900-01-01 21:30:00.000', '1900-01-01 02:30:00.000'

    SELECT * FROM @ShiftMaster


    The table @CheckInTime conatins the In time of an employee. The @ShiftMaster contains the shift.
    How to get the maximum of time the employee falls in which shift.

    Thanks,
    Babu Kumarasamy.
  2. FrankKalis Moderator

    Aren't you missing the link to the employee who checked in @CheckInTime? Or what would you expect as result from the above data?
  3. baburk New Member

    Thanks for the quick reply Frank Kalis.
    Now I changed the table structure.
    DECLARE @CheckInTime AS TABLE(EmpID INT, InTime DATETIME)INSERT
    INTO @CheckInTime SELECT
    1, '1900-01-01 11:50:00.000' UNION ALLSELECT
    2, '1900-01-01 06:45:00.000' UNION ALLSELECT
    3, '1900-01-01 16:20:00.000' UNION ALLSELECT
    4, '1900-01-01 21:30:00.000'SELECT
    * FROM @CheckInTimeDECLARE
    @ShiftMaster AS TABLE([ID] [int],[Name] [varchar]
    (50) NOT NULL,[StartTime] [datetime]
    NOT NULL,[EndTime] [datetime]
    NOT NULL)INSERT
    INTO @ShiftMasterSELECT
    1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALLSELECT
    7, 'F', '1900-01-01 06:00:00.000', '1900-01-01 14:00:00.000' UNION ALLSELECT
    8, 'S', '1900-01-01 14:00:00.000', '1900-01-01 21:30:00.000' UNION ALLSELECT
    9, 'T', '1900-01-01 21:30:00.000', '1900-01-01 02:30:00.000'SELECT
    * FROM @ShiftMaster
    Thanks,
    Babu Kumarasamy
  4. FrankKalis Moderator

    I'm a bit unsure that you mean by "maximum of time", but this might be a start.
    SELECT
    *
    FROM
    @ShiftMaster SM
    JOIN
    @CheckInTime CIT ON (SM.StartTime <= CIT.InTime AND SM.EndTime >= CIT.InTime) AND
    SM.ID = CIT.EmpID
    Otherwise it would be good if you could explain what you would like to see as the result from your sample data.
  5. baburk New Member

    Sorry FrankKalis. once again I need changed the Table structure.
    Thanks for the reply.DECLARE
    @CheckInTime AS TABLE(EmpID INT, InTime DATETIME, OutTime DATETIME)INSERT
    INTO @CheckInTime SELECT
    1, '1900-01-01 00:50:00.000', '1900-01-01 08:50:00.000' UNION ALLSELECT
    2, '1900-01-01 09:45:00.000', '1900-01-01 14:45:00.000' UNION ALLSELECT
    3, '1900-01-01 14:20:00.000', '1900-01-01 22:45:00.000' UNION ALLSELECT
    4, '1900-01-01 08:30:00.000', '1900-01-01 06:45:00.000'--SELECT * FROM @CheckInTimeDECLARE
    @ShiftMaster AS TABLE([ShiftID] [int],[Name] [varchar]
    (50) NOT NULL,[StartTime] [datetime]
    NOT NULL,[EndTime] [datetime]
    NOT NULL)INSERT
    INTO @ShiftMasterSELECT
    1, 'G', '1900-01-01 09:30:00.000', '1900-01-01 22:00:00.000' UNION ALLSELECT
    7, 'F', '1900-01-01 00:00:00.000', '1900-01-01 08:00:00.000' UNION ALLSELECT
    8, 'S', '1900-01-01 08:00:00.000', '1900-01-01 16:00:00.000' UNION ALLSELECT
    9, 'T', '1900-01-01 16:00:00.000', '1900-01-01 23:59:00.000'There is no relationship between @ShiftMaster and @CheckInTime.
    Here emp 1 should comes in F shift. He also falls in S shift. But he works more than F shift when compared to S shift
    Here emp 2 should comes in G shift
    Here emp 3 should comes in T shift (because he works more time in T shift)
    Here emp 4 should comes in S shift
    I wants to get (ShiftID) in which shift he works for more time.
    Thanks,
    Babu Kumarasamy.

Share This Page