October 17, 2016 at 1:58 pm
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 2016 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');
October 17, 2016 at 2:25 pm
DO NOT CROSS POST!!!
Looks like it's answered here.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
October 17, 2016 at 2:34 pm
Hello Ray,
Both are different query with different requirement but table pattern is same.
BR.
October 17, 2016 at 3:26 pm
philand3 (10/17/2016)
Hello Ray,Both are different query with different requirement but table pattern is same.
BR.
This is a cross post of this post: http://www.sqlservercentral.com/Forums/Topic1826702-3412-1.aspx.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy