MAX(DateTime) less than xDateTime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MAX(DateTime) less than xDateTime

Author

Topic

mmcfar
Starting Member

8 Posts

Posted - 2009-09-15 : 14:09:57

I have a table called Times Which I would like to pull the
MAX TimeMark (one per UserID) less than a DateTime I pass.

Can't figure out where to put "AND TimeMark <= '9/15/2009 23:59:59'"

CREATE TABLE #testit (
SiteID int NOT NULL,
UserName nchar(10) NOT NULL,
UserID int NOT NULL,
InOut nchar(3) NOT NULL,
TimeMark datetime NOT NULL)

INSERT INTO #testit (SiteID, UserName, UserID, InOut, TimeMark)
SELECT '2', 'Bill', '1568', 'IN', '9/13/2009 9:00:00' UNION ALL
SELECT '2', 'Bill', '1568', 'OUT', '9/13/2009 17:00:00' UNION ALL
SELECT '1', 'Joe', '1567', 'IN', '9/14/2009 9:00:00' UNION ALL
SELECT '1', 'Joe', '1567', 'OUT', '9/14/2009 17:00:00' UNION ALL
SELECT '2', 'Bill', '1568', 'IN', '9/14/2009 9:00:00' UNION ALL
SELECT '2', 'Bill', '1568', 'OUT', '9/14/2009 17:00:00' UNION ALL
SELECT '1', 'Joe', '1567', 'IN', '9/27/2009 9:00:00' UNION ALL
SELECT '1', 'Joe', '1567', 'OUT', '9/27/2009 17:00:00'

SELECT SiteID, UserName, UserID, InOut, TimeMark
FROM #testit AS T
WHERE (TimeMark = (SELECT MAX(TimeMark) AS Expr1 FROM #testit WHERE (T.UserID = UserID))) AND (SiteID IN ('1', '2')) AND (InOut IN ('OUT'))

Currently returns

1, Joe, 1567, OUT, 9/27/2009 17:00:00
2, Bill, 1568, OUT, 9/14/2009 17:00:00

I would like it to return

1, Joe, 1567, OUT, 9/14/2009 17:00:00
2, Bill, 1568, OUT, 9/14/2009 17:00:00

SteveTR
Starting Member

7 Posts

Posted - 2009-09-15 : 16:26:34

Try the following:

SELECT T.SiteID, T.UserName, T.UserID, T.InOut, T.TimeMark
FROM #testit AS T
WHERE T.SiteID IN ('1', '2')
AND T.InOut IN ('OUT')
AND T.TimeMark =
(
SELECT MAX(T1.TimeMark)
FROM #testit T1
WHERE (T1.UserID = T.UserID)
AND (T1.TimeMark <= '9/15/2009 23:59:59')
)

Note the use of table aliases T and T1 to distinguish which instance of the table you are referring to.
- TR

mmcfar
Starting Member

8 Posts

Posted - 2009-09-15 : 17:36:48

That works great, Thanks. I've been struggling with it for a couple of days. Many, many thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources