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?

Welcome to my meanderings

Hello visitor,

I am Levi Johnston. My passions include technology mostly of the software variety, but I have dived into some hardware hacking.

With this blog I plan on writing various things about the solutions I’ve found that I don’t want to forget. It might bore you to tears, but hopefully you’ll learn a thing or two, and skip past some of the pitfalls I’ve encountered. My current career trajectory is dealing with big data and providing meaningful nuggets from the mounds of data. I also build OLTP and OLAP data schemas and integration packages mostly working with Microsoft technologies, but I also work with Oracle and MySQL frequently. SQL, SSIS, Crystal Reports and SSRS are my forte, but here is a trimmed list of technologies and applications I am familiar with in alphabetical order:

  • AHK
  • ANSI SQL
  • Business Objects
  • Crystal Reports
  • C#.NET
  • CSS
  • HTML/5
  • Javascript
  • JQuery
  • mIRC MSL
  • Microsoft Access
  • Microsoft SQL Server 2000,2005,2008,2008R2,2012
    • Administration
    • Design
    • Development
  • Microsoft SQL Server Integration Services (SSIS)
  • Microsoft SQL Server Management Studio (SSMS)
  • Microsoft SQL Server Reporting Services (SSRS)
  • Microsoft Team Foundation Server
  • MySQL
  • Node.JS
  • Oracle
  • PHP
  • PL/SQL
  • Raspberry Pi
  • T-SQL
  • Visual Basic 6
  • Visual Basic.NET
  • Visual Studio
  • XML

This list is incomplete, and will be amended as I continue. When articles are written, I will link them to keywords.