locked
Finding Earliest Time, Latest Time -- Over a range of Dates RRS feed

  • Question

  • Hi Folks,

    I have a report query which tracks report usage over time.

    SELECT C.Name 
              ,Case E.Requesttype  
               WHEN 1 THEN 'Subscription'  
               WHEN 0 THEN 'Report Launch' 
               ELSE '' 
               END 
              ,E.TimeStart  
              ,E.TimeProcessing 
              ,E.TimeRendering 
              ,E.TimeEnd 
              ,E.Status 
              ,E.InstanceName 
              ,E.UserName 
         FROM Reportserver.dbo.ExecutionLog E 
         JOIN Reportserver.dbo.Catalog C 
           ON E.ReportID = C.ItemID 
        --WHERE C.Name IN(@ReportName) 
        ORDER BY E.TimeStart DESC 
    	Having MIN(date)

    I am interested to find the earliest time, and latest time a report has been run over the past two months.

    I have made several attempts but I do not think I am getting it quite right, can anyone help?

    My attempts:

     SELECT  C.Name 
              ,Case E.Requesttype  
               WHEN 1 THEN 'Subscription'  
               WHEN 0 THEN 'Report Launch' 
               ELSE '' 
               END 
              ,E.TimeStart  
              ,E.TimeProcessing 
              ,E.TimeRendering 
              ,E.TimeEnd 
              ,E.Status 
              ,E.InstanceName 
              ,E.UserName 
         FROM Reportserver.dbo.ExecutionLog E 
    	 JOIN Reportserver.dbo.Catalog C 
         ON E.ReportID = C.ItemID 
    JOIN    (               
                SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, E.TimeStart), 0) _date
                        , MIN(E.TimeStart) TimeStart
                        , MAX(E.TimeEnd) TimeEnd
                FROM    Reportserver.dbo.ExecutionLog E 
                GROUP BY
                        DATEADD(DAY, DATEDIFF(DAY, 0, E.TimeStart), 0)
    ) t ON  t._date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
    AND     DATEADD(DAY, DATEDIFF(DAY, 0, E.TimeStart), 0) = t._date
    AND     (t.TimeStart = E.TimeStart OR t.TimeEnd = E.TimeEnd)

    second attempt:

    SELECT * FROM (
            SELECT C.Name 
              ,E.TimeStart  
              ,E.TimeProcessing 
              ,E.TimeRendering 
              ,E.TimeEnd 
              ,E.Status 
              ,E.InstanceName 
              ,E.UserName
              ,RANK() OVER ( ORDER BY E.TimeStart) rk1 --earliest record gets 1
              ,RANK() OVER (ORDER BY E.TimeEnd DESC) rk2 --latest record gets 1
         FROM Reportserver.dbo.ExecutionLog E 
    	 JOIN Reportserver.dbo.Catalog C 
         ON E.ReportID = C.ItemID 
            WHERE (E.TimeStart > dateadd(day, datediff(day, 0, GETDATE())-62, 0)) AND (E.TimeEnd < dateadd(day, datediff(day, 0, GETDATE()), 0))
        ) A
        WHERE rk2=1 
        or rk1 = 1

    Thursday, April 25, 2013 5:42 PM

Answers

  • Never mind I got it :) Don't try to reinvent the wheel. 
    Thursday, April 25, 2013 6:10 PM

All replies

  • Never mind I got it :) Don't try to reinvent the wheel. 
    Thursday, April 25, 2013 6:10 PM
  • Where is the DDL? Now we have to start typing and guessing because you did not follow Netiquette. What you did post is full of ISO-111790 errors. Why do you think that “name” is a clear, precise data element name? What about “status”? How did a report_id change into an item_id? 

    This garbage is why we need to see DDL, so we can fix it.  

    >> I have a report query which tracks report usage over time. I am interested to find the earliest time, and latest time a report has been run over the past two months. <<

    Based on what? Look at how useless your posting is. 

    SELECT C.vague_generic_name,
             CASE E.request_type  
               WHEN 1 THEN 'subscription'  
               WHEN 0 THEN 'report launch' 
               ELSE '' END AS  request_type,
              E.something_start_timestamp,
              E.timeprocessing,  -- explain this 
              E.timerendering, -- explain this 
              E.something_end_timestamp,
              E.employment_status, --why not?? status by itself is silly
              E.instance_name, 
              E.user_name 
         FROM Executionlog AS E,
              Something_Catalog AS C 
         WHERE E.item_id = C.item_id;

    Want to try again with good manners and useful information? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 25, 2013 6:53 PM
  • SELECT C.vague_generic_name,
             CASE E.request_type  
               WHEN 1 THEN 'subscription'  
               WHEN 0 THEN 'report launch' 
               ELSE '' END AS  request_type,
              E.something_start_timestamp,
              E.timeprocessing,  -- explain this 
              E.timerendering, -- explain this 
              E.something_end_timestamp,
              E.employment_status, --why not?? status by itself is silly
              E.instance_name, 
              E.user_name 
         FROM Executionlog AS E,
              Something_Catalog AS C 
         WHERE E.item_id = C.item_id;

    You do realize that these 'useless names' are Microsoft tables/columns straight from their report server database?
    Thursday, April 25, 2013 7:11 PM
  • I apologize, I haven't been doing this as long as you, CELKO. 

    Thursday, April 25, 2013 8:58 PM
  • I apologize, I haven't been doing this as long as you, CELKO

    Almost nobody has! Get me drunk and I will tell you how we invented dirt and ended the Stone Age :)

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 25, 2013 9:27 PM