Group By Day/Month/Year

I often find myself grouping data by various time periods. Usually, I use day/month/year. If you’ve played around with datemath at all, it can get rather complicated quickly. Early on, I found this article which really resonated with me.

I use SSMSTools SQL Snippets to assign groupbyday[tab] to automatically populate this:


dateadd(DAY, datediff(DAY, 0, {C}),0)

I replace the {C} with the field I’m interested in grouping by or rounding off, and replace “DAY” with the period I’m interested in.


SELECT dateadd(DAY, datediff(DAY, 0, o.OrderDate),0) AS OrderDay, count(*), SUM(o.OrderTotal) AS OrderTotal

FROM Orders o

GROUP BY dateadd(DAY, datediff(DAY, 0, o.OrderDate),0)

This also allows me to +/- days/months easily by adding or subtracting from the 2nd parameter in the outside function. The following shows all of yesterday’s orders.


DECLARE @StartDate datetime2;

SET @StartDate = dateadd(DAY, datediff(DAY, 0, getdate())-1,0)

DECLARE @EndDate datetime2;

SET @EndDate = dateadd(DAY, datediff(DAY, 0, getdate()),0)

SELECT SUM(o.OrderTotal) AS OrderTotal

FROM Orders o

WHERE o.OrderDate >= @StartDate AND o.OrderDate < @EndDate

I could push the logic to the left side (e.g. WHERE dateadd(DAY, datediff(DAY, 0, o.OrderDate),0) = dateadd(d,-1,getdate())), but that effectively forces SQL Server to skip any indexes it had on OrderDate. Here’s some good reading on using dates in SQL Server.

Do you have any good tips on handling dates in SQL? Let me know in the comments.

Locate SSRS reports subscriptions with SQL

I have some complex data-driven subscriptions in SSRS that both attach a report to the email and places a summary of the data within the email body template.

Sometimes an upstream ETL process fails and an SSRS report gets sent out that has invalid data. After the ETL issue is fixed, I need to send out a correct report to the stakeholders. I can either:

  1. Reschedule the report to a time a few minutes in the future, wait for it to send, and go back and correct it back to the original schedule,
  2. Run it manually within SSRS web interface/Visual Studio/Report builder and send an email out from my account,
  3. or …use SSMSTools SQL Snippets and open a query window, type “reports” and hit [tab] type in the name of the report, and have it spit out an EXEC statement that will retrigger the schedule for me immediately.

Here is a SQL Snippet I use frequently to locate SSRS Report subscriptions in case I need to run it manually.

SELECT
'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
+ CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
, b.name AS JobName
, a.SubscriptionID
, e.name
, e.path
, d.description
, laststatus
, eventtype
, LastRunTime
, date_created
, date_modified
FROM
ReportServer.dbo.ReportSchedule a
INNER JOIN msdb.dbo.sysjobs b ON convert(varchar(max),a.ScheduleID) = b.name
INNER JOIN ReportServer.dbo.ReportSchedule c ON b.name = convert(varchar(max),c.ScheduleID)
INNER JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
INNER JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE e.Name like '%{C}%'

Later, I plan on alleviating most of the ETL upstream incomplete issues with my ETL process dependency application, but this will do for now.

How do you handle upstream ETL processes failing, and the downstream reports?