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)
Posted in SQL.

Leave a reply