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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
(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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
@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
PortletPaulEE Topic AdvisorCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.