trying to count number of distinct minutes of record inserted by hour. If minutes are in sequential order then they should count as 1.

Question

Post reply

trying to count number of distinct minutes of record inserted by hour. If minutes are in sequential order then they should count as 1.

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
December 8, 2010 at 8:45 am

#244065

/* Problem: We have a service that runs n times an hour. i need to know how many times it runs.
One of the things it does is insert records into a table. In this instance that table
is #delivered. So i was going to count the number of minutes in the hour in the table. This
should give me the number of runtimes of the service. This works unless in a given runtime
the service is inserting so many records that the records inserted span more than 1 minute.
In this instance my process thinks it ran twice however it actually only ran once.
Does anyone know how to devise a way to count it as one if records span consecutive minutes?
I have under the insert the expected results.*/
create table #delivered
(
id int identity(1,1)
, offerId int
, dateAdded datetime
)
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:02')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:12')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:18')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:18')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:24')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:24')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:24')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:30')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:30')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:30')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:38')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:38')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:44')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:44')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:45')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:45')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:45')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:49')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:49')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:02')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:03')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:12')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:12')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:21')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:21')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:36')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:36')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:36')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:41')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:41')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (576,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:02')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:02')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:07')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:08')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:12')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:17')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:18')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:18')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:23')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:24')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:24')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:24')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:25')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:26')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:28')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:30')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:30')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:30')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:31')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:37')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:38')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:38')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:44')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:44')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:45')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:45')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:45')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:49')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:49')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 12:54')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:02')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:03')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:12')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:12')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:13')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:21')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:21')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:22')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:28')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:29')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:35')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:36')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:36')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:36')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:41')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:41')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
insert into #delivered (offerId, dateAdded) values (499,'12/7/10 13:42')
select *
from #delivered
/* should return. The hour and the number of runtimes. I know this may be confusing
so please feel free to ask for more specifics
hour | runtimes
12 10
13 6
*/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

You'll never get an accurate result from that table.

You could count all of the records that were during consecutive minutes, but what if it ran twice over 2 minutes?

E.G.

Run 1 Starts 15:55:55

Ends 15:58:02

Run 2 Starts 15:59:00

Ends 16:05:00

If you count consecutive minutes, you only get 1 run from that.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

Sorry guys. i need the offerid, the hour, and number of runtimes. Also it will never run twice for a given offer in separate consecutive minutes

/* should return. The offerid, hour and the number of runtimes. I know this may be confusing

so please feel free to ask for more specifics

offerid | hour | runtimes

576 | 12 | 10

576 | 13 | 6

499 | 12 | 10

499 | 13 | 6

*/

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 3

--you could count all of the records that were during consecutive minutes, but what if it ran twice over 2 minutes?

Maybe but what if a few times it didnt span consecutive minutes? As the example shows.

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

anyone interested in the solution here it is. The solution was by good ol Itzik Ben-Gan. It's called Islands and Gaps. And it deals specifically w/ this solution to this issue. This is my first poke. however i will condense it a bit and try to make it all run off the original #delivered table w/o bringing in the #final table. None the less below is the solution as well as the link to "Islands and Gaps". Pretty interesting stuff.

http://msdn.microsoft.com/en-us/library/aa175780(SQL.80).aspx

--drop table #fianl

select datepart(mi, dateadded) gapID,datepart(hh,dateadded) hr, offerid

into #fianl

from #delivered

where datepart(hh,dateadded)=12

select t.offerid

, t.hr

, count(t.startofgroup) cnt

from (

SELECT t1.offerid

, t1.hr hr

, t1.gapID AS startOfGroup

, MIN(t2.gapID) AS endOfGroup

FROM (SELECT offerid, hr, gapID

FROM #fianl tbl1

WHERE NOT EXISTS(SELECT *

FROM #fianl tbl2

WHERE tbl1.gapID - tbl2.gapID = 1

and tbl1.offerid = tbl2.offerid

and tbl1.hr = tbl2.hr)) t1

INNER JOIN (SELECT offerid,hr, gapID

FROM #fianl tbl1

WHERE NOT EXISTS(SELECT *

FROM #fianl tbl2

WHERE tbl2.gapID - tbl1.gapID = 1

and tbl1.offerid = tbl2.offerid

and tbl1.hr = tbl2.hr)) t2

ON t1.gapID <= t2.gapID

and t1.offerid = t2.offerid

and t1.hr = t2.hr

where t1.offerid=576

GROUP BY t1.offerid, t1.gapID , t1.hr

)t

group by t.offerid

, t.hr

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

--Why not update the count in a pre-allocated table with (start_time, end_time) ranges already filled in?

Not sure what u mean. You saying adjust the process to insert a row into another table specifying start and end time?

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 6

CELKO (12/8/2010)
set up and populate a table with the time slots defined by the start and end times, then update that table as you get data. It should be an in-place update which is fast.

No problem... let's see your code for that. Also please explain to the boss why you created a table that wastes have the space in empty rows and why it won't allow the duplicates that the machine keeps kicking out. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 7

@BaldingLoopMan,

This should do it compliments of some old but very reliable mag tape methods I learned in the 50's 😉 No need for an extra table, either.

WITH

cteGroupedDates AS

( --=== Find the unique dates and assign them to a group.

-- The group looks like a date but the date means nothing except that adjacent

-- dates will be a part of the same group.

SELECT OfferID,

UniqueDate = DateAdded,

DateGroup = DATEADD(mi, - ROW_NUMBER() OVER (ORDER BY OfferID, DateAdded), DateAdded),

Hour = DATEPART(hh,DateAdded)

FROM #Delivered

GROUP BY OfferID, DateAdded

)

,

ctePreAgg AS

( --=== This figures out the "islands" and a nice something extra

SELECT OfferID,

Hour,

-- MIN(UniqueDate),

-- MAX(UniqueDate),

ContinuousMinutes = DATEDIFF(mi,MIN(UniqueDate),MAX(UniqueDate))+1

FROM cteGroupedDates

GROUP BY OfferID, DateGroup, Hour

) --=== Do the final aggregation

SELECT OfferID,

Hour,

RunTimes = Count(*),

TotalDuration = SUM(ContinuousMinutes)

FROM ctePreAgg

GROUP BY OfferID, Hour

ORDER BY OfferID, Hour

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)