How can I exclude the tech_id's which are exactly repeating in SQL server?

Question

0.00/5 (No votes)

See more:

Work_Order_No	Tech_ID	ST_DATE
397129	         1205	2020-12-22 00:00:00.000
397129	         1315	2021-01-07 00:00:00.000
397129	         1205	2021-01-07 00:00:00.000
397129	         1293	2021-01-12 00:00:00.000
397129	         1263	2021-01-12 00:00:00.000
397129	         1205	2021-01-12 00:00:00.000

397595	         1256	2020-12-30 00:00:00.000
397595	         1358	2020-12-30 00:00:00.000
397595	         1322	2020-12-30 00:00:00.000
397595	         1256	2021-01-06 00:00:00.000
397595	         1322	2021-01-06 00:00:00.000
397595	         1358	2021-01-06 00:00:00.000

I am trying to Groupby Work_Order_No and want to see only the Tech_Id's which are not repeating: for example I want to exclude Work_Order_No = 397595, because on 2021-01-06 the exact same 3 tech worked as on 2020-12-30.
Basically, I just want those work_order_no's where exact same tech's did not go on another ST_Date: for example I would want the Work_order_no = 397129 in my results, because it has different Tech_id's going on different ST_DATE's

Not sure what Having condition to put here? the ones which I am putting would not include Work_order_no = 397129, which I want

What I have tried:

SELECT WORK_ORDER_NO,max(DISTINCT TECH_ID),St_date
FROM TR_LBR
group by WORK_ORDER_NO,St_date
ORDER BY WORK_ORDER_NO

Posted 29-Mar-22 5:34am

Member 15583281

Updated 29-Mar-22 10:29am

v8

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2022-03-29T10:14:00

Solution 2

I'm not sure what you want to achieve...

Try this:

SQL

SELECT Work_Order_No, Tech_ID, COUNT(*) AS Cnt
FROM SampleData
GROUP BY Work_Order_No, Tech_ID
HAVING COUNT(*)>1;

This will give you:

Work_Order_No 	Tech_ID 	Cnt
397129 	 	 	1205 	 	3
397595 	 	 	1256 	 	2
397595 	 	 	1322 	 	2
397595 	 	 	1358 	 	2

SQL Server 2019 | db<>fiddle[^]

Posted 29-Mar-22 10:14am

Maciej Los

Comments

Member 15583281 29-Mar-22 16:28pm

I am looking for those Work_order_no's where the same technician (Tech_id) is not going on the different date (ST_DATE)

OriginalGriff · Answer 2 · 2022-03-29T05:43:00

Solution 1

Try a HAVING clause: SQL HAVING Clause[^] If you COUNT the number of different Techs and eliminate those with only one that should give you what you need.

Posted 29-Mar-22 5:43am

OriginalGriff

Comments

Member 15583281 29-Mar-22 11:48am

SELECT WORK_ORDER_NO,count(DISTINCT TECH_ID)
FROM TR_LBR
group by WORK_ORDER_NO
having count(DISTINCT TECH_ID)>1
ORDER BY WORK_ORDER_NO

tried this but didnt work, it still show like :
WORK_ORDER_NO Tech_id
397129 4
397595 3

Maciej Los 29-Mar-22 16:14pm

Short And To The Point!