Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Can someone help me this SQL syntax?

I am developing an Access "project" (.adp) as a front-end to data in a SQL Server 2005 Express database.

In the Access Form that allows employees to clock-in and clock-out of work, I want to control the "events" the employees are recording based on what they have already recorded that day. Specifically, if the last event they recorded was either a "start of a break" or an "end of a break", then I shouldn't allow them to immediately record the same event again.

I am using a SQL Server Stored Procedure to do this and am trying to discover if the last recorded event is the same as the new one they are trying to record.

Basically, I need to know how to check if the results of a "Top 1", single column SELECT query is a particular value. My various attempts (example attached) produce an "Incorrect syntax near '='" error message when I try to save the Stored procedure.

Can anyone show me how this should be written?

Many thanks. Colin.


SET @paramAlreadyUsedBoolean = ISNULL ((SELECT  TOP 1 ISNULL (ClockInEvent_Event, '')
						FROM TBL_ClockInEvents
						WHERE (ClockInEvent_Operator = @paramOperator) 
				 		AND (CONVERT (varchar(12), ClockInEvent_DateTime, 103) = CONVERT (varchar(12), @paramDate, 103)) 
						ORDER BY ClockInEvent_DateTime DESC), '') = @paramEvent

Open in new window

0
colinasad
Asked:
colinasad
1 Solution
 
knightEknightCommented:
SET @paramAlreadyUsedBoolean = (SELECT  TOP 1 ISNULL (ClockInEvent_Event, '')
                                                FROM TBL_ClockInEvents
                                                WHERE (ClockInEvent_Operator = @paramOperator)
                                                AND (CONVERT (varchar(12), ClockInEvent_DateTime, 103) = CONVERT (varchar(12), @paramDate, 103))
                                                ORDER BY ClockInEvent_DateTime DESC)
0
 
knightEknightCommented:
I see what you mean now, try this instead:

 select @paramAlreadyUsedBoolean = case when (SELECT  TOP 1 ISNULL (ClockInEvent_Event, '')
                                                FROM TBL_ClockInEvents
                                                WHERE (ClockInEvent_Operator = @paramOperator)
                                                AND (CONVERT (varchar(12), ClockInEvent_DateTime, 103) = CONVERT (varchar(12), @paramDate, 103))
                                                ORDER BY ClockInEvent_DateTime DESC)  = @paramEvent then 1 else 0 end


This will set @paramAlreadyUsedBoolean to 1 if it equals the result of the query, else 0.
0
 
knightEknightCommented:
but why is it necessary to do this in one step?  It would be much easier to read this way:

select @paramAlreadyUsedBoolean = TOP 1 ISNULL (ClockInEvent_Event, '')
                                                FROM TBL_ClockInEvents
                                                WHERE (ClockInEvent_Operator = @paramOperator)
                                                AND (CONVERT (varchar(12), ClockInEvent_DateTime, 103) = CONVERT (varchar(12), @paramDate, 103))
                                                ORDER BY ClockInEvent_DateTime DESC

if @paramAlreadyUsedBoolean =  @paramEvent
  select 'equal'
else
  select 'not equal'


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GlobaLevelCommented:
its a problem if no vlaue is returned..dont think thats gonna workk...

couple ways see this link..
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/46163/Using-T-SQL-ISNULL-what-if-the-record-doesn-t-exist

this is stated in the link:
SELECT CASE WHEN EXISTS ( SELECT * FROM References
                          WHERE description = 'Mister'
                            AND ref_type = 'title' )
           THEN 1
           ELSE 0
      END ;



0
 
knightEknightCommented:
I like the suggestion of using EXISTS.  But either way will work because if no row is returned it will simply compare the parameter to null and the result will be 0.
0
 
colinasadAuthor Commented:
Thanks for the very prompt replies.
I was trying to do it in a single statement for brevity and elegance (but was failing).
I also knew there would be other ways of achieving the same ends, but I was intrigued to get my original approach to at least be valid syntax.
I'll give your suggestions a go and get back to you.
Regards. Colin.
0
 
SharathData EngineerCommented:
check this.
SELECT  TOP 1 CASE WHEN ISNULL (ClockInEvent_Event, '') = @paramEvent THEN 'equal' ELSE 'not equal' END
						FROM TBL_ClockInEvents
						WHERE (ClockInEvent_Operator = @paramOperator) 
				 		AND (CONVERT (varchar(12), ClockInEvent_DateTime, 103) = CONVERT (varchar(12), @paramDate, 103)) 
						ORDER BY ClockInEvent_DateTime DESC

Open in new window

0
 
colinasadAuthor Commented:
Many thanks. That did the trick.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now