I want to calculate amount of came on time or early of employee per specific date. But in the example i put specific USERID as additional criteria.
This is my CHECKINOUT table (Total 39 rows):
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID WorkCode sn UserExtFmt Update
1040 02/03/2020 6:54:50 I 1 3 0 0840060140610 0 02/03/2020 10:13:56
1040 02/03/2020 8:00:00 I 1 2 0 0840060140160 0 02/03/2020 10:50:20
1040 02/03/2020 16:34:37 I 1 5 0 2809731360643 0 26/03/2020 9:51:41
1040 03/03/2020 8:02:41 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 03/03/2020 16:45:00 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 03/03/2020 16:45:03 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 04/03/2020 7:57:46 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 04/03/2020 7:57:48 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 04/03/2020 17:01:53 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 04/03/2020 17:01:56 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 05/03/2020 8:03:45 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 05/03/2020 8:03:48 I 1 2 0 0840060140160 0 26/03/2020 9:50:49
1040 05/03/2020 16:41:02 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 05/03/2020 16:41:05 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 06/03/2020 8:27:13 I 1 2 0 0840060140160 0 26/03/2020 9:50:50
1040 06/03/2020 17:26:03 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 06/03/2020 17:26:06 I 1 5 0 2809731360643 0 26/03/2020 9:51:42
1040 07/03/2020 11:53:57 I 1 2 0 0840060140160 0 26/03/2020 9:50:50
1040 09/03/2020 8:01:51 I 1 2 0 0840060140160 0 27/03/2020 10:29:16
1040 16/03/2020 7:58:20 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 16/03/2020 7:58:22 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 16/03/2020 16:34:07 I 1 5 0 2809731360643 0 26/03/2020 9:51:43
1040 17/03/2020 7:59:05 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 17/03/2020 16:43:50 0 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 18/03/2020 8:00:43 I 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 18/03/2020 8:00:46 I 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 18/03/2020 16:30:23 I 1 2 0 0840060140160 0 26/03/2020 9:50:52
1040 19/03/2020 8:03:24 I 1 2 0 0840060140160 0 26/03/2020 9:50:53
1040 19/03/2020 17:13:44 I 1 2 0 0840060140160 0 26/03/2020 9:50:54
1040 20/03/2020 8:10:41 I 1 3 0 0840060140610 0 26/03/2020 9:51:10
1040 20/03/2020 8:10:44 I 1 3 0 0840060140610 0 26/03/2020 9:51:10
1040 20/03/2020 17:01:41 I 1 5 0 2809731360643 0 26/03/2020 9:51:44
1040 23/03/2020 8:00:07 I 1 2 0 0840060140160 0 26/03/2020 9:50:54
1040 23/03/2020 16:38:09 I 1 5 0 2809731360643 0 26/03/2020 9:51:45
1040 24/03/2020 7:59:08 I 1 5 0 2809731360643 0 26/03/2020 9:51:45
1040 24/03/2020 7:59:11 I 1 5 0 2809731360643 0 26/03/2020 9:51:45
1040 24/03/2020 16:39:30 I 1 2 0 0840060140160 0 26/03/2020 9:50:55
1040 24/03/2020 16:39:33 I 1 2 0 0840060140160 0 26/03/2020 9:50:55
1040 26/03/2020 8:10:31 I 1 3 0 0840060140610 0 26/03/2020 9:51:11
This is my CHECKEXACT looks like:
EXACTID USERID CHECKTIME
404 1040 09/03/2020 8:01:51
I've tried to achieve this using SUM aggregate function and IIf condition, but unfortunately the query give me wrong result.
This is my query:
SELECT af.USERID, SUM(
IIf(af.CHECKTIME Is Not Null,
IIf(WeekDay(DateValue(af.CHECKTIME)) <> 6 And Format(af.CHECKTIME, 'hh:nn:ss') <= '08:15:00',
1, IIf(Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
),
IIf(bf.CHECKTIME Is Not Null,
IIf(WeekDay(DateValue(bf.CHECKTIME)) <> 6 And Format(bf.CHECKTIME, 'hh:nn:ss') <= '08:15:00',
1, IIf(Format(bf.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
), 0
)
)
) AS [Came On Time or Early]
FROM (CHECKINOUT AS af
LEFT JOIN CHECKEXACT bf ON af.USERID = bf.USERID)
WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020# GROUP BY af.USERID
Above query returns this result:
USERID Came On Time or Early
1040 441
As we know if we COUNT CHECKINOUT table it would return 39 rows And CHECKEXACT only return 1 row. But the query returns 441 as [Came On Time or Early].
I don't know what is wrong with my query, i think i put the right query to get the total of came on time or early of employee with USERID = 1040 at March 2020.
Could you tell me what is wrong with my query ?
Thanks to @June7 to response this question through the comment.
After checked multiple times i realized that i wrote wrong query especially at this line :
IIf(WeekDay(DateValue(af.CHECKTIME)) <> 6 And Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00',
1, IIf(Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
)
I should separate ...WeekDay(DateValue(af.CHECKTIME)) <> 6... to another part.
My previous query would take Friday to be not Friday if af.CHECKTIME time is greater than '08:30:00' while the day is Friday. And then would jump to IIf on the false part where i suppose to operate another weekday.
And also i should change this line :
...
FROM (CHECKINOUT AS af
LEFT JOIN CHECKEXACT bf ON af.USERID = bf.USERID)
...
To this :
...
FROM(
SELECT af.USERID, MIN(af.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKINOUT AS af
WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY af.USERID, DateValue(af.CHECKTIME)
UNION
SELECT bf.USERID, MIN(bf.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKEXACT AS bf
WHERE bf.USERID = 1040 And bf.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY bf.USERID, DateValue(bf.CHECKTIME)
)
...
Because in the previous query, From would print all of datetime between the given range and LEFT JOIN CHECKEXACT would print data of CHECKEXACT that only a row (09/03/2020 8:01:51 every checking would be true because smaller than '08:30:00' and off course would print 1) repeatedly as many as CHECKINOUT's rows while i only need to check presence comes which is only the minimum datetime of each days both of CHECKINOUT and CHECKEXACT.
So the right complete query would look like this:
SELECT USERID,
SUM(IIf(WeekDay(DateValue([Tanggal dan Waktu])) <> 6,
IIf(TimeValue([Tanggal dan Waktu]) <= TimeValue('08:30:00'),
1, 0
),
IIf(TimeValue([Tanggal dan Waktu]) <= TimeValue('08:30:00'), 1, 0)
)
)
AS [Came On Time or Early]
FROM(
SELECT af.USERID, MIN(af.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKINOUT AS af
WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY af.USERID, DateValue(af.CHECKTIME)
UNION
SELECT bf.USERID, MIN(bf.CHECKTIME) AS [Tanggal dan Waktu]
FROM CHECKEXACT AS bf
WHERE bf.USERID = 1040 And bf.CHECKTIME Between #3/1/2020# And #3/31/2020#
GROUP BY bf.USERID, DateValue(bf.CHECKTIME)
)
GROUP BY USERID
The above query would print 6 as the correct / desired [Came On Time or Early] record.