SELECT
FROM
(
SELECT Customer,DATEADD(ms,DATEDIFF(mi,0,LogTime)/@intervalLength*@intervalLength,0) As SlotTime, COUNT(Event) AS EvtCnt
FROM Table
WHERE Time BETWEEN @startTime AND @stopTime
GROUP BY Customer,DATEADD(ms,DATEDIFF(mi,0,LogTime)/@intervalLength*@intervalLength,0)
)t
GROUP BY Customer
HAVING SUM(CASE WHEN EvtCnt > = @eventCount THEN 1 ELSE 0 END) >0

quote:

---

Originally posted by mercury529

Thank you very much for the reply visakh16. I appreciate you taking the time to help me out. I had a few questions if you can help to clarify for me.

1.) You specify LogTime and Time. Were those meant to be the same and represent the "Time' field of an event? I have treated it as such when I tested your code.
it was a typo.they are the same

2.) Is Event supposed to correlate to a specific field in the Events table that I have listed? I have temporarily replaced it with * when I tested your code.
event represent any field that gives information on events for a customer
3.) I tried to run your code and I get a syntax error. I removed what appears to be the extraneous 't' after the parenthesis, but it still occurs. The error is "Error 156: Incorrect syntax near the keyword 'FROM'. Incorrect syntax near the keyword 'GROUP'." If I try running only using the inner SELECT, it works properly.
I just gave a stub to work with. in relaity you need to put actual columns there

SELECT Customer
FROM
(
SELECT Customer,DATEADD(ms,DATEDIFF(mi,0,LogTime)/@intervalLength*@intervalLength,0) As SlotTime, COUNT(Event) AS EvtCnt
FROM Table
WHERE LogTime BETWEEN @startTime AND @stopTime
GROUP BY Customer,DATEADD(ms,DATEDIFF(mi,0,LogTime)/@intervalLength*@intervalLength,0)
)t
GROUP BY Customer
HAVING SUM(CASE WHEN EvtCnt > = @eventCount THEN 1 ELSE 0 END) >0

4.) I am trying to wrap my head around what it is doing. The part that is confusing me at the moment is:

DATEDIFF(mi,0,LogTime)/@intervalLength*@intervalLength

What is this code calculating? It appears to be taking the number of minutes that occur between a 0 value datetime and the time of my event. I think the intention after that is to divide by @intervalLength squared, although won't this divide by @intervalLength and then multiply by @intervalLength effectively multiplying by 1 due to operator precedence?
its basically grouping your event time onto slot based on @intervalLength
5.) What does adding the value in 4) to a 0 time value do?
you will get logtime corrected to nearest slot
Again, thank you very much for your help.

---

quote:

---

Originally posted by mercury529

CREATE PROCEDURE dbo.GetCustomersWithHighDensityOfEvents
(
@nEventsInInterval INT,
@nSecondsInInterval INT,
)
AS

SET NOCOUNT ON

SELECT IDENTITY(Int, 1, 1) AS RowNumber,
EventTime,
Customer
INTO #tableEventsWithRowNumber
FROM dbo.Events
WHERE EventTime BETWEEN @StartTime AND @StopTime
ORDER BY Customer, EventTime

create unique clustered index ucx_swepeso on #tableEventsWithRowNumber (customer, rownumber)

SELECT DISTINCT tableLowerBound.Customer
FROM
(
SELECT RowNumber,
EventTime,
Customer
FROM #tableEventsWithRowNumber
) as tableLowerBound
INNER JOIN
(
SELECT RowNumber,
EventTime,
Customer
FROM #tableEventsWithRowNumber
) as tableUpperBound
ON tableLowerBound.rowNumber = tableUpperBound.rowNumber - @nEventsInInterval + 1
AND tableLowerBound.customer = tableUpperBound.customer
WHERE DATEDIFF(millisecond, tableLowerBound.EventTime, tableUpperBound.EventTime) <= 1000 * @nSecondsInInterval

DROP TABLE #tableEventsWithRowNumber

GO

---

SELECT		e.Customer
FROM		dbo.[Events] AS e
CROSS APPLY	(
		SELECT TOP(1)	x.[Time]
		FROM		dbo.[Events] AS x
		WHERE		x.Customer = e.Customer
				AND x.[Time] >= e.[Time]
				AND x.[Time] <= DATEADD(MILLISECOND, @IntervalLength, e.[Time])
				AND x.[Time] <= @StopTime
		) AS f
WHERE		e.[Time] BETWEEN @StartTime AND @StopTime
GROUP BY	e.Customer
HAVING		COUNT(*) >= @EventCount