Resolution Datetime and GroupName

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Resolution Datetime and GroupName

Author

Topic

meth73
Starting Member

10 Posts

Posted - 2005-09-25 : 16:04:15

Hi guys,

I am trying to add data to 2 columns CaseResolvDatetime and CaseResolvGroupName based on the criteria:
1. for a CallID, the last datetime where Resolution='Resolved'
2. where there is no 'Resolved', the 2nd last datetime & groupname for a particular CallID

-----------------------------------------------
DECLARE @table TABLE(CallID CHAR(8), ResolvDatetime DATETIME, GroupName VARCHAR(15), Resolution VARCHAR(15), CaseResolvDatetime datetime, CaseResolvGroupName varchar (15))

INSERT @table(CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName)
SELECT '00458176','08/1/2005 1:41:54 PM','Field Sup CoOrd','Resolved',Null,Null UNION ALL
SELECT '00458176','08/9/2005 3:35:24 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/12/2005 9:54:37 AM','BSSAppSvcs-Club','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/16/2005 10:32:40 AM','CDR SysOps','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 11:18:30 AM','CDR Server','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 2:13:15 PM','Field Sup CoOrd','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 4:01:24 PM','CDR Server','Resolved',Null,Null UNION ALL
SELECT '00458176','08/25/2005 4:29:44 PM','Field Sup CoOrd','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/26/2005 2:59:19 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00458176','09/9/2005 10:42:44 AM','Field Sup - Vic','Completed',Null,Null UNION ALL
SELECT '00461166','09/7/2005 9:49:02 AM','BSSAppSvcs-Apps','Resolved',Null,Null UNION ALL
SELECT '00461166','09/7/2005 10:26:42 AM','Service Desk','Completed',Null,Null UNION ALL
SELECT '00461166','09/9/2005 10:19:13 AM','BSSAppSvcs-Apps','Reassigned',Null,Null UNION ALL
SELECT '00461166','09/15/2005 1:15:00 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00461166','09/15/2005 1:15:11 PM','Service Desk','Resolved',Null,Null UNION ALL
SELECT '00456633','08/08/2005 1:18:32 PM','BSSAppSvcs-Club','Completed',Null,Null UNION ALL
SELECT '00456633','08/26/2005 2:02:07 PM','Service Desk','Completed',Null,Null UNION ALL
SELECT '00456633','08/26/2005 3:31:36 PM','Service Desk','Completed',Null,Null

SELECT CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName FROM @table

UPDATE @table

SET CaseResolvDatetime =
CASE WHEN Resolution = 'Completed' AND CallId = (select CallId from @table where GroupName like 'Ser%' AND Resolution = 'Completed') then ResolvDatetime
ELSE NULL
END

SELECT CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName FROM @table
-------------------------------------------------

At the moment I get the error:

Server: Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Yet when I change the line:

"where GroupName like 'Ser%'" to "where GroupName like 'CDR%'"
... I get no error.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-25 : 16:14:06

this subquery
(select CallId from @table where GroupName like 'Ser%' AND Resolution = 'Completed')
returned more than one value, meaning you have more than 1 row that has condition
where GroupName like 'Ser%' evaluate to true.

Go with the flow & have fun! Else fight the flow

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-25 : 18:10:27

Your query doesn't make sense anyway. Is this just an example you're posting us of the real issue? Why wouldn't you just use CASE WHEN Resolution = 'Completed' AND GroupName LIKE 'Ser%' THEN blah, blah, blah?

That's going to give you the exact same result. Right now, you're running everything twice, searching for Resolution = 'Completed' twice, and then matching a CallId which isn't even necessary if you already know the GroupName and Resultion criteria you need in the CASE statement.

Of course, there's a possibility I'm missing something here?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources