Avatar of Larry Brister
Larry Brister
Flag for United States of America asked on

MS SQL Server get count in 5 minute blocks

I have a table that I need to count the transactions in each 5 minute block

Columns are CampaignID, IndividualID and DateSent

I need  output like this...

CampaignID   Started                          +5     +10    +15    etc....   Total
123                   2017-10-01 5PM         20      21        19    .....       123  
123                   2017-10-21 3PM         13      2          33    .....       225
Microsoft SQL ServerRemote Access

Avatar of undefined
Last Comment
Larry Brister

8/22/2022 - Mon

(24*60)/5 = 288

Do you want 288 columns?

Is there a "start hour" (e.g. the office opens at 08:00) or finish hour?(e.g. the office closes at 18:00)
could "outliers" go into  single columns outside the main hours?

Can you provide a few sample rows?
Olaf Doschke

Is "Started" the earliest "DateSent" or is that an extra column of campaigns, perhaps? Does "IndividualID" matter in any way for the count?

Overall you want all DateSent grouped into 5-minute intervals and that overall pivoted into the 5,10,15,... columns. I'm not sure how far you'll get with that concept. 1 day would already have 288 time-interval columns.

So are you sure? How long are campaigns running?

Bye, Olaf.
Larry Brister


Each Campoaign will have a "startDateTime" and an "EndDateTime"

Generally speaking it will span < three total hours

I have a "solution" by the hour... I just need to always have the columns orderd by StartDateTime left to right

This is what I have now... and it just "feels" clunky.

SELECT MAX(DatePivot.CampaignID) CampaignID ,
       MAX(DatePivot.[Date]) [Date] ,
       MAX(DatePivot.[0]) [0] ,
       MAX(DatePivot.[1]) [1] ,
       MAX(DatePivot.[2]) [2] ,
       MAX(DatePivot.[3]) [3] ,
       MAX(DatePivot.[4]) [4] ,
       MAX(DatePivot.[5]) [5] ,
       MAX(DatePivot.[6]) [6] ,
       MAX(DatePivot.[7]) [7] ,
       MAX(DatePivot.[8]) [8] ,
       MAX(DatePivot.[9]) [9] ,
       MAX(DatePivot.[10]) [10] ,
       MAX(DatePivot.[11]) [11] ,
       MAX(DatePivot.[12]) [12] ,
       MAX(DatePivot.[13]) [13] ,
       MAX(DatePivot.[14]) [14] ,
       MAX(DatePivot.[15]) [15] ,
       MAX(DatePivot.[16]) [16] ,
       MAX(DatePivot.[17]) [17] ,
       MAX(DatePivot.[18]) [18] ,
       MAX(DatePivot.[19]) [19] ,
       MAX(DatePivot.[19]) [19] ,
       MAX(DatePivot.[19]) [19] ,
       MAX(DatePivot.[20]) [20] ,
       MAX(DatePivot.[21]) [21] ,
       MAX(DatePivot.[22]) [22] ,
       MAX(DatePivot.[23]) [23]
FROM   (   SELECT   CampaignID ,
                    CAST(DateSent AS DATE) [Date] ,
                    DATEPART(HOUR, DateSent) [Hour] ,
                    COUNT(1) [Email Count]
           FROM     EmailAddresses WITH ( NOLOCK )
           WHERE    DateSent IS NOT NULL
                    AND CampaignID = 16703
           GROUP BY CampaignID ,
                    CAST(DateSent AS DATE) ,
                    DATEPART(HOUR, DateSent)) AS HourlySalesData
PIVOT (   SUM([Email Count])
          FOR [Hour] IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23] )) AS DatePivot;

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Thanks, this helps, but now you just want (say)  (4hours/5 Minutes =) 48 columns regardless of hour?
Measured as cumulative minutes from a start point?

5, 10, 15, ....... 200, 205, ........

Scott Pletcher

(4hours/5 Minutes =) 80 columns

I think that would be 48 columns: 12 per hour * 4 hours.
Larry Brister

 Yes... that is right.

It woll ALWAYS be one line of data because I will actually feed in @CampaignID

What we are trying to do is get a sense of send "bottlenecks" on specific campaigns.

Sometimes its the network
Sometimes its the Email Server
Sometimes its because the customer has inadvertently created an email with a body that is 1 MB that they're sending to 50,000 people.

I just need to get a sense of that
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

@Scott, I corrected that earlier :)
Olaf Doschke

You can do a pivoting, that's saving lot of writing even using a static column list.

With Starts as (Select CampaignID, Min(DateSent) as [Started] From YOURTABLE Group by CampaignID)

Select * From
(Select d.CampaignID,
'+'+Cast(DateDiff(minute,s.Started,d.DateSent)/5*5+5 as VarChar(3)) as mins
From YOURTABLE d inner join Starts s on d.CampaignID = s.CampaignID
Where d.CampaignID =  @CampaignID
) x
PIVOT( count(mins) FOR mins IN ([+5],[+10],[+15],[+20],...,[+240])) AS PVTTable

Open in new window

Notice, since you specified the first column "+5", this will be minutes 0..5, etc, so it goes up to [+240].

Remove the +5 from /5*5+5 and column names would become +0,+5,...,+235

Bye, Olaf.

I got this far:
    , CampaignStart
    , grp_5
    , count(CampaignID) grp_5_count
from (
          CampaignID, min(DateSent) over(partition by CampaignID) CampaignStart
        , datediff(minute,min(DateSent) over(partition by CampaignID), DateSent) / 5 grp_5
        from myTable
    ) d
group by
    , CampaignStart
    , grp_5

Open in new window

demo here: http://rextester.com/VJEZG50522
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Larry Brister

Hey guys I am on the He road at the moment
I'll review later this evening
Olaf Doschke

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Olaf, My original is still there, so no problem. Cheers.

Echo Olaf's suggestion to use the minute portion of the column, then int(/5)*5
The example relies on SQL since integers are provided to have the result as integer
Or using datepart and the value is evaluated in a range ...
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Olaf Doschke

arnold, it's good to mention, yes. SQL Server remains in the domain of integers, if you divide by 5, so int/5*5 is not an operation resulting in int, it moves all values 0-4 to 0, 5-9 will become 5, etc. so it pulls together a group of 5 ints to one and thus groups the data. If you wonder why I do this "stupid" calculation.

Bye, Olaf.
Larry Brister

Hey guys,
 I used Olafs post D: 42371360

Here is my final solution... I insert to a temp table...
Then join to my main table to get the rest of the information I need.

Just a little confused...
I THINK??? Olafs is the best answer but am open to everyones comments first.

USE EmailEveryware;

IF OBJECT_ID('tempdb..#tmpPivot') IS NOT NULL
    DROP TABLE #tmpPivot;

;WITH Starts
AS ( SELECT   CampaignID ,
              MIN(DateSent) AS [Started],
			  MAX(DateSent) AS [Ended]
     FROM     EmailAddresses WITH ( NOLOCK )
     WHERE    DateSent IS NOT NULL
              AND CampaignID > 16702
     GROUP BY CampaignID )
INTO #tmpPivot
FROM   (   SELECT d.CampaignID ,
					CONVERT(VARCHAR(10), [Started], 101) + RIGHT(CONVERT(VARCHAR(32), [Started], 100), 8) [Started],
					CONVERT(VARCHAR(10), [Ended], 101) + RIGHT(CONVERT(VARCHAR(32), [Ended], 100), 8) [Ended],
                  '+' + CAST(DATEDIFF(MINUTE, s.Started, d.DateSent) / 5 * 5 + 5 AS VARCHAR(3)) AS mins
           FROM   EmailAddresses d
                  INNER JOIN Starts s ON d.CampaignID = s.CampaignID ) x
PIVOT (   COUNT(mins)
          FOR mins IN ( [+5], [+10], [+15], [+20], [+25], [+30], [+35], [+40], [+45], [+50], [+55], [+60], 
						[+65], [+70], [+75], [+80], [+85], [+90], [+95], [+100], [+105], [+110], [+115], [+120], 
						[+125], [+130], [+135], [+140], [+145], [+150], [+155], [+160], [+165], [+170], [+175], [+180],
						[+185] , [+190] ,[+195], [+200], [+205], [+210], [+215], [+220], [+225], [+230], [+235], [+240], 
						[+245], [+250], [+255], [+260], [+265], [+270], [+275], [+280], [+285], [+290], [+295], [+300], 
						[+305], [+310], [+315], [+320], [+325], [+330], [+335], [+340], [+345], [+350], [+355] ,[+360] ,
						[+365], [+370], [+375], [+380], [+385], [+390], [+395], [+400], [+405], [+410], [+415], [+420], 
						[+425], [+430], [+435], [+440], [+445], [+450], [+455], [+460], [+465], [+470], [+475], [+480], 
						[+485], [+490], [+495] )) AS PVTTable;

SELECT s.Name SalesSite,
	   c.[Description] ,
       c.Recipients ,
       c.EmailRead ,c.BusinessID,
FROM   Campaigns c
       JOIN #tmpPivot t ON c.CampaignID = t.CampaignID
	   JOIN Everyware.dbo.SalesSItes s ON c.SalesSiteID = s.SalesSiteID

Open in new window

Olaf Doschke

I mentioned dynamic pivoting, which mainly means computing the list of values=field names instead of manually writing them, but even for these 48 columns while it was cumbersome, it was still simple enough and much less than extending what you had with all the MAX() expressions.

If you want to dig deeper into that topic you might want to read http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

The transposing of rows to columns could also be done at the client side, it doesn't need to be done by SQL-Server.

And maybe the most important for your case, since you say you have a start date for the campaigns, you wouldn't need the CTE computing them from the MIN(DateSent) and instead of joining Starts would join your main campaigns table and use its start date column as reference date time, so DATEDIFF(MINUTE, s.Started, d.DateSent) becomes DATEDIFF(MINUTE, campaign.StarteDate, d.DateSent) or something along that lines.

Otherwise: It gives you, what you need and it's quite easy to extend, as long as you stay with 5-minute intervals. It's also easy to go for 10 or 15-minute intervals and drop some columns, but of course, changing to an arbitrary interval length would make the dynamic approach more important to get a more flexible pivoting.

Bye, Olaf.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Olaf's answer is the only one that extended to the pivot.
I stopped once I saw that answer as it was similar to where I was headed in any case.
The only significant difference was that I used a window function to arrive at the minimum date instead of a joined subquery

datediff(minute,min(DateSent) over(partition by CampaignID), DateSent) / 5 grp_5

Larry Brister