SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE IF NOT EXISTS `TableA` ( `AgentID` int(11) NOT NULL, `SiteID` varchar(11) DEFAULT NULL, `TimeIn` time DEFAULT NULL, `TimeOut` time DEFAULT NULL, `RouteID` varchar(11) DEFAULT NULL, `Tickets` int(11) DEFAULT NULL, `Amount` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `TableA` (`AgentID`, `SiteID`, `TimeIn`, `TimeOut`, `RouteID`, `Tickets`, `Amount`) VALUES (1001, 'xyz', '11:00:10', '11:30:01', 'ABCDEF', 1, 100), (1001, 'abc', '11:35:12', '11:41:11', 'ABCDEF', 2, 200), (1001, 'def', '11:46:10', '11:48:45', 'ABCDEF', 0, 0), (1001, 'abc', '11:53:15', '11:59:20', 'ABCDEF', 3, 300), (1001, 'xyz', '12:07:10', '12:14:20', 'ABCDEF', 1, 100), (1001, 'xyz', '12:15:30', '12:31:15', 'ABCDEF', 0, 0), (1001, 'def', '12:38:16', '12:43:43', 'ABCDEF', 0, 0), (1001, 'abc', '12:48:28', '12:50:51', 'ABCDEF', 1, 100), (1001, 'pqr', '12:53:12', '12:55:57', 'ABCDEF', 0, 0), (1001, 'pqr', '12:55:58', '13:01:15', 'ABCDEF', 3, 300), (1001, 'pqr', '13:02:20', '13:15:30', 'ABCDEF', 1, 100), (1001, 'xyz', '13:21:08', '13:24:14', 'ABCDEF', 0, 0), (1001, 'abc', '13:28:06', '13:32:35', 'ABCDEF', 2, 200), (1001, 'pqr', '13:35:28', '13:47:19', 'ABCDEF', 0, 0), (1001, 'pqr', '13:48:05', '13:50:52', 'ABCDEF', 1, 100); create temporary table final_table with table2 as( with table1 as( select *, lead(SiteID) over(partition by AgentID, RouteID) as next_site, lag(SiteID) over(partition by AgentID, RouteID) as previous_site from TableA) select *,case when SiteID=next_site then 1 else 0 end as next_site_flag, case when SiteID=previous_site then 1 else 0 end as previous_site_flag from table1) select AgentID,SiteID,TimeIn,TimeOut,RouteID,Tickets, Amount, case when (next_site_flag=1 or previous_site_flag=1) then 1 else 0 end final_flag from table2; set @csum := 0; with table3 as( select *, (@csum := @csum + final_flag) as cumulative_sum from final_table order by TimeIn, TimeOut) select * from table3 order by TimeIn, TimeOut;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear