none
To create a view for each employee and each date

    질문

  • I would like to create a View to know each employee  IN and OUT for each date  . At the moment  I am using the following sql to know each  IN and OUT  for single employee and for single date. But I want to create a view for all employee and for all date . First clock in treated as IN and another Clockin  treated as OUT and so on. But it  should be worked for all the employee and without date in where condition . Please help

     SELECT
    CONVERT (varchar(10), Logdate, 103) as  DateClock,
    convert(varchar(8), logdate, 108)  as ClockTime,
    Status 
    from 
    (SELECT
    EmpCode,
    DateTime,
    CASE ROW_NUMBER() OVER(PARTITION BY EmpCode ORDER BY id) % 2
    WHEN 0 THEN 'OUT'
    ELSE 'IN'
    END AS Status
    FROM
    TimeClock
    Where EmpCode= 'XXX' and CONVERT(date, LogDate)  = '06-13-2018'
    ) t
     


    polachan

    2018년 6월 13일 수요일 오후 4:00

답변

  • Your given code is not working

    polachan

    Unless you post your expected result how can we guess what you've in mind?

    I gave the suggestion based on your initial explanation 

    Anyways as per your latest post and data what you need is this

    declare @T table
    (
    Empcode int,   [DateTime]   datetime,          [status] varchar(10),          ID int)
    
    insert @t
    values
    
    (12,'2018-06-12 08:48:36.000','in',58),
    (12 ,'2018-06-12 13:01:37.000','in', 71),
    (12 ,'2018-06-12 13:33:02.000','in', 80),
    (12 ,'2018-06-12 17:10:29.000','in', 93),
    
    (20 ,'2018-06-12 08:49:36.000','in', 59),
    (20 ,'2018-06-12 13:02:37.000','in', 72),
    (20 ,'2018-06-12 13:34:02.000','in', 81),
    (20 ,'2018-06-12 17:11:29.000','in', 94),
    
    
    (21 ,'2018-06-12 08:50:36.000','in', 60),
    (21 ,'2018-06-12 13:03:37.000','in', 73),
    (21 ,'2018-06-12 13:35:02.000','in', 82),
    (21 ,'2018-06-12 17:12:29.000','in', 95),
    (21 ,'2018-06-12 17:13:29.000','in', 96),
    
    
    (12 ,'2018-06-13 08:48:36.000','in', 97),
    (12 ,'2018-06-13 13:01:37.000','in', 99),
    (12 ,'2018-06-13 13:33:02.000','in', 101),
    (12 ,'2018-06-13 17:10:29.000','in', 103),
    
    (20 ,'2018-06-12 08:49:36.000','in', 98),
    (20 ,'2018-06-12 13:02:37.000','in', 100),
    (20 ,'2018-06-12 13:34:02.000','in', 102),
    (20 ,'2018-06-12 17:11:29.000','in', 104)
    
    SELECT EmpCode,
    LogDatetime, CASE WHEN Seq % 2 = 1 THEN 'IN' ELSE 'OUT' END AS Status
    FROM
    (
    SELECT
    EmpCode,
    CAST([DateTime] AS date) AS LogDate,
    [DateTime] AS LogDatetime,
    ROW_NUMBER() OVER(PARTITION BY EmpCode,CAST([DateTime] AS date) ORDER BY [DateTime]) AS Seq
    FROM
    @t
    )t
    ORDER BY EmpCode,LogDatetime
    
    
    
    
    /*
    Output
    ----------------------------------------------------
    EmpCode	LogDatetime	Status
    -----------------------------------------
    12	2018-06-12 08:48:36.000	IN
    12	2018-06-12 13:01:37.000	OUT
    12	2018-06-12 13:33:02.000	IN
    12	2018-06-12 17:10:29.000	OUT
    12	2018-06-13 08:48:36.000	IN
    12	2018-06-13 13:01:37.000	OUT
    12	2018-06-13 13:33:02.000	IN
    12	2018-06-13 17:10:29.000	OUT
    20	2018-06-12 08:49:36.000	IN
    20	2018-06-12 08:49:36.000	OUT
    20	2018-06-12 13:02:37.000	IN
    20	2018-06-12 13:02:37.000	OUT
    20	2018-06-12 13:34:02.000	IN
    20	2018-06-12 13:34:02.000	OUT
    20	2018-06-12 17:11:29.000	IN
    20	2018-06-12 17:11:29.000	OUT
    21	2018-06-12 08:50:36.000	IN
    21	2018-06-12 13:03:37.000	OUT
    21	2018-06-12 13:35:02.000	IN
    21	2018-06-12 17:12:29.000	OUT
    21	2018-06-12 17:13:29.000	IN
    */

    Again I want to reiterate fact that this will not work correctly under below circumstances

    1. When you've have gaps in swipes i.e for any day when you dont have even number of entries

    2. Single shift crossing over a day i.e someone starting from evening to work till next morning etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 polachan 2018년 6월 15일 금요일 오전 9:18
    2018년 6월 14일 목요일 오전 6:38

모든 응답

  • this?


    SELECT EmpCode, LogDate,MIN(LogDatetime) AS INTime,MAX(LogDatetime) AS OUTTime FROM ( SELECT EmpCode, CAST(LogDate AS date) AS LogDate, LogDate AS LogDatetime, ROW_NUMBER() OVER(PARTITION BY EmpCode,CAST(LogDate AS date) ORDER BY LogDate) AS Seq FROM TimeClock )t GROUP BY EmpCode, LogDate, (Seq-1)/2



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 13일 수요일 오후 4:20
  • Hi polachan,

    Could you please share us some sample data if possible? So that we can make some test.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 3:02
  • No your given sql is not working. Please can you look my sample data

    My sample data coming from clocking device

    Empcode    DateTime             status          ID
    12 2018-06-12 08:48:36.000 in 58
    12 2018-06-12 13:01:37.000 in 71
    12 2018-06-12 13:33:02.000 in 80
    12 2018-06-12 17:10:29.000 in 93

    20 2018-06-12 08:49:36.000 in 59
    20 2018-06-12 13:02:37.000 in 72
    20 2018-06-12 13:34:02.000 in 81
    20 2018-06-12 17:11:29.000 in 94


    21 2018-06-12 08:50:36.000 in 60
    21 2018-06-12 13:03:37.000 in 73
    21 2018-06-12 13:35:02.000 in 82
    21 2018-06-12 17:12:29.000 in 95
    21 2018-06-12 17:13:29.000 in 96


    12 2018-06-13 08:48:36.000 in 97
    12 2018-06-13 13:01:37.000 in 99
    12 2018-06-13 13:33:02.000 in 101
    12 2018-06-13 17:10:29.000 in 103

    20 2018-06-12 08:49:36.000 in 98
    20 2018-06-12 13:02:37.000 in 100
    20 2018-06-12 13:34:02.000 in 102
    20 2018-06-12 17:11:29.000 in 104

    I want to get the result as follows in a view

    12 12/06/2018 08:48:36 IN
    12 12/06/2018 13:01:37 OUT
    12 12/06/2018 13:33:02 IN
    12 12/06/2018 17:10:29 OUT

    20 12/06/2018 08:49:36 IN
    20 12/06/2018 13:02:37 OUT
    20 12/06/2018 13:34:02 IN
    20 12/06/2018 17:11:29 OUT

    21 12/06/2018 08:50:36 IN
    21 12/06/2018 13:03:37 OUT
    21 12/06/2018 13:35:02 IN

    12 13/06/2018 08:48:36 IN
    12 13/06/2018 13:01:37 OUT
    12 13/06/2018 13:33:02 IN
    12 13/06/2018 17:10:29 OUT

    20 13/06/2018 08:49:36 IN
    20 13/06/2018 13:02:37 OUT
    20 13/06/2018 13:34:02 IN
    20 13/06/2018 17:11:29 OUT

    My sql is working for single employee and single date. But I dont know how to create View  for all employee  . Please can you help.

     Your given sql above will not be working . If any one can give a help , it would be very appreciate


    polachan


    • 편집됨 polachan 2018년 6월 14일 목요일 오전 6:09 spelling
    2018년 6월 14일 목요일 오전 6:04
  • I have given my sample data . Please can you go through the thread. Many Thanks

    polachan

    2018년 6월 14일 목요일 오전 6:05
  • Your given code is not working

    polachan

    2018년 6월 14일 목요일 오전 6:11
  • Your given code is not working

    polachan

    Unless you post your expected result how can we guess what you've in mind?

    I gave the suggestion based on your initial explanation 

    Anyways as per your latest post and data what you need is this

    declare @T table
    (
    Empcode int,   [DateTime]   datetime,          [status] varchar(10),          ID int)
    
    insert @t
    values
    
    (12,'2018-06-12 08:48:36.000','in',58),
    (12 ,'2018-06-12 13:01:37.000','in', 71),
    (12 ,'2018-06-12 13:33:02.000','in', 80),
    (12 ,'2018-06-12 17:10:29.000','in', 93),
    
    (20 ,'2018-06-12 08:49:36.000','in', 59),
    (20 ,'2018-06-12 13:02:37.000','in', 72),
    (20 ,'2018-06-12 13:34:02.000','in', 81),
    (20 ,'2018-06-12 17:11:29.000','in', 94),
    
    
    (21 ,'2018-06-12 08:50:36.000','in', 60),
    (21 ,'2018-06-12 13:03:37.000','in', 73),
    (21 ,'2018-06-12 13:35:02.000','in', 82),
    (21 ,'2018-06-12 17:12:29.000','in', 95),
    (21 ,'2018-06-12 17:13:29.000','in', 96),
    
    
    (12 ,'2018-06-13 08:48:36.000','in', 97),
    (12 ,'2018-06-13 13:01:37.000','in', 99),
    (12 ,'2018-06-13 13:33:02.000','in', 101),
    (12 ,'2018-06-13 17:10:29.000','in', 103),
    
    (20 ,'2018-06-12 08:49:36.000','in', 98),
    (20 ,'2018-06-12 13:02:37.000','in', 100),
    (20 ,'2018-06-12 13:34:02.000','in', 102),
    (20 ,'2018-06-12 17:11:29.000','in', 104)
    
    SELECT EmpCode,
    LogDatetime, CASE WHEN Seq % 2 = 1 THEN 'IN' ELSE 'OUT' END AS Status
    FROM
    (
    SELECT
    EmpCode,
    CAST([DateTime] AS date) AS LogDate,
    [DateTime] AS LogDatetime,
    ROW_NUMBER() OVER(PARTITION BY EmpCode,CAST([DateTime] AS date) ORDER BY [DateTime]) AS Seq
    FROM
    @t
    )t
    ORDER BY EmpCode,LogDatetime
    
    
    
    
    /*
    Output
    ----------------------------------------------------
    EmpCode	LogDatetime	Status
    -----------------------------------------
    12	2018-06-12 08:48:36.000	IN
    12	2018-06-12 13:01:37.000	OUT
    12	2018-06-12 13:33:02.000	IN
    12	2018-06-12 17:10:29.000	OUT
    12	2018-06-13 08:48:36.000	IN
    12	2018-06-13 13:01:37.000	OUT
    12	2018-06-13 13:33:02.000	IN
    12	2018-06-13 17:10:29.000	OUT
    20	2018-06-12 08:49:36.000	IN
    20	2018-06-12 08:49:36.000	OUT
    20	2018-06-12 13:02:37.000	IN
    20	2018-06-12 13:02:37.000	OUT
    20	2018-06-12 13:34:02.000	IN
    20	2018-06-12 13:34:02.000	OUT
    20	2018-06-12 17:11:29.000	IN
    20	2018-06-12 17:11:29.000	OUT
    21	2018-06-12 08:50:36.000	IN
    21	2018-06-12 13:03:37.000	OUT
    21	2018-06-12 13:35:02.000	IN
    21	2018-06-12 17:12:29.000	OUT
    21	2018-06-12 17:13:29.000	IN
    */

    Again I want to reiterate fact that this will not work correctly under below circumstances

    1. When you've have gaps in swipes i.e for any day when you dont have even number of entries

    2. Single shift crossing over a day i.e someone starting from evening to work till next morning etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 polachan 2018년 6월 15일 금요일 오전 9:18
    2018년 6월 14일 목요일 오전 6:38