{"id":645,"date":"2012-09-11T02:57:26","date_gmt":"2012-09-11T02:57:26","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=298"},"modified":"2016-09-10T15:26:29","modified_gmt":"2016-09-10T15:26:29","slug":"getting-rows-from-table-whose-status-changes-first-time","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/getting-rows-from-table-whose-status-changes-first-time\/","title":{"rendered":"Getting Rows from table whose status changes first time"},"content":{"rendered":"<p><strong>Table Creation Script<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE Customers(Row_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,\r\n                       Cust_Name VARCHAR(255),\r\n                       Created_Date DATE,\r\n\t\t       Cust_Status TINYINT)\r\n<\/pre>\n<p>The Rows in the table are as Below<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nINSERT INTO Customers(Cust_Name, Created_Date, Cust_Status)\r\n               VALUES('Customer A', '20120516', 0),\r\n                     ('Customer B', '20120516', 0),\r\n                     ('Customer C', '20120516', 0),       \r\n                     ('Customer A', '20120517', 1),\r\n                     ('Customer B', '20120517', 0),\r\n  \t\t     ('Customer C', '20120517', 0),\r\n\t\t     ('Customer A', '20120520', 1),\r\n\t\t     ('Customer B', '20120520', 0),\r\n\t             ('Customer C', '20120520', 1),\r\n\t\t     ('Customer A', '20120521', 0),\r\n                     ('Customer B', '20120521', 0),\r\n\t\t     ('Customer C', '20120521', 1),                                                               \r\n                     ('Customer A', '20120526', 1),\r\n\t\t     ('Customer B', '20120526', 1),\t\t\t\t \t\r\n                     ('Customer C', '20120526', 0),\t\t\t\t \t\r\n                     ('Customer A', '20120530', 1),\r\n\t\t     ('Customer B', '20120530', 1),\t\t\t\t \t\t\r\n                     ('Customer C', '20120530', 0);\r\n<\/pre>\n<p>I want to take the rows which changes their Cust_Status from 0 to 1 only for first time.<br \/>\nWhen I run the script by giving 20120517 as parameter to where clause it should return Customer A.<br \/>\nWhen I run the script by giving 20120520 as parameter to where clause it should return Customer C.<br \/>\nWhen I run the script by giving 20120526 as parameter to where clause it should return Customer B.   <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT  C.Cust_Name\r\n  FROM  Customers C\r\n WHERE  C.Created_Date = '20120526' AND\r\n        C.Cust_Status  = 1          AND\r\n        NOT EXISTS (SELECT c2.Cust_Name\r\n  \t              FROM Customers c2\r\n      \t\t     WHERE c2.Cust_Name   = C.Cust_Name AND\r\n\t                   c2.Cust_Status = 1 AND\r\n\t                   c2.Created_Date &amp;lt; C.Created_Date)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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(&#8216;Customer A&#8217;, &#8216;20120516&#8217;, 0), (&#8216;Customer B&#8217;, &#8216;20120516&#8217;, 0), (&#8216;Customer C&#8217;, &#8216;20120516&#8217;, 0), (&#8216;Customer A&#8217;, &#8216;20120517&#8217;, 1), (&#8216;Customer B&#8217;, &#8216;20120517&#8217;, 0), (&#8216;Customer C&#8217;, &#8216;20120517&#8217;, 0),&hellip; <a href=\"https:\/\/codethataint.com\/blog\/getting-rows-from-table-whose-status-changes-first-time\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-645","post","type-post","status-publish","format-standard","hentry","category-queries"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/645","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=645"}],"version-history":[{"count":1,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/645\/revisions"}],"predecessor-version":[{"id":1615,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/645\/revisions\/1615"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=645"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=645"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=645"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}