issue comparing dates

Question

What I'm trying to do is if user A clocked in 2019/04/29, user A won't be able to clock in during that date until tomorrow or the day after.

So basically this SQL statement is working, but it wont show user A any error and it will insert double record.

     IF EXISTS(select createddate from clockin  where username = @Username and eventname = @b
group by createddate
HAVING COUNT(createddate) = 2
)

This doesn't work either, it won't allow any other dates to be inserted, because 29/04 existed, it won't allow 30/04 to come in. HAVING COUNT(createddate) = 1

I wan to display an error if user tries to clock in twice on 2019/04/29 and allow them to clock in on the any other dates.

logic

if (user and createddate doesn't exist)
{
allow clock in
}

else if user and createddate exists) 
{
deny clock in, show an error message.
}

else if (user exists but createddate doesn't match any of the dates) -- to allow user to clock in on other days
{
allow clock in
}

I'm kind of stuck on how to do this.

Your logic is flawed. What if the user clocks in at the beginning of the day, have to leave for several hours so clocks out, and then return in the same day? You will (if you get this to work, that is) deny the second clock in just because there's already a clock in that day. — Zohar Peled, Apr 30 '19 at 08:27
Right now , I'm only accounting for the clock in, no plans on adding the clock out just yet. I'm just trying to limit users to clock in once first. I can do this in an alternative way, which will remove double clock ins on the same day, but I won't be able to show the error to users, which I don't like it. — Visual Dot, Apr 30 '19 at 08:28
Your query doesn't take any date parameter. You are basically asking if any user has clocked in to a specific event more than once. Also, you might want to consider `count(distinct createdate)`, and I hope that the data type of the `createdate` column is `date`, otherwise you need to cast: `count(distinct cast(createdate as date))` — Zohar Peled, Apr 30 '19 at 08:31

score 1 · Accepted Answer · answered Apr 30 '19 at 08:50

Check this following script. This should server your purpose-

IF NOT EXISTS(
    SELECT * FROM clockin 
    WHERE CONVERT(VARCHAR, createddate, 101) = CONVERT(VARCHAR, GETDATE(), 101)
    AND username = @Username 
    AND eventname = @b
)
BEGIN
    --You can perform your Clocked steps here
    PRINT 'Not  Clocked Today'
END

ELSE
    PRINT 'Already Clocked Today'

issue comparing dates

1 Answers1