jcelko212 32090 - Wednesday, January 3, 2018 8:02 PM

mcfarlandparkway - Wednesday, January 3, 2018 2:39 PM

>> I have a table, where it has status like active, terminated. <<

Where is the DDL for this table? Keys? Constraints? Data types? Did you read the basic forum rules before you posted?

I don’t you understand the basic principle of data modeling. Status is from the Latin word for a “state of being†And it implies that it has a temporal dimension. For example, if I had a properly named table instead of your vague generic data element name, such as “employment_statusâ€, it’s values would depend on the time interval. If they were currently employed then the end of my interval would be a NULL to indicate that it has not completed yet.

Please read this article:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/

>>I have checked the duplicate records [sic] in the table with pn_id how many active and how many terminated are there.. I found only one record [sic].<<

Please learn the difference between a row and a record. This is a fundamental concept in RDBMS. Now it’s time to start guessing at your DDL and correcting it. Even though we can’t see it 🙁

SELECT pn_id, COUNT(*)
FROM Active_Status --- Invalid table name
GROUP BY pn_id HAVING COUNT(*) > 1;

You don’t understand what a table is! The “active status†is not a set of entities or a relationship, but is a value of an attribute inside a table.

-- I found only one pn_id which has active and terminated record [sic].

>> Is this query [sic]] correct to delete records [sic]?<<

This is a statement, not a query.