Clock-In/ Clock-Out

  • I am working on a clock in and clock out system. I have one table with Employee id, punchTime, punchType.

    test data:

    ClockIDEmployeeIDpunchTimepunchType

    87692013-01-22 14:26:35.5901

    97692013-01-22 14:46:59.3630

    105092013-01-22 14:49:17.2601

    115092013-01-22 15:13:10.7700

    where punchtype '0' for out and '1' for In.

    desired output:

    EmployeeID ClockIn ClockOut [Hours Worked]

    7692013-01-22 14:26:352013-01-22 14:46:590.20

    Please can anyone help me how can i acheive this output.

    Thanks

    Ram

  • p.ramchander (1/22/2013)


    I am working on a clock in and clock out system. I have one table with Employee id, punchTime, punchType.

    test data:

    ClockIDEmployeeIDpunchTimepunchType

    87692013-01-22 14:26:35.5901

    97692013-01-22 14:46:59.3630

    105092013-01-22 14:49:17.2601

    115092013-01-22 15:13:10.7700

    where punchtype '0' for out and '1' for In.

    desired output:

    EmployeeID ClockIn ClockOut [Hours Worked]

    7692013-01-22 14:26:352013-01-22 14:46:590.20

    Please can anyone help me how can i acheive this output.

    Thanks

    Ram

    You have been around these forums long enough to know that we need ddl and sample data. Please read the first link in my signature for best practices when posting questions.

    If you want to do this without posting details, you will most likely need a cross tab type of query. You can read about those by following the links in my signature.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I think this is what you are looking for :

    Declare @table Table(ClockID Int, EmployeeID int, punchTime DateTime, punchType Bit)

    Insert into @table

    Select 8,769,'2013-01-22 14:26:35.590',1

    Union ALL

    Select 9,769,'2013-01-22 14:46:59.363',0

    Union ALL

    Select 10,509,'2013-01-22 14:49:17.260',1

    Union ALL

    Select 11,509,'2013-01-22 15:13:10.770',0

    Select *, DATEDIFF(MINUTE,Cast(ClockIn As DateTime),CAST(ClockOut As DateTime))/Cast(60 As Float) As HoursWorked From

    (

    Select EmployeeID,

    MAX(Case When punchType = 1 then Cast(punchTime As Varchar) Else '' End) As ClockIn,

    MAX(Case When punchType = 0 then Cast(punchTime As Varchar) Else '' End) As ClockOut

    From @table

    Group By EmployeeID

    ) As A

    From next time post DDL and sample data to support your requirement. If you don't know how...then please checkout the link in my signature. How hard is it?...and how much time does it take. It'll get you instant answers. Please help us help you.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (1/22/2013)


    I think this is what you are looking for :

    Declare @table Table(ClockID Int, EmployeeID int, punchTime DateTime, punchType Bit)

    Insert into @table

    Select 8,769,'2013-01-22 14:26:35.590',1

    Union ALL

    Select 9,769,'2013-01-22 14:46:59.363',0

    Union ALL

    Select 10,509,'2013-01-22 14:49:17.260',1

    Union ALL

    Select 11,509,'2013-01-22 15:13:10.770',0

    Select *, DATEDIFF(MINUTE,Cast(ClockIn As DateTime),CAST(ClockOut As DateTime))/Cast(60 As Float) As HoursWorked From

    (

    Select EmployeeID,

    MAX(Case When punchType = 1 then Cast(punchTime As Varchar) Else '' End) As ClockIn,

    MAX(Case When punchType = 0 then Cast(punchTime As Varchar) Else '' End) As ClockOut

    From @table

    Group By EmployeeID

    ) As A

    From next time post DDL and sample data to support your requirement. If you don't know how...then please checkout the link in my signature. How hard is it?...and how much time does it take. It'll get you instant answers. Please help us help you.

    Hi sean/vinu,

    Thanks for your reply and also pointing me in the right direction by providing the links 🙂

    @vinu

    I tried your solution, but the output is not the same i was expecting.

    For example as you can see in the code below, i added some more clock in & clock out information for employeeID '509'

    Declare @table Table(ClockID Int, EmployeeID int, punchTime DateTime, punchType Bit)

    Insert into @table

    Select 8,769,'2013-01-22 14:26:35.590',1

    Union ALL

    Select 9,769,'2013-01-22 14:46:59.363',0

    Union ALL

    Select 10,509,'2013-01-22 14:49:17.260',1

    Union ALL

    Select 11,509,'2013-01-22 15:13:10.770',0

    Union ALL

    Select 12, 509, '2013-01-23 12:00',1

    Union ALL

    Select 13,509,'2013-01-23 15:00',0

    Select *, DATEDIFF(MINUTE,Cast(ClockIn As DateTime),CAST(ClockOut As DateTime))/Cast(60 As Float) As HoursWorked From

    (

    Select EmployeeID,

    MAX(Case When punchType = 1 then Cast(punchTime As Varchar) Else '' End) As ClockIn,

    MAX(Case When punchType = 0 then Cast(punchTime As Varchar) Else '' End) As ClockOut

    From @table

    Group By EmployeeID

    ) As A

    output:

    EmployeeIDClockInClockOutHoursWorked

    509Jan 23 2013 12:00PMJan 23 2013 3:00PM3

    769Jan 22 2013 2:26PMJan 22 2013 2:46PM0.333333333333333

    It is not displaying the Clock In, Clock Out data for Jan 22 2013.

    Here is the solution worked for me.

    CREATE TABLE [dbo].[tbl_clock](

    [ClockID] [int] IDENTITY(1,1) NOT NULL,

    [EmployeeID] [int] NULL,

    [punchTime] [datetime] NULL,

    [punchType] [bit] NULL,

    CONSTRAINT [PK_tbl_clock] PRIMARY KEY CLUSTERED

    (

    [ClockID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    INSERT INTO [Time_Attendance_System_TestDB].[dbo].[tbl_clock] ([EmployeeID] ,[punchTime] ,[punchType])

    Select 769,'2013-01-22 14:26:35.590',1

    Union ALL

    Select 769,'2013-01-22 14:46:59.363',0

    Union ALL

    Select 509,'2013-01-22 14:49:17.260',1

    Union ALL

    Select 509,'2013-01-22 15:13:10.770',0

    Union ALl

    Select 509, '2013-01-23 12:00',1

    Union ALL

    Select 509,'2013-01-23 15:00',0

    select i.EmployeeID, i.punchTime as [clock in], o.punchTime as [clock out], CONVERT(decimal, DATEDIFF(MINUTE,i.punchTime,o.punchTime))/60 as [hours worked]

    from tbl_clock i INNER JOIN tbl_clock o ON i.EmployeeID = o.EmployeeID

    where (o.punchType = 0) AND (i.punchType = 1) AND (o.punchTime =

    (SELECT MIN(punchTime)

    from tbl_clock

    where punchTime>i.punchTime and punchType=0))

    once again, Thanks a lot for your help. much appreciated.

  • You're welcome Ram.

    But just to keep you thinking.....how is your on performance??

    The following is the edited version of my query which gets you the results :

    Declare @table Table(ClockID Int, EmployeeID int, punchTime DateTime, punchType Bit)

    Insert into @table

    Select 8,769,'2013-01-22 14:26:35.590',1

    Union ALL

    Select 9,769,'2013-01-22 14:46:59.363',0

    Union ALL

    Select 10,509,'2013-01-22 14:49:17.260',1

    Union ALL

    Select 11,509,'2013-01-22 15:13:10.770',0

    Union ALL

    Select 12, 509, '2013-01-23 12:00',1

    Union ALL

    Select 13,509,'2013-01-23 15:00',0

    Select *, Convert(Decimal,DATEDIFF(MINUTE,Cast(ClockIn As DateTime),CAST(ClockOut As DateTime)))/60 As HoursWorked From

    (

    Select EmployeeID,

    MAX(Case When punchType = 1 then Cast(punchTime As Varchar) Else '' End) As ClockIn,

    MAX(Case When punchType = 0 then Cast(punchTime As Varchar) Else '' End) As ClockOut

    From

    (

    Select EmployeeID,punchType, Cast(CONVERT(VARCHAR(10),punchTime,111) As DATE) As punchDate, punchTime

    From @table

    ) As p

    Group By EmployeeID, punchDate

    ) As A

    To see how both the queries work I did a performance test. Here are the results of both our queries :

    --Vinu's Query Results

    Table '#5070F446'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (6 row(s) affected)

    (3 row(s) affected)

    Table '#5070F446'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    --Ram's Query Results

    Table '#4E88ABD4'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (6 row(s) affected)

    (3 row(s) affected)

    Table '#4E88ABD4'. Scan count 7, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    Checkout the difference in the Scan Count and the Logical Reads. This is for the same sample data you provided. So, the difference in time is negligible....but when you try the queries with more data then you will see the difference.

    I am just learning and it was good to get a solution from you too. Did the tests to increase my own knowledge...thought I'd share it with you....it might be helpful for you as well. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (1/23/2013)


    You're welcome Ram.

    But just to keep you thinking.....how is your on performance??

    The following is the edited version of my query which gets you the results :

    Declare @table Table(ClockID Int, EmployeeID int, punchTime DateTime, punchType Bit)

    Insert into @table

    Select 8,769,'2013-01-22 14:26:35.590',1

    Union ALL

    Select 9,769,'2013-01-22 14:46:59.363',0

    Union ALL

    Select 10,509,'2013-01-22 14:49:17.260',1

    Union ALL

    Select 11,509,'2013-01-22 15:13:10.770',0

    Union ALL

    Select 12, 509, '2013-01-23 12:00',1

    Union ALL

    Select 13,509,'2013-01-23 15:00',0

    Select *, Convert(Decimal,DATEDIFF(MINUTE,Cast(ClockIn As DateTime),CAST(ClockOut As DateTime)))/60 As HoursWorked From

    (

    Select EmployeeID,

    MAX(Case When punchType = 1 then Cast(punchTime As Varchar) Else '' End) As ClockIn,

    MAX(Case When punchType = 0 then Cast(punchTime As Varchar) Else '' End) As ClockOut

    From

    (

    Select EmployeeID,punchType, Cast(CONVERT(VARCHAR(10),punchTime,111) As DATE) As punchDate, punchTime

    From @table

    ) As p

    Group By EmployeeID, punchDate

    ) As A

    To see how both the queries work I did a performance test. Here are the results of both our queries :

    --Vinu's Query Results

    Table '#5070F446'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (6 row(s) affected)

    (3 row(s) affected)

    Table '#5070F446'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    --Ram's Query Results

    Table '#4E88ABD4'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (6 row(s) affected)

    (3 row(s) affected)

    Table '#4E88ABD4'. Scan count 7, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    Checkout the difference in the Scan Count and the Logical Reads. This is for the same sample data you provided. So, the difference in time is negligible....but when you try the queries with more data then you will see the difference.

    I am just learning and it was good to get a solution from you too. Did the tests to increase my own knowledge...thought I'd share it with you....it might be helpful for you as well. 🙂

    I think this approach using Vinu's set up data might be a little simpler to comprehend:

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM @table)

    SELECT EmployeeID

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    FROM Punches

    GROUP BY EmployeeID, rn

    I leave it to you guys to sort out which is faster but I suggest you not read too much into the logical/physical reads stuff and use Jeff Moden's advice to set up 1M rows of sample data and test with that. Times are what rule, not reads.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I tried the query on random sample data consisting of 1164000 rows.

    Dwain, your query is the clear winner. Here are the results for all the three queries :

    Dwain's Query - 60889 ms

    Vinu's Query - 87000 ms

    Ram' Query - 293812 ms(4 mins. 57 secs.)

    Nice one Dwain. Logic was very nice and you made it really simple....that was the catch!!! 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (1/25/2013)


    I tried the query on random sample data consisting of 1164000 rows.

    Dwain, your query is the clear winner. Here are the results for all the three queries :

    Dwain's Query - 60889 ms

    Vinu's Query - 87000 ms

    Ram' Query - 293812 ms(4 mins. 57 secs.)

    Nice one Dwain. Logic was very nice and you made it really simple....that was the catch!!! 🙂

    KISS principle.

    Simplest usually the best.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I agree with you vinu. Dwain's logic is very nice and simple. Thanks Dwain for your time and help. I replaced the logic with your's.

    Now, I created a table named TAT_Shiftpaterns to store employee shift patterns.

    CREATE TABLE [dbo].[TAT_Shiftpatterns]

    (

    Employee_number varchar(30)

    ,Full_name, varchar(240),

    ,day_name, varchar(80),

    ,day_value, varchar(80),

    ,work_pattern, varchar(80),

    ,Start_Day, varchar(30),

    )

    INSERT INTO [Time_Attendance_TestDB].[dbo].[TAT_Shiftpatterns]

    ([Employee_number]

    ,[Full_name]

    ,[day_name]

    ,[day_value]

    ,[work_pattern]

    ,[Start_Day])

    SELECT '769','Baxter, Mrs. Natasha','Day 01','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 02','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 03','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 04','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 05','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 06','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 07','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    i modified dwain's code to display day_start,day_name

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM @table)

    SELECT EmployeeID

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,date_start=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,day_name=DATEPART(dw,MIN(punchTime))

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    FROM Punches

    GROUP BY EmployeeID, rn

    I want to display fullname and work_pattern along with ClockIN and CLockOUT. i tried using JOINS, but no success.

    Please can anyone point me in right direction or help me with a code.

  • p.ramchander (2/11/2013)


    I agree with you vinu. Dwain's logic is very nice and simple. Thanks Dwain for your time and help. I replaced the logic with your's.

    Now, I created a table named TAT_Shiftpaterns to store employee shift patterns.

    CREATE TABLE [dbo].[TAT_Shiftpatterns]

    (

    Employee_number varchar(30)

    ,Full_name, varchar(240),

    ,day_name, varchar(80),

    ,day_value, varchar(80),

    ,work_pattern, varchar(80),

    ,Start_Day, varchar(30),

    )

    INSERT INTO [Time_Attendance_TestDB].[dbo].[TAT_Shiftpatterns]

    ([Employee_number]

    ,[Full_name]

    ,[day_name]

    ,[day_value]

    ,[work_pattern]

    ,[Start_Day])

    SELECT '769','Baxter, Mrs. Natasha','Day 01','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 02','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 03','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 04','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 05','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 06','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 07','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    i modified dwain's code to display day_start,day_name

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM @table)

    SELECT EmployeeID

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,date_start=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,day_name=DATEPART(dw,MIN(punchTime))

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    FROM Punches

    GROUP BY EmployeeID, rn

    I want to display fullname and work_pattern along with ClockIN and CLockOUT. i tried using JOINS, but no success.

    Please can anyone point me in right direction or help me with a code.

    Are you saying something like this doesn't work?

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM @table)

    SELECT EmployeeID

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,date_start=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,day_name=DATEPART(dw,MIN(punchTime))

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    ,[Full_name]=MAX([Full_name])

    ,[work_pattern]=MAX([work_pattern])

    FROM Punches a

    INNER JOIN [TAT_Shiftpatterns] b ON b.[Employee_number] = a.EmployeeID

    GROUP BY EmployeeID, rn


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (2/11/2013)


    p.ramchander (2/11/2013)


    I agree with you vinu. Dwain's logic is very nice and simple. Thanks Dwain for your time and help. I replaced the logic with your's.

    Now, I created a table named TAT_Shiftpaterns to store employee shift patterns.

    CREATE TABLE [dbo].[TAT_Shiftpatterns]

    (

    Employee_number varchar(30)

    ,Full_name, varchar(240),

    ,day_name, varchar(80),

    ,day_value, varchar(80),

    ,work_pattern, varchar(80),

    ,Start_Day, varchar(30),

    )

    INSERT INTO [Time_Attendance_TestDB].[dbo].[TAT_Shiftpatterns]

    ([Employee_number]

    ,[Full_name]

    ,[day_name]

    ,[day_value]

    ,[work_pattern]

    ,[Start_Day])

    SELECT '769','Baxter, Mrs. Natasha','Day 01','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 02','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 03','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 04','7.5','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 05','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 06','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    SELECT '769','abc','Day 07','0','WP_P/TIME_MO-WE_22.5HRS','Day 02'

    i modified dwain's code to display day_start,day_name

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM @table)

    SELECT EmployeeID

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,date_start=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,day_name=DATEPART(dw,MIN(punchTime))

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    FROM Punches

    GROUP BY EmployeeID, rn

    I want to display fullname and work_pattern along with ClockIN and CLockOUT. i tried using JOINS, but no success.

    Please can anyone point me in right direction or help me with a code.

    Are you saying something like this doesn't work?

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM @table)

    SELECT EmployeeID

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,date_start=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,day_name=DATEPART(dw,MIN(punchTime))

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    ,[Full_name]=MAX([Full_name])

    ,[work_pattern]=MAX([work_pattern])

    FROM Punches a

    INNER JOIN [TAT_Shiftpatterns] b ON b.[Employee_number] = a.EmployeeID

    GROUP BY EmployeeID, rn

    dwain your query worked. 🙂

    How can i get the hrs difference between day_value and Hrs for a particular day.

    Thanks a lot.

  • MAX(day_value)-DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (2/11/2013)


    MAX(day_value)-DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    dwain, Thanks again.

    how can i compare the day_name between @table and shiftpatterns table to get day_value from shiftpattern table.

    I added an extra line in the code to show the day_name for MIN(punchTime)

    ;WITH Punches AS (

    SELECT *

    ,rn=(ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY punchTime)-1)/2

    FROM tbl_clock)

    SELECT EmployeeID

    ,[Full_name] = MAX([Full_name])

    ,ClockIN=MIN(punchTime)

    ,ClockOUT=MAX(punchTime)

    ,date_start=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,date_end=CONVERT(VARCHAR(10),MIN(punchtime),111)

    ,day_name=DATEPART(dw,MIN(punchTime))

    ,Hrs=DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    ,[day_value]= MIN(day_value)

    ,[work_pattern] = MAX([work_pattern])

    ,[Hrs_diff] = MAX(day_value)-DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.

    FROM Punches a

    INNER JOIN [TAT_Shiftpatterns] b ON b.Employee_number = a.EmployeeID

    GROUP BY EmployeeID, rn

  • No idea because I don't know what Day 01, Day 02, etc. are supposed to represent.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Day 01 represents sunday and Day 02 - monday and so on.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply