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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply