Display Live clock event
-
Hi Experts,
I need to solve below two issues in my SQL query,
1.query should not show any duplicate clock event(ex: if it is last event clock_in than next query display should be only clock out ) whereas current query shows all clock event( double clock_in nor clock_out)
2.Each employee has two option(key enabled , badge enabled) 1 means enabled and 0 means not enabled. And this query should show matching this condition.Meaning X employee key enabled =0 and badge enabled=1 and query should display only if x employee use badge.
Existing SQL query is,
SELECT TOP 1 [action_id]
,[creation_date]
,[event_name]
,[PERSON_NUM]
FROM [dbo].[TEST]
order by Action_Id desc;
And table is,
CREATE TABLE dbo.test ( action_id int, person_num nvarchar(40), creation_date nvarchar(20), event_name nvarchar(40), badge_enabled int, key_enabled int,full_name nvarchar(40) );
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('168448','10148','7/21/2016 12:00','CLOCK_IN',’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('168780','10148','7/21/2016 15:00','CLOCK_OUT', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) `) VALUES ('169121','10148','7/23/2016 7:00','CLOCK_IN', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('169146','10148','7/23/2016 9:00','CLOCK_OUT', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('169148','10148','7/23/2016 13:00','CLOCK_IN', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('169340','10148','7/23/2016 17:00','CLOCK_OUT', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('169340','10148','7/23/2016 17:30','CLOCK_IN', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('169340','10148','7/23/2016 18:00','CLOCK_OUT', ’1’, ’0’,'OSU');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('169650','10146','7/24/2016 18:10','CLOCK_IN', ’0’, ’1’,'keni');
INSERT INTO dbo.test (`action_id`,`person_num`,creation_date`,`badge_enabled`,`key_enabled`,`event_name`,`full_name`) VALUES ('175112','10146','7/24/2016 18:20','CLOCK_OUT', ’0’, ’1’,'keni');
-
DO NOT CROSS POST!!!
Looks like it's answered here.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
Hello Ray,
Both are different query with different requirement but table pattern is same.
BR.
-
philand3 (10/17/2016)
Hello Ray,Both are different query with different requirement but table pattern is same.
BR.
Crossposting is poor etiquette, and it wastes people's time. Don't do it.
Apologies for sounding like a hardass (I'm not really like this, believe me), but this is a major pet peeve of mine.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
Ray agreed on cross post but i disagree on your statement where you mention that solution is already available for recent query.
since both requirement differ from each other.
BR
-
Are you even using sql server? Your insert statements look like mysql.
What do you expect for output based on your sample data? The business rules here are pretty vague and unclear.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
philand3 (10/17/2016)
Ray agreed on cross post but i disagree on your statement where you mention that solution is already available for recent query.since both requirement differ from each other.
BR
Looks to me like this one is an expansion of the other post. May have been better to keep them together.
So, what have you tried to complete this requirement? What error(s) or problem(s) did you encounter?
-
First Issue is:
my table dbo.test does not have any control on clock event(Clockin or Clocout) and table will accept whatever our Bio Metric machines throws the data. please note that our Bio metric machine has two button (IN button, Out Button).
First Event : Clock in (Button In)
Second Event : Clock in (Button In)
In above scenario first employee clock in and table get updated with clock in data and my SQL query shows
Same employee mistakenly pressing the "IN" button in bio metric machine and table will get updated but my SQL query should display "Duplicate In". example,
And it will be vice versa for Clockout and duplicate clockout.
Second issue should be solved in this same query,
Our biometric machine operates with badge(1) or numbers alias keyboard features.Employee can use badge or numbers for time attendance punches.scenario. person num 1019 s doing IN by using badge and query also shows .but it should disallow in query if they us keyboard features since person num 1019 does have not permission for the same but table is getting updated since table does not have any control.
-
philand3 (10/17/2016)
First Issue is:my table dbo.test does not have any control on clock event(Clockin or Clocout) and table will accept whatever our Bio Metric machines throws the data. please note that our Bio metric machine has two button (IN button, Out Button).
First Event : Clock in (Button In)
Second Event : Clock in (Button In)
In above scenario first employee clock in and table get updated with clock in data and my SQL query shows
Same employee mistakenly pressing the "IN" button in bio metric machine and table will get updated but my SQL query should display "Duplicate In". example,
And it will be vice versa for Clockout and duplicate clockout.
Second issue should be solved in this same query,
Our biometric machine operates with badge(1) or numbers alias keyboard features.Employee can use badge or numbers for time attendance punches.scenario. person num 1019 s doing IN by using badge and query also shows .but it should disallow in query if they us keyboard features since person num 1019 does have not permission for the same but table is getting updated since table does not have any control.
Don't see the images, you may need to load them to ssc for us to see them.
So, what have you tried, and what issues have you run into and please show all work, error messages, and current and expected results.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply