converting statement

• Nathan Chapman

  SSChasing Mays

  Points: 620

  More actions

  September 29, 2005 at 1:17 am

  #593935

  For scenario 1:

  00456633  NULL

  00458176  NULL

  00461166  NULL

  For scenario 2:

  00456633  NULL

  00458176  2005-08-01 13:41:54   Resolved     1121646538

  00461166  2005-09-07 09:49:02   Resolved     1115274260

  For scenario 3:

  00456633 2005-08-26 14:02:07  Completed  1125026327 

  00458176 2005-08-26 14:59:19  Reassigned 1124951416 

  00461166 2005-09-15 13:15:00  Reassigned 1126225239 

  In summary:

  For CallID '00456633', there would no CaseResolvDatetime until scenario 3

  CallIDs  '00458176', '00461166' would have a CaseResolvDatetime until scenario 3

  Scenarios 1 & 3 are more quickly demonstrated with the following code (keep in mind that's it's the update statement I'm ultimately after):

  -------------------------------------------

  Declare @myTable table (callid char(8), resolvdatetime datetime, HeatSEQ int, Resolution varchar(15))

  Insert Into @myTable

  SELECT  '00456633','2005-04-18 16:30:38','1113803212','Reassigned' UNION ALL

  SELECT  '00456633','2005-08-26 13:18:32','1113805838','Completed' UNION ALL

  SELECT  '00456634','2005-08-26 14:02:07','1125026327','Completed' UNION ALL

  SELECT  '00456635','2005-08-26 15:31:36','1125028938','Completed' UNION ALL

  SELECT  '00458176','2005-05-02 13:45:37','1114472754','Mis-assigned' UNION ALL

  SELECT  '00458176','2005-05-13 11:11:59','1114472769','Partial' UNION ALL

  SELECT  '00458176','2005-05-19 10:03:01','1115946763','Reassigned' UNION ALL

  SELECT  '00458176','2005-05-23 08:51:03','1116465792','Completed' UNION ALL

  SELECT  '00458176','2005-06-01 17:12:14','1116460607','Reassigned' UNION ALL

  SELECT  '00458176','2005-06-01 17:13:33','1117609976','Completed' UNION ALL

  SELECT  '00458176','2005-07-12 09:53:31','1121125378','Completed' UNION ALL

  SELECT  '00458176','2005-07-18 10:24:18','1117609952','Completed' UNION ALL

  SELECT  '00458176','2005-08-01 13:41:54','1121646538','Resolved' UNION ALL

  SELECT  '00458176','2005-08-09 15:35:24','1122867743','Reassigned' UNION ALL

  SELECT  '00458176','2005-08-12 09:54:37','1123566048','Reassigned' UNION ALL

  SELECT  '00458176','2005-08-16 10:32:40','1123804715','Reassigned' UNION ALL

  SELECT  '00458176','2005-08-24 11:18:30','1124152393','Reassigned' UNION ALL

  SELECT  '00458176','2005-08-24 14:13:15','1124846513','Reassigned' UNION ALL

  SELECT  '00458176','2005-08-24 16:01:24','1124856820','Resolved' UNION ALL

  SELECT  '00458176','2005-08-25 16:29:44','1124863423','Reassigned' UNION ALL

  SELECT  '00458176','2005-08-26 14:59:19','1124951416','Reassigned' UNION ALL

  SELECT  '00458176','2005-09-09 10:42:44','1125032390','Completed' UNION ALL

  SELECT  '00461166','2005-09-07 09:49:02','1115274260','Resolved' UNION ALL

  SELECT  '00461166','2005-09-07 10:26:42','1126050552','Completed' UNION ALL

  SELECT  '00461166','2005-09-09 10:19:13','1126220086','Reassigned' UNION ALL

  SELECT  '00461166','2005-09-15 13:15:00','1126225239','Reassigned' UNION ALL

  SELECT  '00461166','2005-09-15 13:15:11','1126754120','Resolved'

  Select callid, resolvdatetime AS CaseResolvDatetime, HeatSEQ, resolution

  From @myTable as t1

  Where (Select count(distinct HeatSEQ) From @myTable Where callid=t1.callid and HeatSEQ > t1.HeatSEQ)=1

  Order By callid, resolvdatetime Desc

  Select callid, resolvdatetime AS CaseResolvDatetime, HeatSEQ, resolution

  From @myTable as t1

  Where (Select count(distinct HeatSEQ) From @myTable Where callid=t1.callid)=1

  Order By callid, resolvdatetime Desc

  -------------------------------------------------