The topic for this month’s TSQL Tuesday, hosted by Allen White, is an invitation to share your tricks. Before I get into my trick to share, I wanted to mention that Allen is a pretty awesome speaker too. I got to see his session “Gather SQL Server Performance Data with Powershell” at the SQL Saturday in Columbus earlier this year. Allen is really excited about SQL Server and the cool things you can do with Powershell.
Now for my trick, I have a view that was created to help write more dynamic rolling sql queries and reports in my organization. I’m not sure who the original author of the script is, as it has been passed around and modified several times. I thought I would share it here in the hopes that it helps someone else someday. If you’re the original author or know who is, please let me know so I can give you due credit.
This view defines a bunch of different date parameters compared to the current date, including:
- TODAY_BEGIN
- TODAY_END
- YESTERDAY_BEGIN
- YESTERDAY_END
- DAY_BEFORE_YESTERDAY_BEGIN
- DAY_BEFORE_YESTERDAY_END
- SUNDAY_WEEK_BEGIN
- SUNDAY_WEEK_END
- MONDAY_WEEK_BEGIN
- MONDAY_WEEK_END
- PREVIOUS_SUNDAY_WEEK_BEGIN
- PREVIOUS_SUNDAY_WEEK_END
- PREVIOUS_MONDAY_WEEK_BEGIN
- PREVIOUS_MONDAY_WEEK_END
- MONTH_BEGIN
- MONTH_END
- YESTERDAYS_MONTH_BEGIN
- YESTERDAYS_MONTH_END
- PREVIOUS_MONTH_BEGIN
- PREVIOUS_MONTH_END
- SECOND_PREVIOUS_MONTH_BEGIN
- SECOND_PREVIOUS_MONTH_END
- THIRD_PREVIOUS_MONTH_BEGIN
- THIRD_PREVIOUS_MONTH_END
- FOURTH_PREVIOUS_MONTH_BEGIN
- FOURTH_PREVIOUS_MONTH_END
- TWELTH_PREVIOUS_MONTH_BEGIN
- TWELTH_PREVIOUS_MONTH_END
- PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN
- PREVIOUS_SIXTH_MONDAY_WEEK_END
- PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN
- PREVIOUS_SIXTH_SUNDAY_WEEK_END
- NEXT_MONTH_BEGIN
- NEXT_MONTH_END
You can take a quick look at the result returned from this view. After creating the view (script included below), run:
SELECT * FROM vw_date_ranges
With these columns defined, you can easily query a database table looking for rows based off of a date by cross joining this view and the adding the date columns to the where clause. For example, if you want to see all orders for “This Week”, you can run a query similar to:
SELECT SalesOrderID, OrderDate, SalesOrderNumber, PurchaseOrderNumber, CustomerID, TotalDue FROM Sales.SalesOrderHeader CROSS JOIN vw_date_ranges WHERE Sales.SalesOrderHeader.OrderDate > vw_date_ranges.SUNDAY_WEEK_BEGIN
Or if you want to see all orders for “Last Month”, you can run a query similar to:
SELECT SalesOrderID, OrderDate, SalesOrderNumber, PurchaseOrderNumber, CustomerID, TotalDue FROM Sales.SalesOrderHeader CROSS JOIN vw_date_ranges WHERE Sales.SalesOrderHeader.OrderDate >= vw_date_ranges.PREVIOUS_MONTH_BEGIN AND Sales.SalesOrderHeader.OrderDate < vw_date_ranges.MONTH_BEGIN
Using this method helps me keep my sql query clean, and provides a rolling date range on queries and reports.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vw_date_ranges] (TODAY_BEGIN, TODAY_END, YESTERDAY_BEGIN, YESTERDAY_END, DAY_BEFORE_YESTERDAY_BEGIN, DAY_BEFORE_YESTERDAY_END, SUNDAY_WEEK_BEGIN, SUNDAY_WEEK_END, MONDAY_WEEK_BEGIN, MONDAY_WEEK_END, PREVIOUS_SUNDAY_WEEK_BEGIN, PREVIOUS_SUNDAY_WEEK_END, PREVIOUS_MONDAY_WEEK_BEGIN, PREVIOUS_MONDAY_WEEK_END, MONTH_BEGIN, MONTH_END, YESTERDAYS_MONTH_BEGIN, YESTERDAYS_MONTH_END, PREVIOUS_MONTH_BEGIN, PREVIOUS_MONTH_END, SECOND_PREVIOUS_MONTH_BEGIN, SECOND_PREVIOUS_MONTH_END, THIRD_PREVIOUS_MONTH_BEGIN, THIRD_PREVIOUS_MONTH_END, FOURTH_PREVIOUS_MONTH_BEGIN, FOURTH_PREVIOUS_MONTH_END, TWELTH_PREVIOUS_MONTH_BEGIN, TWELTH_PREVIOUS_MONTH_END, PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN, PREVIOUS_SIXTH_MONDAY_WEEK_END, PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN, PREVIOUS_SIXTH_SUNDAY_WEEK_END,NEXT_MONTH_BEGIN,NEXT_MONTH_END) AS select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) TODAY_BEGIN, dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()+ 1 ), 0)) - .000011574 TODAY_END, DATEADD(dd, DATEDIFF(dd,0,getdate()), -1) YESTERDAY_BEGIN, dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate() ), 0)) - .000011574 YESTERDAY_END, DATEADD(dd, DATEDIFF(dd,0,getdate()), -2) DAY_BEFORE_YESTERDAY_BEGIN, dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate() ), -1)) - .000011574 DAY_BEFORE_YESTERDAY_END, -- Relative Dates - Weeks DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) SUNDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) + 6.999988426 SUNDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) MONDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 7.999988426 MONDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) PREVIOUS_SUNDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) + 6.999988426 PREVIOUS_SUNDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) PREVIOUS_MONDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) + 7.999988426 PREVIOUS_MONDAY_WEEK_END, -- Relative Dates - Months dateadd(mm,datediff(mm,0,getdate()),0) MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)) - .000011574 MONTH_END, dateadd(mm,datediff(mm,0,getdate() - 1),0) YESTERDAYS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() - 1 )+1, 0)) - .000011574 YESTERDAYS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-1, 0) PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)) - .000011574 PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-2, 0) SECOND_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-1, 0)) - .000011574 SECOND_PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-3, 0) THIRD_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-2, 0)) - .000011574 THIRD_PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-4, 0) FOURTH_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-3, 0)) - .000011574 FOURTH_PREVIOUS_MONTH_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )-12, 0) TWELTH_PREVIOUS_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-11, 0)) - .000011574 TWELTH_PREVIOUS_MONTH_END, -- Added 2/23/09 These will be used in a claim audit report that looks at data from 6 weeks prior. DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, 0) as PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, 0) + 7.999988426 as PREVIOUS_SIXTH_MONDAY_WEEK_END, DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, -1) as PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN, DATEADD(wk, DATEDIFF(wk,0,getdate() )-6, -1) + 6.999988426 as PREVIOUS_SIXTH_SUNDAY_WEEK_END, DATEADD(mm, DATEDIFF(mm,0,getdate() )+1, 0) NEXT_MONTH_BEGIN, dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() )+2, 0)) + .000011574 NEXT_MONTH_END