SQL Server Performance

Access to Reporting Services

Discussion in 'SQL Server Reporting Services' started by Karen Turner, Oct 5, 2005.

  1. Karen Turner New Member

    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

  2. Karen Turner New Member

    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.
  3. Madhivanan Moderator

    Can you post some sample data and the result you want?

    Madhivanan

    Failing to plan is Planning to fail

Share This Page