converting statement
-
What you've done looks a bit different with a few different variables etc. I actually don't need this data in the database at all:
--SELECT '00500588','Desktop', NULL UNION ALL
--SELECT '00500588','SD Followup', NULL UNION ALL
--SELECT '00500600','Server', NULL UNION ALL
--SELECT '00500600','Service Desk', NULL UNION ALL
--SELECT '00500777','SD Followup', NULL UNION ALL
--SELECT '00500777','Network', NULL
I just need to update what is existing in the database.
N.
-
"...I actually don't need this data in the database at all:..."
That's just the data I used to test the query and make sure it works. See the comment,
"-- create and populate testing source table"
Really, all you need is the update query.
--------------------
Colt 45 - the original point and click interface
-
Not sure where to go from here. You've got a JOIN in the update where I'm only touch the 1 table in the database itself, and I don't see a 'FROM Asgnmnt' anywhere in there, so not sure how the update would put Y/N into the database table.
N
-
Simple really, remove all the guff for the testing and you're left with the update statement. The update statement refers to a table variable that was created for testing, so replace it with the table in your database.
I do have a JOIN in the update, but you'll see that I'm joining to the same table. This is called a derived table.
--------------------
Colt 45 - the original point and click interface -
Really sorry Phil, but I think that's what I was having trouble with in the first place - having a test statement, and replacing the 'test statement' with the details for the database itself.
ie I had INSERT @table(Asgnmnt.CallID, Asgnmnt.GroupName, CustPendCase)
..and adding FROM Asgnmnt
produces and error. Without the FROM Asgnmnt, no results are returned and nothing in the table is updated. I had tried:
SET @Asgnmnt=(SELECT CallID, GroupName, NULL) FROM Asgnmnt
and a few things which all produced syntax errors. I know this thread deserves to be in the newbie forum and I thank you for your patience.
-
Not quite sure from that post if you've got it worked out
And don't worry about being a "newbie", we were all there at one stage, and in some areas I still consider myself to be a "newbie".
--------------------
Colt 45 - the original point and click interface -
Don't yet have it working.
-
Ok, try this then,
-- Update CustPendCase to Y or N UPDATE Asgn SET Asgn.CustPendCase = CASE WHEN ISNULL(FlwUp.CallId, '') = '' THEN 'N' ELSE 'Y' END FROM Asgnmnt Asgn LEFT JOIN ( SELECT CallId FROM Asgnmnt WHERE GroupName = 'SD Followup' ) as FlwUp ON Asgn.CallID = FlwUp.CallID
--------------------
Colt 45 - the original point and click interface -
That has worked. Thank you so much.
-
For curiosity sake, how long did it take to run?
--------------------
Colt 45 - the original point and click interface -
About 20 seconds(?) There were approx 460k rows to update.
-
Using a very similar construct.. this time I get the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Asgn'.
-------------------
UPDATE Asgn
SET Asgn.CaseResolvDatetime = CASE
WHEN ISNULL(t1.CallId, '') = ''
THEN ResolvDatetime ELSE NULL END
FROM Asgnmnt Asgn
WHERE (Select count(distinct HeatSEQ)
FROM Asgn
WHERE callid=t1.callid)=1
--------------------
Suggestion welcome.
-
What are you trying to do with this query? Looks like you're trying to update the field CaseResolvDatetime if there is only one record in the table.
--------------------
Colt 45 - the original point and click interface -
That's basically right. Unfortunately I need to mangle resolvdatetime into an additional column 'CaseResolvDatetime'. HeatSEQ looks to be like an index number i.e. whenever any CallID is updated/created, this is incremented by one. The CaseResolvDatetime criteria is in this order:
1. Where there is only 1 instance of HeatSEQ for a particular CallID, resolvdatetime = CaseResolvDatetime
2. If CaseResolvDatetime is still NULL for a particular CallID, resolvdatetime = CaseResolvDatetime for the last occurance of Resolution='Resolved' (yes, a CallID can be set to resolution='resolved' a number of times)
3. If CaseResolvDatetime is still NULL for a particular CallID, 2nd last resolvdatetime = CaseResolvDatetime (regardless of Resolution)
A sample of what the data looks like:
CallID resolvdatetime Resolution HeatSEQ
--------- --------------------- ---------- -----------
00456633 2005-08-26 13:18:32 Completed 1113805838
00456633 2005-08-26 14:02:07 Completed 1125026327
00456633 2005-08-26 15:31:36 Completed 1125028938
00458176 2005-05-02 13:45:37 Mis-assigned 1114472754
00458176 2005-05-13 11:11:59 Partial 1114472769
00458176 2005-05-19 10:03:01 Reassigned 1115946763
00458176 2005-05-23 08:51:03 Completed 1116465792
00458176 2005-06-01 17:12:14 Reassigned 1116460607
00458176 2005-06-01 17:13:33 Completed 1117609976
00458176 2005-07-12 09:53:31 Completed 1121125378
00458176 2005-07-18 10:24:180 Completed 1117609952
00458176 2005-08-01 13:41:540 Resolved 1121646538
00458176 2005-08-09 15:35:24 Reassigned 1122867743
00458176 2005-08-12 09:54:370 Reassigned 1123566048
00458176 2005-08-16 10:32:40 Reassigned 1123804715
00458176 2005-08-24 11:18:30 Reassigned 1124152393
00458176 2005-08-24 14:13:15 Reassigned 1124846513
00458176 2005-08-24 16:01:24 Resolved 1124856820
00458176 2005-08-25 16:29:44 Reassigned 1124863423
00458176 2005-08-26 14:59:19 Reassigned 1124951416
00458176 2005-09-09 10:42:44 Completed 1125032390
00461166 2005-09-07 09:49:02 Resolved 1115274260
00461166 2005-09-07 10:26:42 Completed 1126050552
00461166 2005-09-09 10:19:13 Reassigned 1126220086
00461166 2005-09-15 13:15:00 Reassigned 1126225239
00461166 2005-09-15 13:15:11 Resolved 1126754120
-
So from that data, these are the records with the relevant datetime ??
00456633 2005-08-26 15:31:36 Completed 1125028938 00458176 2005-08-24 16:01:24 Resolved 1124856820 00461166 2005-09-15 13:15:11 Resolved 1126754120
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply