This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(64, 25%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESK%3C/text%3E%3C/svg%3E)
I am using SQL query which shows the result of 1st IN & Last Out entry of unique UserID for current day from our attendance system SQL database.
In this query, I want to exclude some devices like - Bike Barrier Enter & Flap Barrier Enter 2.
Can you please help me, how can I exclude these devices from below query.
Not sure if I'm missing something, but why not just extend the WHERE clause in the CTE with:
AND InLoc NOT IN ('Bike Barrier Enter', 'Flap Barrier Enter 2')
As for the "Access Denied" error, it may be that your query text is somehow trapped by a spam filter (please don't ask why). But you could attach the query in a file. (But if my Answer helps you, I don't think there is a need to do this.)
Thanks Erland for the hint..!!!
Adding below line in my SQL query helped me.
AND Devicename Not in ('Flap Barrier Enter 1','Flap Barrier Enter 2','Bike Barrier Enter')
That's to less on information and we can't "query images" to reproduce/test it.
Please post table design as DDL, some sample data as DML statement and the expected result.
I am trying to post this SQL query in comments box but getting an below access denied error.
Access Denied
You don't have permission to access "http://learn.microsoft.com/answers/answers/940704/post.html" on this server.
Reference #18.34367c68.1658751368.7949b86
Hence I have posted this as image. Any other way I can post this query in text format.?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Try to put your second query into another cte and then you should be able to add filter to it as Erland suggested. I can see you cannot do this directly in your query based on how it's structured.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 56.00000000000001%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBZ%3C/text%3E%3C/svg%3E)
Welcome to Microsoft T-SQL Q&A Forum!
Have you tried adding a where filter after select --- FROM SourceTable like this:
where NOT INLOC='Bike Barrier Enter' or NOT INLOC= 'Flap Barrier Enter 2'
Best regards,
Bert Zhou
Bert, I don't think that is correct. Since InLoc can't have two different values at the same time, one of the OR terms will always be TRUE. Look at this:
CREATE TABLE #test (a int NOT NULL)
INSERT #test (a) VALUES (1), (2)
SELECT a FROM #test WHERE NOT a = 1 OR NOT a = 2
The SELECT returns two rows.
Thanks for the kind reminder, I tried your case and there is something wrong and will correct it.
Bert Zhou