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

So...
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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Olaf DoschkeSoftware DeveloperCommented:
I put my pivot to http://rextester.com/KOXLW60100

The main part you need is past the last GO. I used Pauls data preparation, too.

Paul, I forked and yet still think I renamed your code fiddle. It's still your query. Is forking not working on that site?

Bye, Olaf.
0
 
PortletPaulfreelancerCommented:
(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?
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Larry Bristersr. DeveloperAuthor Commented:
PortletPaul

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

0
 
PortletPaulfreelancerCommented:
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, ........

??
0
 
Scott PletcherSenior DBACommented:
(4hours/5 Minutes =) 80 columns

I think that would be 48 columns: 12 per hour * 4 hours.
0
 
Larry Bristersr. DeveloperAuthor Commented:
PortletPaul,
 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
0
 
PortletPaulfreelancerCommented:
@Scott, I corrected that earlier :)
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
PortletPaulfreelancerCommented:
I got this far:
select
      CampaignID
    , CampaignStart
    , grp_5
    , count(CampaignID) grp_5_count
from (
        select
          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
      CampaignID
    , CampaignStart
    , grp_5

Open in new window

demo here: http://rextester.com/VJEZG50522
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey guys I am on the He road at the moment
I'll review later this evening
0
 
PortletPaulfreelancerCommented:
Olaf, My original is still there, so no problem. Cheers.
0
 
arnoldCommented:
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 ...
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
Larry Bristersr. DeveloperAuthor Commented:
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 )
SELECT *
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,
       t.*
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

0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
PortletPaulfreelancerCommented:
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

)
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.