Hello everyone, I would like to ask for idea and help on how to achieve my concern. Below is my SQL statement. Im joining 2 tables to get my results. Here's the sample results of what im getting.
Name | Desc | Issue | ATime | Back | TotalTime | Ack | Res 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:21:33 | 00:02:07 | u...@home.net | fixed 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:25:33 | 00:02:07 | u...@home.net | fixed 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:29:33 | 00:02:07 | u...@home.net | fixed What I want to achieve is to get only the first entry of data based on Back and do my calculation. how can I do that? 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed Here's my statement. SELECT t1.name, t1.Description, t1.Issue, t1.Dateres AS ATime, t2.Dateres AS BAck, TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime, t2.Acknowledge, t2.Resolution FROM t1 LEFT JOIN t2 ON t1.name = t2.name AND t1.IPAddress = t2.IPAddress AND t1.Description = t2.Description AND t1.Issue = t2.Issue AND t1.Severity = t2.Severity AND t1.Timestamp = t2.Timestamp WHERE t1.Dateres is NOT NULL AND t2.Dateres is NOT NULL AND t2.Acknowledge = 'u...@home.net' AND t2.Dateres >= '2011-10-18 00:00:00' AND t2.Dateres <= '2011-10-23 23:59:59' GROUP BY ATime ORDER by BAck ASC; Thanks.
