SQL Server Performance Forum – Threads Archive
Access to Reporting Services
I have created 2 separate queries and one that combines the two in Access. I don’t know the correct format to use in Reporting Services for the combined report to run there: Query 1 MON _is Down Karen 10_05 SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp, First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to ‘MON-System Down’%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Query 2 MON_is Up Karen 10_05 SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp, First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to ‘MON-System Up’%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Query Combined
SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up], AHD_net_res.nr_prim_search_key
FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON [MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id; Thank you, Karen
I wanted to add the query in Reporting Services I have created: SELECT DISTINCT
AHD.call_req.ref_num, MIN(DISTINCT AHD.act_log.time_stamp) AS MINOftime_stamp, MIN(DISTINCT AHD.act_log.time_stamp) AS [Time Stamp Down],
DATEADD(ss, AHD.act_log.time_stamp – 18000, CONVERT(DATETIME, ‘1970-01-01 00:00:00’, 102)) AS [Time Down], DATEADD(ss,
act_log_1.time_stamp – 18000, CONVERT(DATETIME, ‘1970-01-01 00:00:00’, 102)) AS [Time Up], MAX(DISTINCT act_log_1.time_stamp)
AS [Time Stamp Up], MAX(DISTINCT act_log_1.time_stamp) AS MaxOftime_stamp, AHD.net_res.nr_prim_search_key
FROM AHD.net_res RIGHT OUTER JOIN
AHD.call_req ON AHD.net_res.id = AHD.call_req.affected_rc LEFT OUTER JOIN
AHD.act_log act_log_1 ON AHD.call_req.persid = act_log_1.call_req_id LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid = AHD.act_log.call_req_id
WHERE (AHD.act_log.action_desc LIKE ‘%to ”MON-System Down%’) AND (act_log_1.action_desc LIKE ‘%to ”MON-System Up%’)
GROUP BY AHD.call_req.ref_num, DATEADD(ss, AHD.act_log.time_stamp – 18000, CONVERT(DATETIME, ‘1970-01-01 00:00:00’, 102)), DATEADD(ss,
act_log_1.time_stamp – 18000, CONVERT(DATETIME, ‘1970-01-01 00:00:00’, 102)), AHD.net_res.nr_prim_search_key
ORDER BY AHD.net_res.nr_prim_search_key, AHD.call_req.ref_num —The query works up to a point. I changed "First" to "Min" and "Max". I am trying to weed out multiple instances of time_stamp when action_desc inlcludes Mon is Up or Mon is Down. In a perfect world the user would enter each only once but you know how that goes. So, I want the earliest time_stamp when action_desc includes "to Mon-System Down" and Max time_stamp when action_desc includes "to Mon-System Up". Thanks in advance for any help with this.
Can you post some sample data and the result you want? Madhivanan Failing to plan is Planning to fail
]]>