MS SQL Server get count in 5 minute blocks

(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?

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.

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;

Select allOpen in new window

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, ........

??

(4hours/5 Minutes =) 80 columns

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

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

@Scott, I corrected that earlier :)

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

Select allOpen 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:

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

Select allOpen in new window

demo here: http://rextester.com/VJEZG50522

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

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 ...

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.

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

Select allOpen in new window

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.

Olaf's answer is the only one that extended to the pivot.

Thanks!