Finding Earliest Time, Latest Time -- Over a range of Dates

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

Answer 1

Never mind I got it :) Don't try to reinvent the wheel. 

Answer 2

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

Answer 3

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?

Answer 4

I apologize, I haven't been doing this as long as you, CELKO. 

Answer 5

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