2. Home
  3. Articles
  4. Microsoft
  5. SSRS
  6. Before and After, Highs and Lows

Before and After, Highs and Lows

SQL Server Reporting Services
Hits: 8626
So I'm looking for a SQL query that could do this all in one go and return all the results in one table.

With PHP & MySQL it's pretty simple: use individual SQL queries to get the count of yesterday, yesterweek, yestermonth, yesteryear and do the layout in PHP.

Now let's say I have one RDL or SSRS Solution. I could do a dataset per SQL query but it doesn't seem that ideal.

In Theory:
copyraw 
SELECT
     ItemName,
     DATEPART(dayofyear, ItemDate) as DayOfYear,
     DATEPART(year, ItemYear) AS YearRun,
     COUNT(ItemName) AS Counter
FROM
     TableName
WHERE
     ItemDate BETWEEN '01/01/2010' AND '08/01/2011'
GROUP BY
     DATEPART(dayofyear, ItemDate), 
     DATEPART(year, ItemYear), 
     ItemName


// yields something like

ItemName              DayOfYear      YearRun Counter
--------------------- -------------- ------- -------
Report 001            181            2010    134
Report 002            192            2010    12
Report 003            305            2010    479
Report 001            202            2011    84
Report 002            202            2011    45
Report 003            287            2011    94
  1.  SELECT 
  2.       ItemName, 
  3.       DATEPART(dayofyear, ItemDate) as DayOfYear, 
  4.       DATEPART(year, ItemYear) AS YearRun, 
  5.       COUNT(ItemName) AS Counter 
  6.  FROM 
  7.       TableName 
  8.  WHERE 
  9.       ItemDate BETWEEN '01/01/2010' AND '08/01/2011' 
  10.  GROUP BY 
  11.       DATEPART(dayofyear, ItemDate), 
  12.       DATEPART(year, ItemYear), 
  13.       ItemName 
  14.   
  15.   
  16.  // yields something like 
  17.   
  18.  ItemName              DayOfYear      YearRun Counter 
  19.  --------------------- -------------- ------- ------- 
  20.  Report 001            181            2010    134 
  21.  Report 002            192            2010    12 
  22.  Report 003            305            2010    479 
  23.  Report 001            202            2011    84 
  24.  Report 002            202            2011    45 
  25.  Report 003            287            2011    94 

A working example using the ReportServer database in SSRS 2008 R2:
copyraw 
SELECT        
	Catalog.Name AS ReportName, 
	DATEPART(dayofyear, ExecutionLogStorage.TimeStart) AS DayOfYearRun,
	DATEPART(week, ExecutionLogStorage.TimeStart) AS WeekRun,
	DATEPART(year, ExecutionLogStorage.TimeStart) AS YearRun,
	COUNT(Catalog.Name) AS Counter
FROM            
	ExecutionLogStorage 
	INNER JOIN 
	Catalog ON ExecutionLogStorage.ReportID = Catalog.ItemID
WHERE
	(Catalog.Type = 2) 
        AND (ExecutionLogStorage.TimeStart BETWEEN '01/01/2010' AND '08/01/2011')
GROUP BY 
        DATEPART(dayofyear, ExecutionLogStorage.TimeStart), 
        DATEPART(week, ExecutionLogStorage.TimeStart), 
        DATEPART(year, ExecutionLogStorage.TimeStart), 
        Catalog.Name
  1.  SELECT 
  2.      Catalog.Name AS ReportName, 
  3.      DATEPART(dayofyear, ExecutionLogStorage.TimeStart) AS DayOfYearRun, 
  4.      DATEPART(week, ExecutionLogStorage.TimeStart) AS WeekRun, 
  5.      DATEPART(year, ExecutionLogStorage.TimeStart) AS YearRun, 
  6.      COUNT(Catalog.Name) AS Counter 
  7.  FROM 
  8.      ExecutionLogStorage 
  9.      INNER JOIN 
  10.      Catalog ON ExecutionLogStorage.ReportID = Catalog.ItemID 
  11.  WHERE 
  12.      (Catalog.Type = 2) 
  13.          AND (ExecutionLogStorage.TimeStart BETWEEN '01/01/2010' AND '08/01/2011') 
  14.  GROUP BY 
  15.          DATEPART(dayofyear, ExecutionLogStorage.TimeStart), 
  16.          DATEPART(week, ExecutionLogStorage.TimeStart), 
  17.          DATEPART(year, ExecutionLogStorage.TimeStart), 
  18.          Catalog.Name 
Chart-wise: create a line chart [Sum(Counter)] will be the data field, [ReportName] will be the series field and [DayOfYearRun] will be category field:
line graph of this reportserver query


So is this doing what I wanted?
Well NO. I have a table with the same report mentioned several times but just in a different week. I want a returned table structured as follows:
copyraw 
Report Name        Run Last Week     Run This Week
------------------ ----------------- -------------------
Report 001         5                 2
Report 002         11                47
Report 003         7                 61
  1.  Report Name        Run Last Week     Run This Week 
  2.  ------------------ ----------------- ------------------- 
  3.  Report 001         5                 2 
  4.  Report 002         11                47 
  5.  Report 003         7                 61 

Multiple Datasets?
Again NO that's what I have at the moment. There must be a way. Or is time always on that dimension? HAVING is pointless, WITH ROLLUP is interesting, think I'm looking in the wrong place.

Using Aggregate Functions?
What follows is the T-SQL Query I finally went with. Lots of numbers but I think this pretty much answered my question:
copyraw 
SELECT        
	Catalog.Name AS ReportName
	, MIN(ExecutionLogStorage.TimeStart) AS [First]
	, MAX(ExecutionLogStorage.TimeStart) AS [Last]
	, CAST(CONVERT(CHAR, DATEADD(millisecond, MAX(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [High]
	, CAST(CONVERT(CHAR, DATEADD(millisecond, MIN(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [Low]
	, CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [Average]
	, CAST(CONVERT(CHAR, DATEADD(millisecond, SUM(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [Total]
	, COUNT(Catalog.Name) AS Counter
FROM            
	ExecutionLogStorage 
	INNER JOIN 
	Catalog ON ExecutionLogStorage.ReportID = Catalog.ItemID
WHERE
	(Catalog.Type = 2) 
        AND (ExecutionLogStorage.TimeStart BETWEEN '01/01/2010' AND '08/01/2011')
GROUP BY 
        Catalog.Name


// yielded
ReportName            First                   Last                    High                                         Low    Average   Total    Counter
--------------------- ----------------------- ----------------------- ---------------- ---------------- ---------------- ---------------- ----------------
Report 001            2011-07-11 15:46:25.637 2011-07-12 12:14:21.600 00:00:02.2130000 00:00:00.0470000 00:00:00.2600000 00:00:03.3770000 13
  1.  SELECT 
  2.      Catalog.Name AS ReportName 
  3.      , MIN(ExecutionLogStorage.TimeStart) AS [First] 
  4.      , MAX(ExecutionLogStorage.TimeStart) AS [Last] 
  5.      , CAST(CONVERT(CHAR, DATEADD(millisecond, MAX(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [High] 
  6.      , CAST(CONVERT(CHAR, DATEADD(millisecond, MIN(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [Low] 
  7.      , CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [Average] 
  8.      , CAST(CONVERT(CHAR, DATEADD(millisecond, SUM(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME) [Total] 
  9.      , COUNT(Catalog.Name) AS Counter 
  10.  FROM 
  11.      ExecutionLogStorage 
  12.      INNER JOIN 
  13.      Catalog ON ExecutionLogStorage.ReportID = Catalog.ItemID 
  14.  WHERE 
  15.      (Catalog.Type = 2) 
  16.          AND (ExecutionLogStorage.TimeStart BETWEEN '01/01/2010' AND '08/01/2011') 
  17.  GROUP BY 
  18.          Catalog.Name 
  19.   
  20.   
  21.  // yielded 
  22.  ReportName            First                   Last                    High                                         Low    Average   Total    Counter 
  23.  --------------------- ----------------------- ----------------------- ---------------- ---------------- ---------------- ---------------- ---------------- 
  24.  Report 001            2011-07-11 15:46:25.637 2011-07-12 12:14:21.600 00:00:02.2130000 00:00:00.0470000 00:00:00.2600000 00:00:03.3770000 13 
Category: SQL Server Reporting Services :: Article: 374

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.