Log in  ::  Register  ::  Not logged in

# Set Up Grouping Intervals for Excel (SQL Spackle)

By Dwain Camps, 2012/07/31

"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

## Introduction

I don’t know about you folks out there but I’m frequently called upon to perform ad-hoc analyses in SQL.  It used to be that I’d just copy and paste the whole record set into Excel and do my analyses there.  Of late, as my ability with SQL has grown, I became more confident to do my analyses directly in SQL, grouping and whatnot. I then just copy the summarized result set to Excel, usually to graph the final results.  Clearly such things can also be done with SSRS, but Excel is easier for me.

One frequent requirement I have is to count the occurrences of some value and put that into a histogram.  I found myself repeatedly and laboriously creating CASE statements to define my intervals for grouping.  No more as I’ve found a better way.

## Some Test Data

Let’s start with some test data, courtesy of SQL MVP Jeff Moden from his article about generating test data, where I have added some random dates in addition to the INTs and FLOATs.

```DECLARE @NumberOfRows  INT
,@FStartValue  FLOAT
,@FEndValue    FLOAT
,@FRange       FLOAT
,@IStartValue  INT
,@IEndValue    INT
,@IRange       INT
,@DStartValue  DATETIME
,@DEndValue    DATETIME
,@DRange       INT

SELECT  @NumberOfRows = 10000
-- Random FLOATs on the closed interval {275, 850}
,@FStartValue = 275
,@FEndValue   = 850
,@FRange      = @FEndValue - @FStartValue
-- Random INTs starting at 41 and ending at 160
,@IStartValue = 41
,@IEndValue   = 160
,@IRange      = @IEndValue - @IStartValue + 1
-- Example: The 30 day interval ending before midnight today
--,@DStartValue = DATEADD(day, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
--,@DEndValue   = DATEADD(day, 30, DATEADD(day, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
--
-- The 60 day interval ending before midnight today
,@DStartValue = DATEADD(day, -60, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
,@DEndValue   = DATEADD(day, 60, @DStartValue)
,@DRange      = DATEDIFF(day, @DStartValue, @DEndValue)

DECLARE @MyTestTable TABLE (
RandomInteger INT
, RandomFloat FLOAT
, RandomDate DATETIME
)

INSERT INTO @MyTestTable
SELECT TOP (@NumberOfRows)
RandomInteger = ABS(CHECKSUM(NEWID()))  % @IRange + @IStartValue
,RandomFloat  = RAND(CHECKSUM(NEWID())) * @FRange + @FStartValue
,RandomDate   = RAND(CHECKSUM(NEWID())) * @DRange  + @DStartValue
FROM sys.all_columns a1
CROSS JOIN sys.all_columns a2```

We’ve created three columns of interest: 1) a random FLOAT, 2) a random INTeger and 3) a random DATETIME (10,000 of each), so that we can group by intervals the frequency of occurrences.  We also show an example of setting up the 30 day interval ending at midnight today (commented out).  We’ll use the 60 day interval for reporting because it is a more general case for setting up our intervals (we’ll use 2 days per interval).

## Charting Integer Values (Discrete Data)

First we’ll set up the intervals for our integer groupings.  In this case, we’ll choose 40 intervals equally divided into 3 integers each.  You need to examine the end points of your data closely, so you can set up appropriately spaced intervals.  In this case, we knew what they were but you could just as easily have selected MIN(RandomInteger) into @IStartValue and MAX(RandomInteger) into @IEndValue.

```DECLARE @NoIntervals INT
SET @NoIntervals = 40

;WITH SetUpIntervals AS (
-- Anchor: Initial, placeholder interval for totals
SELECT ID=0
, StartInterval = CAST(NULL AS INT)
, EndInterval   = CAST(NULL AS INT)
, Interval      = CAST('Total' AS VARCHAR(40))
UNION ALL
-- Anchor: Start interval taken from @IStartValue
SELECT ID=1
,StartInterval = @IStartValue
,EndInterval   = (@IStartValue - 1) + @IRange/@NoIntervals
,Interval      = CAST(CAST(@IStartValue AS VARCHAR) + ' to ' +
CAST((@IStartValue - 1) + @IRange/@NoIntervals AS VARCHAR) AS VARCHAR(40))
UNION ALL
-- Recursive: Next interval starts at 1 + last EndInterval
SELECT ID + 1
,1 + EndInterval
,EndInt
,CAST(CAST(1 + EndInterval AS VARCHAR) + ' to ' +
CAST(EndInt AS VARCHAR) AS VARCHAR(40))
FROM SetUpIntervals
-- CROSS APPLY to only calculate EndInterval once
CROSS APPLY (SELECT EndInterval + @IRange/@NoIntervals) x(EndInt)
WHERE ID > 0 AND ID < @NoIntervals)
--SELECT * FROM SetupIntervals
SELECT c.Interval
, MyIntFreq= CASE
WHEN MyIntFreq IS NULL THEN 0
ELSE MyIntFreq
END
FROM SetupIntervals c
LEFT JOIN (
-- Obtain frequencies by COUNTing RandomInteger from source table by Intervals
SELECT ID, MyIntFreq=COUNT(*)
FROM @MyTestTable
-- CROSS APPLY to put RandomIntegers into Intervals
CROSS APPLY (
SELECT ID
FROM SetUpIntervals
WHERE RandomInteger BETWEEN StartInterval AND EndInterval AND
StartInterval IS NOT NULL) a
-- Use ROLLUP to get a total row in the grouped results set
GROUP BY ID WITH ROLLUP) b
ON ISNULL(b.ID, 0) = c.ID
ORDER BY c.ID```

As you can see, we’ve used a recursive CTE to create our intervals, start and end, along with a text string that describes the interval, which will ultimately become the x-axis label in our Excel histogram.  We won’t use the total (from ROLLUP) in the histogram, but we may want to use it as a cross-check to make sure we didn’t lose anything in the interval mapping.  The LEFT JOIN, along with a check for COUNT(*) being NULL, ensures that any missing data gets filled in with zero counts.

Note also the commented out statement right after the SetUpIntervals CTE, which is useful if you need to tweak your intervals and you want to check them before running a data intensive grouping.

`--SELECT * FROM SetupIntervals`

Run the first script and copy/paste the results set (with headers if you prefer) into Excel and then with a little Excel wizardry you can produce a really pretty histogram of your summarized results to impress your colleagues and, more importantly, your boss.

## Charting Numbers with Decimals (Continuous Data)

Handling FLOATs (or MONEY or DECIMAL data types) requires a slightly different approach.  It is less important to match the number of intervals against MAX and MIN values in your data, but you still need to know what these are to establish the interval endpoints.  You will need to do a little auspicious rounding and a little fudging to make sure you don’t drop any values from the last interval.

```SELECT @FStartValue=MIN(RandomFloat), @FEndValue=MAX(RandomFloat)
FROM @MyTestTable

;WITH SetUpIntervals AS (
-- Anchor: Initial, placeholder interval for totals
SELECT ID=0
,StartInterval = CAST(NULL AS INT)
,EndInterval   = CAST(NULL AS INT)
,Interval      = CAST('Total' AS VARCHAR(40))
UNION ALL
-- Anchor: Start interval taken from @FStartValue
SELECT ID=1
,StartInterval = StartInt
,EndInterval   = EndInt
,Interval=CAST(CAST(StartInt AS VARCHAR(10)) + ' <= x < ' +
CAST(EndInt AS VARCHAR(10)) AS VARCHAR(40))
FROM (
SELECT StartInt=CAST(FLOOR(@FStartValue) AS FLOAT)
,EndInt=ROUND(FLOOR(@FStartValue) + CAST(@FRange AS FLOAT)/@NoIntervals, 2)) x
UNION ALL
-- Recursive: Next interval starts at last EndInterval
SELECT ID + 1
,EndInterval
,EndInt
,CAST(CAST(EndInterval AS VARCHAR(10)) + ' <= x <' +
CASE ID WHEN @NoIntervals - 1 THEN '= ' ELSE ' ' END +
CAST(EndInt AS VARCHAR(10)) AS VARCHAR(40))
FROM SetUpIntervals
-- CROSS APPLY to only calculate EndInterval once (fudging endpoint on last interval)
CROSS APPLY (
SELECT CASE ID WHEN @NoIntervals - 1 THEN CAST(CEILING(@FEndValue) AS FLOAT)
ELSE ROUND(EndInterval + CAST(@FRange AS FLOAT)/@NoIntervals, 2) END) x(EndInt)
WHERE ID > 0 AND ID < @NoIntervals)
--SELECT * FROM SetupIntervals
SELECT c.Interval
, MyFloatFreq= CASE
WHEN MyFloatFreq IS NULL THEN 0
ELSE MyFloatFreq
END
FROM SetupIntervals c
LEFT JOIN (
-- Obtain frequencies by COUNTing RandomFloat from source table by Intervals
SELECT ID, MyFloatFreq=COUNT(*)
FROM @MyTestTable
-- CROSS APPLY to put RandomFloats into Intervals
CROSS APPLY (
SELECT ID
FROM SetUpIntervals
WHERE RandomFloat >= StartInterval AND
-- Fudge the last interval so it is inclusive (<=)
((ID = @NoIntervals AND RandomFloat <= EndInterval) OR
(ID <> @NoIntervals AND RandomFloat < EndInterval))) a
-- Use ROLLUP to get a total row in the grouped results set
GROUP BY ID WITH ROLLUP) b
ON ISNULL(b.ID, 0) = c.ID
ORDER BY c.ID```

Voila!  After a quick copy/paste into Excel we have another really pretty histogram!

## Charting Over Date Periods (Continuous Data)

Handling dates isn’t much different than the above but it does combine some of the features of both.  We’ll define 30 intervals this time (2 days per interval).  The main difference from FLOAT is there’s no need to fudge the endpoint (we’ll want it to be less than the end date of the final interval) and that the CASTs are to DATETIME, with CONVERT used to get the date into a format we like for the labels (mm/dd/yyyy).

```SET @NoIntervals = 30

;WITH SetUpIntervals AS (
-- Anchor: Initial, placeholder interval for totals
SELECT ID=0
,StartInterval = CAST(NULL AS DATETIME)
,EndInterval   = CAST(NULL AS DATETIME)
,Interval      = CAST('Total' AS VARCHAR(40))
UNION ALL
-- Anchor: Start interval taken from @DStartValue
SELECT ID=1
,StartInterval = @DStartValue
,EndInterval   = 1 + (@DStartValue - 1) + @DRange/@NoIntervals
,Interval=CAST(CONVERT(VARCHAR(10), @DStartValue,101) + ' - ' +
CONVERT(VARCHAR(10), (@DStartValue - 1) + @DRange/@NoIntervals, 101) AS VARCHAR(40))
UNION ALL
-- Recursive: Next interval starts at last EndInterval
SELECT ID + 1
,EndInterval
,EndInterval + @DRange/@NoIntervals
,CAST(CONVERT(VARCHAR(10), EndInterval, 101) + ' - ' +
CONVERT(VARCHAR(10), EndInterval + @DRange/@NoIntervals - 1, 101) AS VARCHAR(40))
FROM SetUpIntervals
WHERE ID > 0 AND ID < @NoIntervals)
--SELECT * FROM SetupIntervals
SELECT c.Interval, MyDateFreq=CASE WHEN MyDateFreq IS NULL THEN 0 ELSE MyDateFreq END
FROM SetupIntervals c
LEFT JOIN (
-- Obtain frequencies by COUNTing RandomDate from source table by Intervals
SELECT ID, MyDateFreq=COUNT(*)
FROM @MyTestTable
-- CROSS APPLY to put RandomDate into Intervals
CROSS APPLY (
SELECT ID
FROM SetUpIntervals
WHERE RandomDate >= StartInterval AND RandomDate < EndInterval) a
-- Use ROLLUP to get a total row in the grouped results set
GROUP BY ID WITH ROLLUP) b
ON ISNULL(b.ID, 0) = c.ID
ORDER BY c.ID```

Once again, pasting our results into Excel we have a nicely formatted histogram over the desired date range.

## Conclusion

I have successfully used these scripts across many examples including:

• Frequency charts of batch job run times across intervals of run times in seconds or minutes.
• Frequency charts showing on-demand batch jobs, either counted by interval or over a specific time period charting their run times by interval.
• Transaction counts charted over selected time periods.
• You can also use it for SUMs instead of COUNTs, and I have done this to calculate invoiced revenue over specific time periods.

I have attached an Excel workbook that provides some templates for your charting endeavors.

We hope that if you do ad-hoc reporting against your SQL databases, you’ll consider these scripts a useful addition to your toolbox.  Has this SQL Spackle filled a crack?

### Resources:

Frequency Histograms.xlsx
Total article views: 5179 | Views in the last 30 days: 6

Related Articles
SCRIPT

### Aggregating Intervals

create intervals from effective dates and aggregate like and adjacent intervals

FORUM

### Time Intervals

Who is logged on in 15 minute intervals

FORUM

### Time intervals

Creating time intervals on overlapping events

FORUM

### sql time intervals problem

sql time intervals problem

FORUM

### Set Up Grouping Intervals for Excel (SQL Spackle)

Comments posted to this topic are about the item [B]Set Up Grouping Intervals for Excel (SQL Spackl...

Tags
 excel histogram chart t-sql

## Join the most active online SQL Server Community

### SQL knowledge, delivered daily, free:

Email address:

#### You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platformâ€¦ And itâ€™s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

### Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
 Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.