Table Creation Script
CREATE TABLE Customers(Row_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Cust_Name VARCHAR(255), Created_Date DATE, Cust_Status TINYINT)
The Rows in the table are as Below
INSERT INTO Customers(Cust_Name, Created_Date, Cust_Status) VALUES('Customer A', '20120516', 0), ('Customer B', '20120516', 0), ('Customer C', '20120516', 0), ('Customer A', '20120517', 1), ('Customer B', '20120517', 0), ('Customer C', '20120517', 0), ('Customer A', '20120520', 1), ('Customer B', '20120520', 0), ('Customer C', '20120520', 1), ('Customer A', '20120521', 0), ('Customer B', '20120521', 0), ('Customer C', '20120521', 1), ('Customer A', '20120526', 1), ('Customer B', '20120526', 1), ('Customer C', '20120526', 0), ('Customer A', '20120530', 1), ('Customer B', '20120530', 1), ('Customer C', '20120530', 0);
I want to take the rows which changes their Cust_Status from 0 to 1 only for first time.
When I run the script by giving 20120517 as parameter to where clause it should return Customer A.
When I run the script by giving 20120520 as parameter to where clause it should return Customer C.
When I run the script by giving 20120526 as parameter to where clause it should return Customer B.
SELECT C.Cust_Name FROM Customers C WHERE C.Created_Date = '20120526' AND C.Cust_Status = 1 AND NOT EXISTS (SELECT c2.Cust_Name FROM Customers c2 WHERE c2.Cust_Name = C.Cust_Name AND c2.Cust_Status = 1 AND c2.Created_Date < C.Created_Date)