I was able to display the attendance from my SQL but I can't display the weekdays where I was absent.
Here is my table: enter image description here
Here is my current query
Userid Date TimeIn TimeOut Remark
7 7/25/2023 7:55:14 Didn't Clock Out
7 7/24/2023 7:41:27 17:07:59
7 7/21/2023 7:47:03 17:17:37
7 7/20/2023 7:51:43 17:04:53
7 7/19/2023 7:53:53 17:07:28
7 7/14/2023 8:47:35 17:17:15
7 7/13/2023 8:36:06 17:07:25
7 7/12/2023 7:49:26 17:05:15
7 7/7/2023 7:50:55 17:03:52
7 7/6/2023 7:52:57 17:11:54
But I want to display like this:
Userid Date TimeIn TimeOut Remark
7 7/25/2023 7:55:14 Didn't Clock Out
7 7/24/2023 7:41:27 17:07:59
7 7/21/2023 7:47:03 17:17:37
7 7/20/2023 7:51:43 17:04:53
7 7/19/2023 7:53:53 17:07:28
7 7/18/2023 absent
7 7/17/2023 absent
7 7/14/2023 8:47:35 17:17:15
7 7/13/2023 8:36:06 17:07:25
7 7/12/2023 7:49:26 17:05:15
7 7/7/2023 7:50:55 17:03:52
7 7/6/2023 7:52:57 17:11:54
Here is my code, I hope somebody will update the correct query.
SELECT UserID 'Userid',
CONVERT(DATE,TransactionTime) 'Date',
MIN(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) 'TimeIN',
CASE WHEN MIN(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) = MAX(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) THEN
''
ELSE
MAX(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8)))
END 'TimeOut',
CASE WHEN MIN(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) = MAX(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) THEN
'Didn''t Clock Out'
ELSE
''
END 'Remark'
FROM NGAC_AUTHLOG WHERE UserID='0007'
GROUP BY UserID,DATEPART(MONTH,TransactionTime),CONVERT(DATE,TransactionTime)
Order by CONVERT(DATE,TransactionTime) desc
First Result from comments: enter image description here
Last Results from comments: enter image description here
1条答案
按热度按时间t40tm48m1#
The simplest way to solve this is to add a calendar table to your database, where in that table you'll have a column for the date, and (at least) a column that indicates if that date is a workday or not.
Here's an article by Aaron Bertrand explaining how you can easily build one. His calendar table is very informative in terms of what is stored in it, but for the purpose if this question a simple bit column called
IsWorkDay
will suffice.Something as simple as that would do the trick:
I've created a sample script for generating a years worth of dates for such a table, including the week day name - you can use it if you want or create your own. Note that my script doesn't take into account holydays, only weekends (Saturday and Sunday). You can run it on db<>fiddle and change to your needs.
Once you have your dates table set up, it's a simple
LEFT JOIN
query:Note: You'll need to select
@UserId
and notN.UserId
because when the user is absent, there will be no record of them in theNGAC_AUTHLOG
table.That is also why the condition
N.UserId = @UserId
is in theON
clause and not in theWHERE
clause - because if it was in theWHERE
clause, that would effectively convert your left join to an inner join.Update
@siggemannen commented, rightfully, "You probably want to add some selection on non-weekend dates."
There are two ways to do that - you can either choose to select only work days - so adding a
WHERE
clause to the query (and removing the now-redundant second condition in theCASE
expression) -Or, you can change the
CASE
expression to add a remark for days that aren't work dayes: