checking null values
-
Hi everyone. I am stucked,busy doing an access system for monitering the arrival,lunch and depature time of employees.I need a sql statement that checks if the timein and timeout column are null in the table. pls help. kind regards Minkinin.
SELECT * FROM SomeTable WHERE SomeColumn IS NULL
Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
SELECT * FROM SomeTable WHERE SomeColumn IS NULL
Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
let me clarify my question. I am busy with security access system for monitoring the arrival,lunch and depature time of employee,so when a user click an access card the system should check if that user has just arrived meaning that theres a record for arrival in the database,if not it should create it, so now I want a sql statement to check if theres null value in the table. my column names are: Date, Name, IN, OUT so if a user click an access card in the morning the record will be like this: DATE Name TimeIn OUT 07/02/07 Jomo 7:45 Null then when the user click the card during lunch, system willhave to check if the In and Out columns are null and insert that time on the null column, so when the user comes back from lunch it has to create a new record then the table will look like: Date Name Timein TimeOut 07/02/07 Jomo 7:45 12:00 o7/02/07 Jomo 1:00 Null so I need a sql statement which can always check if the time field is null so that it can insert time .
-
let me clarify my question. I am busy with security access system for monitoring the arrival,lunch and depature time of employee,so when a user click an access card the system should check if that user has just arrived meaning that theres a record for arrival in the database,if not it should create it, so now I want a sql statement to check if theres null value in the table. my column names are: Date, Name, IN, OUT so if a user click an access card in the morning the record will be like this: DATE Name TimeIn OUT 07/02/07 Jomo 7:45 Null then when the user click the card during lunch, system willhave to check if the In and Out columns are null and insert that time on the null column, so when the user comes back from lunch it has to create a new record then the table will look like: Date Name Timein TimeOut 07/02/07 Jomo 7:45 12:00 o7/02/07 Jomo 1:00 Null so I need a sql statement which can always check if the time field is null so that it can insert time .
You could always do it like this in a stored procedure:
IF EXISTS(SELECT * FROM Test WHERE [User] = @User AND [Out] IS NULL) BEGIN UPDATE Test SET [Out] = @TimeValue WHERE [User] = @User AND [Out] IS NULL END ELSE BEGIN INSERT INTO Test ([Date], [User], [In]) VALUES (GETDATE(), @User, @TimeValue) ENDSwapping in your parameters obviously.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
Hi everyone. I am stucked,busy doing an access system for monitering the arrival,lunch and depature time of employees.I need a sql statement that checks if the timein and timeout column are null in the table. pls help. kind regards Minkinin.
try this if ur field is x in table xyz which u have to check its null or blank select x from xyz where isnull(x,'')<>'' this query check both null or blank in ur x colum give only filled records
Rakesh Jha
-
You could always do it like this in a stored procedure:
IF EXISTS(SELECT * FROM Test WHERE [User] = @User AND [Out] IS NULL) BEGIN UPDATE Test SET [Out] = @TimeValue WHERE [User] = @User AND [Out] IS NULL END ELSE BEGIN INSERT INTO Test ([Date], [User], [In]) VALUES (GETDATE(), @User, @TimeValue) ENDSwapping in your parameters obviously.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.