Calculating Week Buckets in SQL

I needed to be able to create static week buckets within a single month to be able to group some fact measures by time period. The below accepts a date and then determines the buckets for that month. This will also correctly handle leap years as well. This can be used to join to fact tables for grouping into week buckets.

DECLARE @date DATE = '2020-02-12'
;

WITH weekcalendar AS (

SELECT *
FROM (
         SELECT 1 as WeekNum, DATEFROMPARTS(YEAR(@date),MONTH(@date),1) AS WeekStartDate, DATEFROMPARTS(YEAR(@date),MONTH(@date),7) AS WeekEndDate
UNION SELECT 2 as WeekNum, DATEFROMPARTS(YEAR(@date),MONTH(@date),8) AS WeekStartDate, DATEFROMPARTS(YEAR(@date),MONTH(@date),14) AS WeekEndDate
UNION SELECT 3 as WeekNum, DATEFROMPARTS(YEAR(@date),MONTH(@date),15) AS WeekStartDate, DATEFROMPARTS(YEAR(@date),MONTH(@date),21) AS WeekEndDate
UNION SELECT 4 as WeekNum, DATEFROMPARTS(YEAR(@date),MONTH(@date),22) AS WeekStartDate, DATEFROMPARTS(YEAR(@date),MONTH(@date),28) AS WeekEndDate
UNION SELECT 5 as WeekNum, CASE WHEN DAY(EOMONTH(@date)) <= 28 THEN NULL
                             ELSE DATEFROMPARTS(YEAR(@date),MONTH(@date),29)
                             END AS WeekStartDate
                     ,CASE WHEN DAY(EOMONTH(@date)) > 28 AND DAY(EOMONTH(@date)) = 29 THEN DATEFROMPARTS(YEAR(@date),MONTH(@date),29)
                             WHEN DAY(EOMONTH(@date)) > 28 AND DAY(EOMONTH(@date)) <> 29 THEN DATEFROMPARTS(YEAR(@date),MONTH(@date),DAY(EOMONTH(@date)))
                             END AS WeekEndDate
) q
WHERE [q].[WeekStartDate] IS NOT NULL
)

select * from weekcalendar

Output:


Comments

Post a Comment

Popular posts from this blog

Continuously Monitor Folder for New Files with PowerShell as a WindowsService

Recently encountered an old-school application that used a cobbled-together external service to generate reports and deliver to end-users in PDF format. Unfortunately, this service didn't have any error handling other than dropping the intended source content into a flat file in an error directory on the local disk. No monitoring, no notification, just fail and pretend it never happened. As these reports are critical to end users, I needed a way to monitor this directory for any new files and notify someone so that the report could be manually re-generated. After some initial Googling, I decided a quick PowerShell script would be the easiest method to continuously monitor the directory and fire off a notification when a new file is found. I'm admittedly an extreme PowerShell newbie, so I'm sure there are better, cleaner ways to do this, but this works nicely and is stable enough for now until my legacy system is retired in a few months. The below PowerShell script will inst
Read more

Unprotect All Excel Sheets for All Workbooks in a Directory

This Excel macro came in very handy when I had a set of workbooks in a folder (and subfolders) that all needed unprotected before running another macro to combine all of the workbooks into a single file. Be sure to update the bolded variables below.  Source . Const cStartFolder = " C:\Documents and Settings\UserName\My Documents\Spreadsheets " 'no slash at end Const cFileFilter = " *.xlsx " Const cPassword = " CleverPassword " 'use empty quotes if blank Sub UnprotectAllWorksheets() Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet ExtractFolder cStartFolder, arr() On Error Resume Next j = -1: j = UBound(arr) On Error GoTo 0 For i = 0 To j Set wkb = Workbooks.Open(arr(i), False) For Each wks In wkb.Worksheets wks.Unprotect cPassword Next wkb.Save wkb.Close Next End Sub Sub ExtractFolder(Folder As String, arr() As String) Dim i As Long, objF
Read more

ChromeOS Flow by Hexxeh Install to HDD

I finally decided to pull out the netbook again and check out the ChromeOS release of Flow by Hexxeh . I got the image downloaded, untar'd and imaged to the thumbdrive. Now comes the part when I want to actually install it to the solid-state disk in my Asus EeePC, right? Well, online sources said to just run /usr/sbin/chromeos-install and it would be taken care of. It kept erroring with "Attempt to install to a removable device: /dev/sda". Of course, this is my thumbdrive so that would make sense. Since it took me some digging to figure this one out, I thought I'd throw up the solution here to make it a little more visible on the Interwebs. Instead, run this command: /usr/sbin/chromeos-install /dev/sdb This should work if your internal device is at least 8gb. Unfortunately for me, mine is 4gb and my source media is 2gb. Instead, I ended up just copying the entire live image from my thumbdrive to the hard drive. Whatever works, right? Run this command instead of the ab
Read more