I'm trying to create a time clock application for the little office where I work, but I'm quite new to SQL. Currently experimenting with the SQL backend, I have a compound statement query. Where I get stuck is that SQL needs to build a new row rather than update an existing one if a user wants to clock out for a break but never clocked in at the start of the shift.
Here is what I tried:
IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = '08/10/2012') AND userName = 'test')
BEGIN
INSERT INTO Clock(clockDate, userName, breakOut)
VALUES({ fn NOW() }, 'test', { fn NOW() })
END
ELSE
BEGIN
UPDATE Clock
SET breakOut = { fn NOW() }
WHERE (clockDate = '08/10/2012') AND (userName = 'test')
END
I'm working with SQL Server Express 2008 on my own machine using Visual Studio 2010. The Compound Statement SQL construct or statement is not supported, according to the error message I receive. But then I get a notification saying that 1 row has been impacted, and when I inspect my Clock table, it exactly matches my expectations. What is the most effective technique to complete this?
And I've answered the second portion of this query in my WHERE expressions. Is there a way to retrieve the current date in the clockDate column without having to manually fill it in? merely making an effort to plan ahead for developing the front end application.
IF NOT EXISTS(SELECT * FROM Clock WHERE clockDate = { fn CURRENT_DATE() }) AND userName = 'test')
Again, this gives me the results I want, but not until after getting an error "Error in WHERE clause near 'CURRENT_DATE'. Unable to parse query text.". Can someone please help me with this?
