How to create date buckets in T-SQL
Tags: sql, sqlserver
Published: 2020-01-01 15:03:59

---
title: How to create date buckets in T-SQL
published: true
date: 2019-10-28 14:00:47 UTC
tags: sql, sqlserver
canonical_url: https://am2.co/2019/10/how-to-create-date-buckets-in-t-sql/
---

[![](http://am2.co/wp-content/uploads/StockSnap_MHIAORM8Q6.jpg)](https://www.youtube.com/watch?v=zYY6Q4nRTS4)  
I was fiddling around with some data, looking at making a quick aging report. I probably should have reached for [Power BI Desktop](https://powerbi.microsoft.com/en-us/desktop/), but sometimes I can be stubborn, and I wanted to get my T-SQL to work–if nothing else to figure out how to make it work.

## My Data

My data was essentially a really long list of widgets, with dates–really simple & straightforward:

| Widget Name | Widget Date |
| --- | --- |
| Widget A | 2019-06-19 |
| Widget B | 2019-07-14 |
| Widget C | 2018-11-04 |
| … | … |
| Widget D | 2015-11-30 |

## My reporting requirements

I wanted to count widgets based on how old they were. The data spanned several years, so I wanted to be able to put them into 3 or 6 month buckets. Essentially, I wanted to use N-month sized buckets to make my report to look something like this:

| Widget Age | Widget Count |
| --- | --- |
| 0 – 6 Months Old | 8752 |
| 6 – 12 Months Old | 9836 |
| 12 – 18 Months old | 7852 |
| … | … |
| 60 – 66 Months old | 5872 |

## Let’s write the report

First, we need some test data we can fool around with. I’m going to create a temp table with one row per day, using the [Numbers table](https://github.com/amtwo/dba-database/blob/master/tables/dbo.Numbers.sql) from my [DBA Database](https://github.com/amtwo/dba-database/). For this test data, that will make the numbers per bucket pretty similar, but that makes for easy validation!

```sql
SELECT  WidgetName = 'Widget ' + LEFT(NEWID(),3),
        WidgetDate = CONVERT(DATE,DATEADD(DAY,-1*[Number],GETDATE()))
INTO #Widgets
FROM DBA.dbo.Numbers
WHERE [Number] < 75000;
```

Now, it’s pretty easy to calculate the age of these rows–we just use `DATEDIFF` to compute the number of days/weeks/months/years between the `WidgetDate` and today. But that doesn’t help me with my report. I could do a `GROUP BY MonthsAgo` to count per month… but I really wanted to put these into N-month buckets.

```sql
SELECT  WidgetDate,
        DaysOld     = DATEDIFF(DAY,WidgetDate,GETDATE()),
        MonthsOld   = DATEDIFF(MONTH,WidgetDate,GETDATE())
FROM #Widgets;
```

Next, I want to put those “Months Ago” numbers into buckets–and I want to be able to easily adjust the size of those buckets as I evaluate the data & work on how I want to present it. Each bucket has a “bottom end” and “top end” value–referred to as a floor & ceiling.

Here’s where my math nerd-iness comes out… There’s a little trick for “bucketizing” numbers (in this case, turning “Months” into “Month Buckets”):

- Take a number
- Divide it by your bucket size
- Round that number down to a whole number–We’ll call this the “divided number”
- Multiply the “divided number” by the bucket size–This is your bucket floor
- Add one to the “divided number” and then multiply _that_ by your bucket size–This is your bucket ceiling

Converting that into T-SQL, we can even save a step: Our `MonthsOld` column is always a whole number (ie, an `int`). If our bucket size is also an `int`, then dividing the two will always result in an `int`, which has been rounded down–this saves me from having to manually do any rounding! (Though, I could totally throw in a call to the `FLOOR()` function if I wanted to be explicit.)

Now I can convert that into T-SQL and continue to add on to our last query, and spot-check the results:

```sql
DECLARE @BucketSize int = 6;

SELECT  WidgetDate,
        DaysOld            = DATEDIFF(DAY,WidgetDate,GETDATE()),
        MonthsOld          = DATEDIFF(MONTH,WidgetDate,GETDATE()),
        MonthBucketFloor   = DATEDIFF(MONTH,WidgetDate,GETDATE()) / @BucketSize * @BucketSize,
        MonthBucketCeiling = (DATEDIFF(MONTH,WidgetDate,GETDATE()) / @BucketSize + 1) * @BucketSize
FROM #Widgets;
```

[![](http://am2.co/wp-content/uploads/MonthBuckets_1.png)](http://am2.co/2019/10/how-to-create-date-buckets-in-t-sql/monthbuckets_1/)

I know there’s someone else out there shouting “No, Andy! That formula is too complicated! There’s another way to do that with fewer steps!” Yes, there is another way. This option might be easier to understand why it works, but I find the _code_ harder to read.

- Take a number
- Perform a [modulo](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/modulo-transact-sql) with the bucket size as the divisor to get the remainder
- Subtract the remainder from the original number–This is your bucket floor
- Take your bucket floor and add your bucket size–This is your bucket ceiling

OK, so technically, that’s only 4 steps. But I don’t think that makes it any simpler. Here’s what this alternate version looks like when you convert it into T-SQL:

```sql
--Alternate to my prior query
DECLARE @BucketSize int = 6;

SELECT  WidgetDate,
        DaysOld            = DATEDIFF(DAY,WidgetDate,GETDATE()),
        MonthsOld          = DATEDIFF(MONTH,WidgetDate,GETDATE()),
        MonthBucketFloor   = DATEDIFF(MONTH,WidgetDate,GETDATE()) - (DATEDIFF(MONTH,WidgetDate,GETDATE()) % @BucketSize),
        MonthBucketCeiling = DATEDIFF(MONTH,WidgetDate,GETDATE()) - (DATEDIFF(MONTH,WidgetDate,GETDATE()) % @BucketSize) +  @BucketSize
FROM #Widgets;
```

I’m not going to use this version, but know that it’s 100% logically equivalent to the prior version. Maybe some of my fellow math nerds will slide into the comments with the mathematical proof that these two queries are logically equivalent.

My last step is simply to do some counts grouped by the buckets–and to convert the floor/ceiling numbers into a pretty label:

```sql
DECLARE @BucketSize int = 6;

WITH WidgetsWithAge AS (
    SELECT  WidgetDate,
            DaysOld            = DATEDIFF(DAY,WidgetDate,GETDATE()),
            MonthsOld          = DATEDIFF(MONTH,WidgetDate,GETDATE()),
            MonthBucketFloor   = FLOOR(DATEDIFF(MONTH,WidgetDate,GETDATE())/@BucketSize) * @BucketSize,
            MonthBucketCeiling = (FLOOR(DATEDIFF(MONTH,WidgetDate,GETDATE())/@BucketSize) + 1) * @BucketSize
    FROM #Widgets
)
SELECT  WidgetAge   = CONVERT(varchar(100), MonthBucketFloor) + ' - ' + CONVERT(varchar(100), MonthBucketCeiling) + ' Months Old',
        WidgetCount = COUNT(*)
FROM WidgetsWithAge
GROUP BY MonthBucketFloor, MonthBucketCeiling;
```

[![](http://am2.co/wp-content/uploads/MonthBuckets_2.png)](http://am2.co/2019/10/how-to-create-date-buckets-in-t-sql/monthbuckets_2/)

Boom! That’s it. That’s my report. And since I sneaked in that `@BucketSize` as a parameter, it’s pretty easy for me to adjust my bucket size for different data. I can easily switch that to a 36-month bucket with the flick of a keystroke:

```sql
DECLARE @BucketSize int = 36;

WITH WidgetsWithAge AS (
    SELECT  WidgetDate,
            DaysOld            = DATEDIFF(DAY,WidgetDate,GETDATE()),
            MonthsOld          = DATEDIFF(MONTH,WidgetDate,GETDATE()),
            MonthBucketFloor   = FLOOR(DATEDIFF(MONTH,WidgetDate,GETDATE())/@BucketSize) * @BucketSize,
            MonthBucketCeiling = (FLOOR(DATEDIFF(MONTH,WidgetDate,GETDATE())/@BucketSize) + 1) * @BucketSize
    FROM #Widgets
)
SELECT  WidgetAge   = CONVERT(varchar(100), MonthBucketFloor) + ' - ' + CONVERT(varchar(100), MonthBucketCeiling) + ' Months Old',
        WidgetCount = COUNT(*)
FROM WidgetsWithAge
GROUP BY MonthBucketFloor, MonthBucketCeiling;
```

[![](http://am2.co/wp-content/uploads/MonthBuckets_3.png)](http://am2.co/2019/10/how-to-create-date-buckets-in-t-sql/monthbuckets_3/)

The post [How to create date buckets in T-SQL](https://am2.co/2019/10/how-to-create-date-buckets-in-t-sql/) appeared first on [Andy M Mallon - AM²](https://am2.co).