Adapting Uri's DDL and sample data (thanks Uri):

CREATE TABLE Connections (userid INTEGER NOT NULL , starttimecon DATETIME
NOT NULL, endtimecon DATETIME NULL, CHECK (starttimecon<endtimecon), PRIMARY
KEY (userid,starttimecon))

INSERT INTO Connections
SELECT 1,'2000-06-10T10:00:00','2000-06-10T10:10:00' UNION ALL
SELECT 2,'2000-06-11T10:20:00',NULL UNION ALL
SELECT 3,'2000-06-12T10:23:00','2000-06-12T10:25:00' UNION ALL
SELECT 4,'2000-06-12T10:27:00',NULL UNION ALL
SELECT 4,'2000-06-10T10:27:00','2000-06-11T10:25:00'

Maximum number of simultaneous connections on a given day:

DECLARE @dt DATETIME
SET @dt= '20000611'

SELECT MAX(cnt)
FROM
(SELECT COUNT(*) AS cnt
FROM Connections AS C
JOIN
(SELECT COALESCE(starttimecon,CURRENT_TIMESTAMP)
FROM Connections
UNION ALL
SELECT COALESCE(endtimecon,CURRENT_TIMESTAMP)
FROM Connections) AS D(dt)
ON D.dt >= C.starttimecon AND D.dt <
COALESCE(C.endtimecon,CURRENT_TIMESTAMP)
WHERE dt >= @dt AND dt < DATEADD(DAY,1,@dt)
GROUP BY D.dt) AS T

--
David Portas
SQL Server MVP
--

Oops. That should have been UNION not UNION ALL.

--
David Portas
SQL Server MVP
--

Gee
drop table tblConnection

create table tblConnection
(
userid int not null ,
StartTimeCon datetime not null,
EndTimeCon datetime

)

insert into tblConnection(userid,StartTimeCon,EndTimeCon)values (1,'20000610
10:00','20000610 10:10')
insert into tblConnection(userid,StartTimeCon,EndTimeCon)values (2,'20000611
10:20',null)
insert into tblConnection(userid,StartTimeCon,EndTimeCon)values (3,'20000612
10:23','20000612 10:25')
insert into tblConnection(userid,StartTimeCon,EndTimeCon)values (4,'20000612
10:27',null)
insert into tblConnection(userid,StartTimeCon,EndTimeCon)values (4,'20000610
10:27','20000611 10:25')

declare @d datetime
set @d='2000-06-10'
select * from tblConnection
where EndTimeCon is not null
and convert(datetime,convert(varchar(10),EndTimeCon,126))>= @d
and convert(datetime,convert(varchar(10),EndTimeCon,126))<
dateadd(day,1,@d )

Note: using convert function with where condition will prevent from query
optimyzer to use an index.It is not problem to change the query but i don;t
know your business requiremnts. How does the search variable apply?





[quoted text, click to view]

Gee
I don't understand you.
Please change my DDL + post expected result.



[quoted text, click to view]

I have a table listed below where I store a record everytime a user connects
and another record everytime they disconnect. The ConnectDateTime is always
entered but the DisconnectDateTime is only in the record when they
disconnect.

Problem: I want a query to see what the connections peeked out for a given
day. (That is the maximum combined number of users connected at the same
time).

create table connections
(
idx int identity(1,1) not null,
UserId varchar(10) not null,
ConnectDateTime datetime not null,
DisconnectDateTime datetime null
)
GO

I am not worried about performance more about finding what the connections
are peeking at. Your query didn't work for what I am trying to do. Below
closer but is not but still to far out I want it down to a minute without
graps.

Change your sample data so the first record finishes at 10:22 and the result
should be 2 because User 1 and 2 were both connected at the same time.

SELECT COUNT(*),datepart(hour,StartTimeCon) FROM tblConnection
where EndTimeCon is not null
GROUP BY datepart(hour,StartTimeCon)



[quoted text, click to view]

[quoted text, click to view]

This provides a view to find the maximum number of simultaneous connections
for each date in the range [MIN(ConnectDateTime), CURRENT_TIMESTAMP].
There's another view that will also find all time periods for each date that
correspond to the maximum number of simultaneous connections for that date.

CREATE TABLE Connections
(
UserId VARCHAR(10) NOT NULL,
ConnectDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (ConnectDateTime <= CURRENT_TIMESTAMP),
DisconnectDateTime DATETIME NULL DEFAULT NULL,
CHECK (DisconnectDateTime > ConnectDateTime),
PRIMARY KEY (ConnectDateTime, UserId)
)

-- Thanks to Uri Dimant for sample data
INSERT INTO Connections (UserId, ConnectDateTime, DisconnectDateTime)
VALUES ('1', '20000610 10:00', '20000610 10:10')
INSERT INTO Connections (UserId, ConnectDateTime, DisconnectDateTime)
VALUES ('2', '20000611 10:20', NULL)
INSERT INTO Connections (UserId, ConnectDateTime, DisconnectDateTime)
VALUES ('3', '20000612 10:23', '20000612 10:25')
INSERT INTO Connections (UserId, ConnectDateTime, DisconnectDateTime)
VALUES ('4', '20000612 10:27', NULL)
INSERT INTO Connections (UserId, ConnectDateTime, DisconnectDateTime)
VALUES ('5', '20000610 10:27', '20000611 10:25')

-- The set of all connect and disconnect datetimes
CREATE VIEW ConnectionDateTimes (ConnectionDateTime)
AS
SELECT ConnectDateTime
FROM Connections
UNION
SELECT DisconnectDateTime
FROM Connections

-- Set of periods formed from consecutive connection datetimes
CREATE VIEW ConsecutiveConnectionDateTimes
(StartConnectionDateTime, EndConnectionDateTime)
AS
SELECT C.ConnectionDateTime, MIN(D.ConnectionDateTime)
FROM ConnectionDateTimes AS C
INNER JOIN
ConnectionDateTimes AS D
ON C.ConnectionDateTime IS NOT NULL AND
(D.ConnectionDateTime > C.ConnectionDateTime OR
D.ConnectionDateTime IS NULL)
GROUP BY C.ConnectionDateTime

-- Number of simultaneous connections per given time period
CREATE VIEW SimultaneousConnectionsPerPeriod
(StartConnectionDateTime, EndConnectionDateTime, ConnectionTotal)
AS
SELECT DT.StartConnectionDateTime, DT.EndConnectionDateTime, COUNT(*)
FROM ConsecutiveConnectionDateTimes AS DT
INNER JOIN
Connections AS C
ON DT.StartConnectionDateTime >= C.ConnectDateTime AND
(DT.EndConnectionDateTime <= C.DisconnectDateTime OR
C.DisconnectDateTime IS NULL)
GROUP BY DT.StartConnectionDateTime, DT.EndConnectionDateTime

CREATE VIEW Digits (d)
AS
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9

CREATE TABLE NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)

INSERT INTO NonnegativeIntegers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d + 1000 * Thousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands

CREATE VIEW SimultaneousConnectionsPerDate
(ConnectionDate, ConnectionStartDateTime, ConnectionEndDateTime,
ConnectionTotal)
AS
SELECT CD.ConnectionDate,
SC.StartConnectionDateTime,
SC.EndConnectionDateTime,
SC.ConnectionTotal
FROM (SELECT DT.FirstDate + I.n AS ConnectionDate,
DT.FirstDate + I.n + 1 AS NextDate
FROM (SELECT CAST(CONVERT(CHAR(8),
MIN(ConnectDateTime), 112)
AS
DATETIME) AS FirstDate,
CAST(CONVERT(CHAR(8),
CURRENT_TIMESTAMP, 112) AS
DATETIME) AS CurrentDate
FROM Connections) AS DT
INNER JOIN
NonnegativeIntegers AS I
ON I.n <= DATEDIFF(DAY, DT.FirstDate, DT.CurrentDate)) AS CD
INNER JOIN
SimultaneousConnectionsPerPeriod AS SC
ON (SC.StartConnectionDateTime >= CD.ConnectionDate AND
SC.StartConnectionDateTime < CD.NextDate) OR
(SC.EndConnectionDateTime > CD.ConnectionDate AND
SC.EndConnectionDateTime <= CD.NextDate) OR
(SC.StartConnectionDateTime <= CD.ConnectionDate AND
(SC.EndConnectionDateTime >= CD.NextDate OR
SC.EndConnectionDateTime IS NULL))

CREATE VIEW MaxSimultaneousConnectionsPerDate
(ConnectionDate, ConnectionTotal)
AS
SELECT ConnectionDate, MAX(ConnectionTotal)
FROM SimultaneousConnectionsPerDate
GROUP BY ConnectionDate

CREATE VIEW PeriodsWithMaxSimultaneousConnectionsPerDate
(ConnectionDate, ConnectionStartDateTime, ConnectionEndDateTime,
ConnectionTotal)
AS
SELECT SC.ConnectionDate,
SC.ConnectionStartDateTime, SC.ConnectionEndDateTime,
SC.ConnectionTotal
FROM SimultaneousConnectionsPerDate AS SC
INNER JOIN
MaxSimultaneousConnectionsPerDate AS MSC
ON SC.ConnectionDate = MSC.ConnectionDate AND
SC.ConnectionTotal = MSC.ConnectionTotal

-- To find the maximum number of simultaneous connections per date
SELECT ConnectionDate ConnectionTotal
FROM MaxSimultaneousConnectionsPerDate
ORDER BY ConnectionDate

ConnectionDate ConnectionTotal
2000-06-10 00:00:00.000 1
2000-06-11 00:00:00.000 2
2000-06-12 00:00:00.000 2
2000-06-13 00:00:00.000 2
....
2004-01-30 00:00:00.000 2

-- To find the periods per date that have the maximum number of
-- simultaneous connections
SELECT ConnectionDate, ConnectionStartDateTime, ConnectionEndDateTime,
ConnectionTotal
FROM PeriodsWithMaxSimultaneousConnectionsPerDate
ORDER BY ConnectionDate, ConnectionStartDateTime

ConnectionDate ConnectionStartDateTime ConnectionEndDateTime ConnectionTotal