|
draacor
Starting Member
2 Posts |
 Posted - 2009-08-20 : 11:39:24
|
| i have the following sql query that i am trying to use for Crystal Reports. What i need it to do is give me the most recent log comment on the ticket and hte date of that comment, which is where the #commentdate temp table comes into play. One question i have is what this query is doing is if there isnt a LOG comment it doesnt even list the ticket, i still want it to list the ticket its just that if there is a LOG comment that it picks the most recent date, I hope this helps.IF OBJECT_ID ('tempdb..#CommentDate') IS NOT NULL DROP TABLE #CommentDate;IF OBJECT_ID ('tempdb..#HoldDate') IS NOT NULL DROP TABLE #HoldDate;CREATE TABLE #CommentDate(call_req_id nvarchar (30),time_stamp int,analyst_name nvarchar (200),act_type nvarchar (30))CREATE TABLE #HoldDate(call_req_id nvarchar (30),time_stamp int,analyst_name nvarchar (200),act_type nvarchar (30))INSERT INTO #CommentDate(call_req_id,time_stamp,analyst_name,act_type)SELECT a.call_req_id, a.time_stamp, ca_contact.last_name + N', ' + ca_contact.first_name as analyst_name, a.type as act_typeFROM act_log AS a LEFT OUTER JOIN ca_contact ON a.analyst = ca_contact.contact_uuidWHERE a.type = 'LOG' AND a.time_stamp = (SELECT MAX(time_stamp) FROM act_log AS b WHERE b.call_req_id = a.call_req_id AND type = 'LOG')INSERT INTO #HoldDate(call_req_id,time_stamp,analyst_name,act_type)SELECT a.call_req_id, a.time_stamp, ca_contact.last_name + N', ' + ca_contact.first_name AS analyst_name, a.type as act_typeFROM act_log AS a LEFT OUTER JOIN ca_contact ON a.analyst = ca_contact.contact_uuidWHERE a.type = 'SLADELAY' AND a.time_stamp = (SELECT MAX(time_stamp) FROM act_log AS b WHERE b.call_req_id = a.call_req_id AND type = 'SLADELAY')SELECT call_req.ref_num, DATEADD(hour, (SELECT DATEDIFF(hour, getutcdate(), GETDATE()) AS Expr1), DATEADD(second, [#CommentDate].time_stamp, '01-01-1970')) AS Log_time_stamp, [#CommentDate].analyst_name, [#CommentDate].act_type, DATEADD(hour, (SELECT DATEDIFF(hour, getutcdate(), GETDATE()) AS Expr1), DATEADD(second, [#HoldDate].time_stamp, '01-01-1970')) AS Hold_time_stamp, [#HoldDate].analyst_name AS hold_analyst_name, [#HoldDate].act_type AS hold_act_type, dbo.cr_stat.sym AS ticket_status, DATEADD(hour, (SELECT DATEDIFF(hour, getutcdate(), GETDATE()) AS Expr1), DATEADD(second, call_req.open_date, '01-01-1970')) AS open_date, assignee.last_name + N', ' + assignee.first_name AS assignee, [group].last_name AS [group], dbo.pri.sym AS priority, dbo.call_req.summary, dbo.call_req.type AS ticket_typeFROM dbo.ca_contact AS [group] RIGHT OUTER JOIN dbo.pri RIGHT OUTER JOIN dbo.call_req INNER JOIN [#CommentDate] ON [#CommentDate].call_req_id = call_req.persid INNER JOIN [#HoldDate] ON dbo.call_req.persid = [#HoldDate].call_req_id ON dbo.pri.enum = dbo.call_req.priority LEFT OUTER JOIN dbo.ca_contact AS assignee ON dbo.call_req.assignee = assignee.contact_uuid ON [group].contact_uuid = dbo.call_req.group_id LEFT OUTER JOIN dbo.cr_stat ON dbo.call_req.status = dbo.cr_stat.codeWHERE (cr_stat.sym like 'Hold%') and (cr_stat.sym not like 'Hold-Escalated to Vendor%') and (cr_stat.sym not like 'Hold-RFC Submitted') and ([group].last_name not like 'Telecom%') |
|
replacing values with Null