CREATE TABLE [dbo].[ActivityTrend](
  [Date] datetime NOT NULL,
  [Year] [int] NOT NULL,
  [Quarter] [int] NOT NULL,
  [Month] [int] NOT NULL,
  [Week] [int] NOT NULL,
  [DayOfWeek] [int] NOT NULL,
  [Day] [int] NOT NULL,
  [Hour] [int] NOT NULL,
  [Count] [int] NULL
) ON [PRIMARY]

DECLARE @myDatetime datetime
SET @myDatetime = '2008-01-01 00:000:00'

WHILE (@myDatetime < '2009-01-01 00:000:00')
  BEGIN
    INSERT INTO ActivityTrend (
      [Date], [Year], [Quarter],[Month],
      [Week], [DayOfWeek],[Day],[Hour])
    SELECT @myDatetime, DATEPART(yyyy, @myDatetime), DATEPART(q, @myDatetime), DATEPART(mm, @myDatetime), 
      DATEPART(wk, @myDatetime), DATEPART(dw, @myDatetime), DATEPART(dd, @myDatetime), DATEPART(hh, @myDatetime)

    SET @myDatetime = DATEADD(hh, 1, @myDatetime)
  END

UPDATE c 
  SET c.[Count] = dt.[Count]
FROM ActivityTrend c
  INNER JOIN (
      SELECT b.[Date], [Count] = COUNT(*)
      FROM Activity a
        INNER JOIN ActivityTrend b
          ON b.[Date] BETWEEN a.StartDt AND a.EndDt
      GROUP BY b.[Date]) AS dt
    ON c.[Date] = dt.[Date]

SELECT * FROM ActivityTrend WHERE [Count] IS NOT NULL

SELECT '20080419 08:23', '20080419 12:30' UNION ALL	-- 8, 9, 10, 11, 12
SELECT '20080419 08:53', '20080419 11:21' UNION ALL	-- 8, 9, 10, 11
SELECT '20080419 08:59', '20080419 18:22' UNION ALL	-- 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18
SELECT '20080419 09:21', '20080419 16:22' UNION ALL	-- 9, 10, 11, 12, 13, 14, 15, 16
SELECT '20080419 11:20', '20080419 12:20' UNION ALL	-- 11, 12
SELECT '20080419 11:33', '20080419 13:22' UNION ALL	-- 11, 12, 13
SELECT '20080419 12:00', '20080419 16:11' UNION ALL	-- 12, 13, 14, 15, 16
SELECT '20080419 12:31', '20080419 17:10'		-- 12, 13, 14, 15, 16, 17

 Hour  Me  You
--  8	3    3
--  9	4    4
-- 10	4    4	
-- 11	6    5 <- Difference of 1
-- 12	7    6 <- Difference of 1
-- 13	5    3 <- Difference of 2
-- 14	4    3 <- Difference of 1
-- 15	4    4
-- 16	4    4
-- 17	2    2
-- 18	1    1

DECLARE	@Sample TABLE
	(
		StartDT DATETIME,
		EndDT DATETIME
	)

INSERT	@Sample
SELECT '20080419 08:23', '20080419 12:30' UNION ALL
SELECT '20080419 08:53', '20080419 11:21' UNION ALL
SELECT '20080419 08:59', '20080419 18:22' UNION ALL
SELECT '20080419 09:21', '20080419 16:22' UNION ALL
SELECT '20080419 11:20', '20080419 12:20' UNION ALL
SELECT '20080419 11:33', '20080419 13:22' UNION ALL
SELECT '20080419 12:00', '20080419 16:11' UNION ALL
SELECT '20080419 12:31', '20080419 17:10'

-- Old way
SELECT		DATEPART(QUARTER, theTime) AS theQuarter,
		DATEPART(WEEKDAY, theTime) AS theWeekday,
		DATEPART(HOUR, theTime) AS theHour,
		COUNT(*) AS Cnt
FROM		(
			SELECT		DATEADD(HOUR, v.Number, s.StartDT) AS theTime
			FROM		(
						SELECT	DATEADD(HOUR, DATEDIFF(HOUR, 0, StartDT), 0) AS StartDT,
							DATEDIFF(HOUR, StartDT, EndDT) AS Hrs
						FROM	@Sample
					) AS s
			INNER JOIN	master..spt_values AS v ON v.Type = 'P'
			WHERE		v.Number <= s.Hrs
		) AS d
GROUP BY	DATEPART(QUARTER, theTime),
		DATEPART(WEEKDAY, theTime),
		DATEPART(HOUR, theTime)

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		StartDT DATETIME,
		EndDT DATETIME
	)

INSERT	@Sample
SELECT '20080419 08:23', '20080419 12:30' UNION ALL
SELECT '20080419 08:53', '20080419 11:21' UNION ALL
SELECT '20080419 08:59', '20080419 18:22' UNION ALL
SELECT '20080419 09:21', '20080419 16:22' UNION ALL
SELECT '20080419 11:20', '20080419 12:20' UNION ALL
SELECT '20080419 11:33', '20080419 13:22' UNION ALL
SELECT '20080419 12:00', '20080419 16:11' UNION ALL
SELECT '20080419 12:31', '20080419 17:10'

-- Prepare staging table
DECLARE	@Stage TABLE
	(
		RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		FromTime DATETIME NOT NULL,
		ToTime DATETIME NOT NULL
	)
 
-- Populate staging table
INSERT		@Stage
		(
			FromTime,
			ToTime
		)
SELECT		u.theTime,
		u.theTime
FROM		@Sample AS s
UNPIVOT		(
			theTime
			FOR theCol IN (s.StartDT, s.EndDT)
		) AS u
GROUP BY	u.theTime
ORDER BY	u.theTime
 
-- Update with closest range
UPDATE		s
SET		s.ToTime = w.FromTime
FROM		@Stage AS s
INNER JOIN	@Stage AS w ON w.RecID = s.RecID + 1
 
-- Delete last time
DELETE
FROM	@Stage
WHERE	RecID = SCOPE_IDENTITY()
 
-- Display the result
SELECT		w.FromTime,
		w.ToTime,
		COUNT(*) AS Occurencies
FROM		@Sample AS s
INNER JOIN	@Stage AS w ON w.FromTime < s.EndDT
                        AND w.ToTime > s.StartDT
GROUP BY	w.FromTime,
		w.ToTime
HAVING		COUNT(*) > 1
ORDER BY	w.FromTime

SELECT	Occurencies
FROM	MyStatisticsTable
WHERE	@MyDate BETWEEN FromTime AND ToTime

quote:

---

Originally posted by DLTaylor

The problem I have (apart from this type of work being new and infrequent to me!) is that I don’t have anyone to bounce my ideas around with at work and if I had thought through I may have tried asking for the solution Lumbago provided.

---

quote:

---

Originally posted by DLTaylor

Saying that I have looked at your post Peso (on 02/11/2009 10:33) while it is a different approach that will be more accurate and I can see the benefits of your logic.

---

quote:

---

Originally posted by DLTaylor

I may look to add minutes to the Lumbago solution (though for 1 year that will give me 524160 rows of benchmark data! – which I will group; but may well slow my process overall…there is always much to consider)

---

SELECT		TOP 1 WITH TIES
		Occurencies
FROM		MyStatisticsTable
ORDER BY	Occurencies DESC