Displaying Multiple rows clock event Under a different Column view
-
Chris,
Did you apply UTCTOLOCAL converter ?
Our table creation_date is UTC datetime format which i am converting to local datetime format, to over come this datetime issue i did applied following
scalar valued function ,
USE [test]
GO
/****** Object: UserDefinedFunction [dbo].[UtcToLocal] Script Date: 10/19/2016 12:33:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UtcToLocal]
(
@p_utcDatetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END
can you try with this and see the result.
-
using your UtcToLocal function and the first query I posted yesterday, I get these results (note I'm in U.S. Eastern Daylight Time or -240 minutes)
event_date person_num full_name CLOCK_IN1 CLOCK_OUT1 CLOCK_IN2 CLOCK_OUT2 CLOCK_IN3 CLOCK_OUT3 CLOCK_IN4 CLOCK_OUT4 HoursWorked
---------- ----------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
2016-07-21 40014 OSU 14:00:46 15:00:46 16:00:46 17:00:46 17:10:46 17:30:46 NULL NULL 2.333333
2016-07-26 10079 JOHN 23:57:46 23:57:46 23:57:46 00:41:46 10:08:46 11:06:46 NULL NULL 1.700000
2016-07-27 10079 JOHN 23:56:46 00:48:46 10:35:46 11:24:46 NULL NULL NULL NULL 1.683333
2016-07-28 10079 JOHN 23:55:46 00:51:46 09:57:46 10:37:46 NULL NULL NULL NULL 1.600000
2016-07-30 107 KATH 19:51:46 04:14:46 NULL NULL NULL NULL NULL NULL 8.383333
2016-07-31 107 KATH 19:31:46 04:04:46 NULL NULL NULL NULL NULL NULL 8.550000
2016-08-01 107 KATH 19:31:46 04:00:46 NULL NULL NULL NULL NULL NULL 8.483333
2016-08-02 107 KATH 19:53:46 04:07:46 NULL NULL NULL NULL NULL NULL 8.233333
2016-08-03 107 KATH 19:54:46 04:05:46 NULL NULL NULL NULL NULL NULL 8.183333
2016-08-04 107 KATH 19:45:46 06:03:46 NULL NULL NULL NULL NULL NULL 10.300000
2016-08-05 107 KATH 15:51:46 00:11:46 NULL NULL NULL NULL NULL NULL 8.333333
I notice a minor bug in your function, the DATEDIFF should probably be computing the difference between GETUTCDATE() and GETDATE(), then DATEADD the passed in @p_utcDatetime to it. This still doesn't explain the wrong results you're showing from the data you're showing. Please show me your results of the following query:
SELECT creation_date, dbo.UtcToLocal(creation_date) AS creation_date_local,CAST(DATEADD(hour,1,dbo.UtcToLocal(creation_date)) AS date) AS event_date, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY person_num, full_name, event_name ORDER BY creation_date) AS event_sort
FROM TEST
WHERE person_num = '00010079' AND creation_date BETWEEN '2016-07-26' AND '2016-07-29'
-
chris find follow,
2016-07-26 03:57:34.0002016-07-26 06:57:47.6902016-07-2600010079 EVENSCLOCK_IN1
2016-07-26 03:57:41.0002016-07-26 06:57:47.6902016-07-2600010079EVENSCLOCK_IN2
2016-07-26 14:08:43.0002016-07-26 17:08:47.6902016-07-2600010079EVENSCLOCK_IN3
2016-07-27 03:56:55.0002016-07-27 06:56:47.6902016-07-2700010079EVENSCLOCK_IN4
2016-07-27 14:35:40.0002016-07-27 17:35:47.6902016-07-2700010079EVENSCLOCK_IN5
2016-07-28 03:55:11.0002016-07-28 06:55:47.6902016-07-2800010079EVENSCLOCK_IN6
2016-07-28 13:57:37.0002016-07-28 16:57:47.6902016-07-2800010079EVENSCLOCK_IN7
2016-07-26 03:57:38.0002016-07-26 06:57:47.6902016-07-2600010079EVENSCLOCK_OUT1
2016-07-26 04:41:09.0002016-07-26 07:41:47.6902016-07-2600010079EVENSCLOCK_OUT2
2016-07-26 15:06:48.0002016-07-26 18:06:47.6902016-07-2600010079EVENSCLOCK_OUT3
2016-07-27 04:48:19.0002016-07-27 07:48:47.6902016-07-2700010079EVENSCLOCK_OUT4
2016-07-27 15:24:30.0002016-07-27 18:24:47.6902016-07-2700010079EVENSCLOCK_OUT5
2016-07-28 04:51:54.0002016-07-28 07:51:47.6902016-07-2800010079EVENSCLOCK_OUT6
2016-07-28 14:37:19.0002016-07-28 17:37:47.6902016-07-2800010079EVENSCLOCK_OUT7
-
OK, for this day the data looks right, there must be previous days where there are more CLOCK_IN events than CLOCK_OUT events for person_num 00010079, because the cross tab pivoted results you show have the CLOCK_OUT events shifted by five events, that's why on 7-26 CLOCK_OUT1 is really the time for 7-28 CLOCK_OUT1.
The only way I can think of offhand to try to handle this is in the Prep query, to calculate the EVENT_DATE add 1 hour to CHECK_IN and subtract 1 hour from CHECK_OUT, and try to use that in the Paired query, so something like this:
WITH Prep AS
(SELECT dbo.UtcToLocal(creation_date) AS creation_date,
CAST(DATEADD(hour, CASE WHEN event_name = 'CLOCK_IN' THEN 1 ELSE -1 END, dbo.UtcToLocal(creation_date)) AS date) AS event_date, person_num, full_name, event_name
FROM TEST),
EventSort AS
(SELECT creation_date, event_date, person_num, full_name, event_name,
ROW_NUMBER() OVER (PARTITION BY person_num, full_name, event_date, event_name ORDER BY creation_date) AS daypart
FROM Prep),
Paired AS
(SELECT ci.event_date, ci.daypart, ci.person_num, ci.full_name, ci.creation_date AS in_time, co.creation_date AS out_time, DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROM EventSort ci
INNER JOIN EventSort co ON ci.person_num = co.person_num AND ci.full_name = co.full_name AND ci.event_date = co.event_date AND ci.daypart = co.daypart
WHERE ci.event_name = 'CLOCK_IN' AND co.event_name = 'CLOCK_OUT')
SELECT event_date, person_num, full_name,
CAST(MAX(CASE WHEN daypart = 1 THEN in_time END) AS time) AS CLOCK_IN1,
CAST(MAX(CASE WHEN daypart = 1 THEN out_time END) AS time) AS CLOCK_OUT1,
CAST(MAX(CASE WHEN daypart = 2 THEN in_time END) AS time) AS CLOCK_IN2,
CAST(MAX(CASE WHEN daypart = 2 THEN out_time END) AS time) AS CLOCK_OUT2,
CAST(MAX(CASE WHEN daypart = 3 THEN in_time END) AS time) AS CLOCK_IN3,
CAST(MAX(CASE WHEN daypart = 3 THEN out_time END) AS time) AS CLOCK_OUT3,
CAST(MAX(CASE WHEN daypart = 4 THEN in_time END) AS time) AS CLOCK_IN4,
CAST(MAX(CASE WHEN daypart = 4 THEN out_time END) AS time) AS CLOCK_OUT4,
SUM(minutes) / 60.0 AS HoursWorked
FROM Paired
GROUP BY event_date, person_num, full_name
-
Hello Chris,
We are reached almost near to our result,
But i can see two issues which is occurring when shift schedule changing time,
my query example for one person_num 20345,
action_idCreation_Date event_name
1684882016-07-21 15:51:48.697CLOCK_IN
1688252016-07-22 00:06:48.697CLOCK_OUT
1688702016-07-22 10:07:48.697CLOCK_IN
1689142016-07-22 20:12:48.697CLOCK_OUT
1689452016-07-23 05:49:48.697CLOCK_IN
1692032016-07-23 16:07:48.697CLOCK_OUT
1695862016-07-24 07:52:48.697CLOCK_IN
1698102016-07-24 16:09:48.697CLOCK_OUT
1753432016-07-25 07:50:48.700CLOCK_IN
1755992016-07-25 16:08:48.700CLOCK_OUT
1760602016-07-26 07:54:48.700CLOCK_IN
1762202016-07-26 16:03:48.700CLOCK_OUT
1765562016-07-27 07:50:48.700CLOCK_IN
1767802016-07-27 16:07:48.700CLOCK_OUT
1770352016-07-28 05:54:48.700CLOCK_IN
1773402016-07-28 16:11:48.700CLOCK_OUT
1773582016-07-28 16:16:48.700CLOCK_OUT
1794702016-07-30 23:50:48.700CLOCK_IN
1797622016-07-31 08:02:48.700CLOCK_OUT
1802722016-07-31 23:55:48.700CLOCK_IN
1805982016-08-01 08:13:48.700CLOCK_OUT
1811532016-08-02 00:19:48.700CLOCK_IN
1814272016-08-02 08:04:48.700CLOCK_OUT
1819692016-08-02 23:56:48.700CLOCK_IN
1821682016-08-03 08:00:48.700CLOCK_OUT
1828072016-08-03 23:57:48.700CLOCK_IN
1830962016-08-04 08:03:48.700CLOCK_OUT
1836112016-08-04 23:55:48.700CLOCK_IN
1836792016-08-05 10:05:48.700CLOCK_OUT
1837202016-08-05 19:50:48.700CLOCK_IN
1838092016-08-06 06:10:48.700CLOCK_OUT
1841582016-08-06 15:53:48.700CLOCK_IN
1844692016-08-07 00:06:48.700CLOCK_OUT
1849192016-08-07 15:53:48.700CLOCK_IN
1852782016-08-08 00:06:48.700CLOCK_OUT
1857322016-08-08 15:54:48.700CLOCK_IN
1861032016-08-09 00:05:48.700CLOCK_OUT
1874812016-08-10 15:55:48.703CLOCK_IN
1878452016-08-11 00:06:48.703CLOCK_OUT
1883402016-08-11 15:51:48.703CLOCK_IN
1885372016-08-12 00:04:48.703CLOCK_OUT
1885752016-08-12 09:56:48.703CLOCK_IN
1886162016-08-12 20:03:48.703CLOCK_OUT
1886452016-08-13 06:00:48.703CLOCK_IN
1888972016-08-13 16:07:48.703CLOCK_OUT
1892132016-08-14 07:51:48.703CLOCK_IN
1894152016-08-14 16:13:48.703CLOCK_OUT
1895432016-08-14 18:45:48.703CLOCK_IN
1895792016-08-14 20:29:48.703CLOCK_OUT
1897582016-08-15 07:53:48.703CLOCK_IN
1899952016-08-15 17:08:48.703CLOCK_OUT
1902952016-08-16 07:48:48.703CLOCK_IN
1904922016-08-16 16:15:48.703CLOCK_OUT
1908172016-08-17 07:47:48.703CLOCK_IN
1924762016-08-17 16:07:48.703CLOCK_OUT
1930552016-08-18 07:50:48.703CLOCK_IN
1933482016-08-18 16:11:48.703CLOCK_OUT
1945152016-08-20 23:55:48.703CLOCK_IN
1948682016-08-21 08:12:48.707CLOCK_OUT
1953232016-08-21 23:59:48.707CLOCK_IN
1955402016-08-22 08:18:48.707CLOCK_OUT
1962102016-08-22 23:57:48.707CLOCK_IN
1965552016-08-23 08:10:48.707CLOCK_OUT
1970482016-08-23 23:53:48.707CLOCK_IN
1974082016-08-24 08:10:48.707CLOCK_OUT
1979742016-08-24 23:57:48.707CLOCK_IN
1982772016-08-25 08:02:48.707CLOCK_OUT
1987772016-08-25 23:55:48.707CLOCK_IN
1988602016-08-26 10:10:48.707CLOCK_OUT
1989192016-08-26 19:52:48.707CLOCK_IN
1990262016-08-27 06:11:48.707CLOCK_OUT
1994612016-08-27 15:55:48.707CLOCK_IN
1997082016-08-28 00:07:48.707CLOCK_OUT
2002532016-08-28 15:54:48.707CLOCK_IN
2006092016-08-29 00:01:48.707CLOCK_OUT
2011042016-08-29 15:53:48.707CLOCK_IN
2014392016-08-30 00:03:48.707CLOCK_OUT
2019022016-08-30 15:50:48.710CLOCK_IN
2022572016-08-31 00:03:48.710CLOCK_OUT
2027472016-08-31 15:53:48.710CLOCK_IN
2030642016-09-01 00:03:48.710CLOCK_OUT
2035522016-09-01 15:57:48.710CLOCK_IN
2039252016-09-02 00:07:48.710CLOCK_OUT
2040222016-09-02 19:58:48.710CLOCK_IN
2041002016-09-03 06:09:48.710CLOCK_OUT
2045002016-09-03 15:57:48.710CLOCK_IN
2047392016-09-03 22:08:48.710CLOCK_OUT
2052272016-09-04 08:34:48.710CLOCK_IN
2054392016-09-04 16:07:48.710CLOCK_OUT
2060382016-09-05 07:56:48.710CLOCK_IN
2062522016-09-05 13:47:48.710CLOCK_OUT
As per your query, When shift changing one day clock is missing with time negative number with 4 hours less showing in the total calculation.
same above person result with your query.
Creation_Date DATE_IN1 DATE_OUT1 CLOCK_IN1 CLOCK_OUT1
2016-07-212016-07-212016-07-2215:51:30.660000000:06:30.6500000NULLNULLNULLNULLNULLNULL8.250000
2016-07-222016-07-222016-07-2210:07:30.660000020:12:30.6500000NULLNULLNULLNULLNULLNULL10.083333
2016-07-232016-07-232016-07-2305:49:30.660000016:07:30.6500000NULLNULLNULLNULLNULLNULL10.300000
2016-07-242016-07-242016-07-2407:52:30.660000016:09:30.6500000NULLNULLNULLNULLNULLNULL8.283333
2016-07-252016-07-252016-07-2507:50:30.660000016:08:30.6500000NULLNULLNULLNULLNULLNULL8.300000
2016-07-262016-07-262016-07-2607:54:30.660000016:03:30.6500000NULLNULLNULLNULLNULLNULL8.150000
2016-07-272016-07-272016-07-2707:50:30.660000016:07:30.6500000NULLNULLNULLNULLNULLNULL8.283333
2016-07-282016-07-282016-07-2805:54:30.660000016:11:30.6500000NULLNULLNULLNULLNULLNULL10.283333
2016-07-312016-07-302016-07-3123:50:30.660000008:02:30.6500000NULLNULLNULLNULLNULLNULL8.200000
2016-08-012016-07-312016-08-0123:55:30.660000008:13:30.6500000NULLNULLNULLNULLNULLNULL8.300000
2016-08-022016-08-022016-08-0200:19:30.660000008:04:30.6530000NULLNULLNULLNULLNULLNULL7.750000
2016-08-032016-08-022016-08-0323:56:30.660000008:00:30.6530000NULLNULLNULLNULLNULLNULL8.066666
2016-08-042016-08-032016-08-0423:57:30.663000008:03:30.6530000NULLNULLNULLNULLNULLNULL8.100000
2016-08-052016-08-042016-08-0523:55:30.663000010:05:30.6530000NULLNULLNULLNULLNULLNULL10.166666
2016-08-062016-08-062016-08-0615:53:30.663000006:10:30.6530000NULLNULLNULLNULLNULLNULL-9.716666
2016-08-072016-08-072016-08-0815:53:30.663000000:06:30.6530000NULLNULLNULLNULLNULLNULL8.216666
2016-08-082016-08-082016-08-0915:54:30.663000000:05:30.6530000NULLNULLNULLNULLNULLNULL8.183333
2016-08-102016-08-102016-08-1115:55:30.663000000:06:30.6530000NULLNULLNULLNULLNULLNULL8.183333
2016-08-112016-08-112016-08-1215:51:30.663000000:04:30.6530000NULLNULLNULLNULLNULLNULL8.216666
2016-08-122016-08-122016-08-1209:56:30.663000020:03:30.6530000NULLNULLNULLNULLNULLNULL10.116666
2016-08-132016-08-132016-08-1306:00:30.663000016:07:30.6530000NULLNULLNULLNULLNULLNULL10.116666
2016-08-142016-08-142016-08-1407:51:30.663000016:13:30.653000018:45: 20:29 NULLNULLNULLNULL10.100000
2016-08-152016-08-152016-08-1507:53:30.663000017:08:30.6530000NULLNULLNULLNULLNULLNULL9.250000
2016-08-162016-08-162016-08-1607:48:30.663000016:15:30.6530000NULLNULLNULLNULLNULLNULL8.450000
2016-08-172016-08-172016-08-1707:47:30.663000016:07:30.6530000NULLNULLNULLNULLNULLNULL8.333333
2016-08-182016-08-182016-08-1807:50:30.663000016:11:30.6530000NULLNULLNULLNULLNULLNULL8.350000
2016-08-212016-08-202016-08-2123:55:30.663000008:12:30.6570000NULLNULLNULLNULLNULLNULL8.283333
2016-08-222016-08-212016-08-2223:59:30.663000008:18:30.6570000NULLNULLNULLNULLNULLNULL8.316666
2016-08-232016-08-222016-08-2323:57:30.667000008:10:30.6570000NULLNULLNULLNULLNULLNULL8.216666
2016-08-242016-08-232016-08-2423:53:30.667000008:10:30.6570000NULLNULLNULLNULLNULLNULL8.283333
2016-08-252016-08-242016-08-2523:57:30.667000008:02:30.6570000NULLNULLNULLNULLNULLNULL8.083333
2016-08-262016-08-252016-08-2623:55:30.667000010:10:30.6570000NULLNULLNULLNULLNULLNULL10.250000
2016-08-272016-08-272016-08-2715:55:30.667000006:11:30.6570000NULLNULLNULLNULLNULLNULL-9.733333
2016-08-282016-08-282016-08-2915:54:30.667000000:01:30.6570000NULLNULLNULLNULLNULLNULL8.116666
2016-08-292016-08-292016-08-3015:53:30.667000000:03:30.6570000NULLNULLNULLNULLNULLNULL8.166666
2016-08-302016-08-302016-08-3115:50:30.667000000:03:30.6570000NULLNULLNULLNULLNULLNULL8.216666
2016-08-312016-08-312016-09-0115:53:30.667000000:03:30.6570000NULLNULLNULLNULLNULLNULL8.166666
2016-09-012016-09-012016-09-0215:57:30.667000000:07:30.6570000NULLNULLNULLNULLNULLNULL8.166666
2016-09-032016-09-032016-09-0315:57:30.667000006:09:30.6570000NULLNULLNULLNULLNULLNULL-9.800000
2016-09-042016-09-042016-09-0408:34:30.667000016:07:30.6570000NULLNULLNULLNULLNULLNULL7.550000
2016-09-052016-09-052016-09-0507:56:30.667000013:47:30.6600000NULLNULLNULLNULLNULLNULL5.850000
What is your suggestion..
-
OK, this is the best I could come up with, but this won't handle very well the case where there is a missing CLOCK_OUT, I'm brute force picking the first CLOCK_OUT after each CLOCK_IN, even if it's not in the same "event_date":
WITH Prep AS
(SELECT ci.person_num, ci.full_name, ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,
CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROM TEST ci
OUTER APPLY (SELECT TOP 1 creation_date FROM TEST t WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT' ORDER BY t.creation_date) co
WHERE ci.event_name = 'CLOCK_IN'
AND ci.person_num = 20345),
CalcPart AS
(SELECT person_num, full_name, event_date, in_time, out_time, minutes,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep)
SELECT event_date, person_num, --full_name,
MAX(CASE WHEN daypart = 1 THEN in_time END) AS CLOCK_IN1,
MAX(CASE WHEN daypart = 1 THEN out_time END) AS CLOCK_OUT1,
MAX(CASE WHEN daypart = 2 THEN in_time END) AS CLOCK_IN2,
MAX(CASE WHEN daypart = 2 THEN out_time END) AS CLOCK_OUT2,
MAX(CASE WHEN daypart = 3 THEN in_time END) AS CLOCK_IN3,
MAX(CASE WHEN daypart = 3 THEN out_time END) AS CLOCK_OUT3,
MAX(CASE WHEN daypart = 4 THEN in_time END) AS CLOCK_IN4,
MAX(CASE WHEN daypart = 4 THEN out_time END) AS CLOCK_OUT4,
SUM(minutes) / 60.0 AS HoursWorked
FROM CalcPart
GROUP BY event_date, person_num, full_name
-
hello chris i must admit this is also one of the best,
i am trying to show this date as well ,but i am getting error since this already CASTed.
(MAX(CASE WHEN daypart = 1 THEN in_time END) AS date) AS DATE_IN1,
(MAX(CASE WHEN daypart = 1 THEN out_time END) AS date) AS DATE_OUT1,
can you help me with DATE as well.
would be great to chk if any issues.
-
if you want the date in the results for the CLOCK_IN and CLOCK_OUT columns then maybe you don't want to cast them to TIME datatype like I did in the Prep query. Otherwise, you'd have to add columns like this:
WITH Prep AS
(SELECT ci.person_num, ci.full_name, ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,
CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,
CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROM TEST ci
OUTER APPLY (SELECT TOP 1 creation_date FROM TEST t WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT' ORDER BY t.creation_date) co
WHERE ci.event_name = 'CLOCK_IN'
AND ci.person_num = 20345),
and include the new in_date, out_date columns in the CalcPart query and the final results query.
-
Hello Chris,
I am having challenges with "Date" have got two SQL server (SQL 2005 & 2016).
Your code is rocking with SQL 2016 but in 2005 it throw error and its work with datetime instead of (DATE , TIME) exp ; below
CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) As datetime) AS event_date,
CAST(dbo.UtcToLocal(ci.creation_date) AS datetime) AS in_time, CAST(dbo.UtcToLocal(co.creation_date) AS datetime) AS out_time,
but above does not give exact your output. how to overcome this ? in sql 2005
-
The DATE and TIME datatypes weren't introduced until SQL Server 2008, if you want the same query to work in 2005, you will have to stick with the combined DATETIME datatype.
Note that Microsoft stopped supporting SQL Server 2005 earlier this year. If you can you may want to plan an upgrade depending on other software requirements:
https://www.microsoft.com/en-us/cloud-platform/sql-server-2005
-
Hello Chris,
Thanks for your feedback.
With your query i am trying to bring two more column. One from database which is dutyhours and another one is OT which computed from (Dutyhours-hoursworked). and trying for total sum for OT in footer . and challenge here is total sum should exclude the negative value.
can you help with your code ?
-
philand3 (10/25/2016)
Hello Chris,Thanks for your feedback.
With your query i am trying to bring two more column. One from database which is dutyhours and another one is OT which computed from (Dutyhours-hoursworked). and trying for total sum for OT in footer . and challenge here is total sum should exclude the negative value.
can you help with your code ?
how are you currently computing the total SUM row? Is it in the query itself or something in a reporting tool?
-
I am using ASP.NET . but i am unable bring total sum .
BR.
-
would it make sense to calculate the overtime in a CASE statement eliminating the negatives before they get to your sum then? Something like:
CASE WHEN Hoursworked > Dutyhours THEN Hoursworked-Dutyhours ELSE 0 END -
Hello Chris, Thanks i did coding in ASP.NET itself.
By the way i am trying to bring query with the last clock_in where no clock_out for the same details ascending order by preferred_name,
how to achieve with your codings ?
this query should give current date clock details.
expected result.
BR
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply