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
– Zohar Peled, Commented Apr 30, 2019 at 8: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
– Visual Dot, Commented Apr 30, 2019 at 8: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
– Zohar Peled, Commented Apr 30, 2019 at 8:31

mkRabbani · Accepted Answer · 2019-04-30 08:50:43Z

1

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'

answered Apr 30, 2019 at 8:50

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

issue comparing dates

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related