January 22, 2013 at 8:49 am
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
January 22, 2013 at 9:43 am
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/
January 22, 2013 at 11:49 pm
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.
January 23, 2013 at 7:24 am
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.
January 23, 2013 at 11:36 pm
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. 🙂
January 24, 2013 at 6:41 pm
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 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
January 25, 2013 at 2:17 am
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!!! 🙂
January 25, 2013 at 3:56 am
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 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
February 11, 2013 at 3:55 am
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.
February 11, 2013 at 4:37 am
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 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
February 11, 2013 at 5:16 am
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.
February 11, 2013 at 5:23 am
MAX(day_value)-DATEDIFF(minute,MIN(punchTime), MAX(punchTime))/60.
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
February 11, 2013 at 6:14 am
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
February 11, 2013 at 7:01 am
No idea because I don't know what Day 01, Day 02, etc. are supposed to represent.
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
February 11, 2013 at 7:14 am
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