CODEWITHSUNDEEP
c#sqlasp.netsql-server
Visual Dot
Visual Dot

Reputation: 41

issue comparing dates

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.

Upvotes: 1

Views: 87

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

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'

Upvotes: 1

Related Questions