Select most recent date time

6 Jan 2006 7:42 PM

Terri

I am passing a datetime parameter to a select.

DECLARE @TestDateTime DateTime

SET @TestDateTime = '20050110'

SELECT * FROM Table1
WHERE TestDateTime <= @TestDateTime

There may not be a row with a datetime that matchs the parameter, in which
case I want to select the next previous rows. I can't use top with an ORDER
BY since the number of rows with a particular date will vary. Given the
sample data and a parameter of '20050110' I want to select the 4 rows with
the '20050105' datetime.

Thanks

CREATE TABLE [dbo].[Table1] (
[TestDateTime] [datetime] NULL ,
[Class] [char](1) NULL
) ON [PRIMARY]
GO

INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','A')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','E')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','G')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','A')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','C')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','D')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','D')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','B')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','A')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','A')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','B')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','C')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','E')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','F')
INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','G')

6 Jan 2006 7:51 PM

Alexander Kuznetsov

SELECT * FROM #Table1
WHERE TestDateTime = (SELECT max(TestDateTime) FROM #Table1
WHERE TestDateTime <= '20050110'
)

6 Jan 2006 7:55 PM

Payson

Something like this?

SELECT TestDateTime, Class
FROM Table1
WHERE TestDateTime = (SELECT MAX(TestDateTime) FROM Table1
WHERE TestDateTime <=
@TestDateTime)

Payson

Terri wrote:
Show quote

> I am passing a datetime parameter to a select.
>
> DECLARE @TestDateTime DateTime
>
> SET @TestDateTime = '20050110'
>
> SELECT * FROM Table1
> WHERE TestDateTime <= @TestDateTime
>
> There may not be a row with a datetime that matchs the parameter, in which
> case I want to select the next previous rows. I can't use top with an ORDER
> BY since the number of rows with a particular date will vary. Given the
> sample data and a parameter of '20050110' I want to select the 4 rows with
> the '20050105' datetime.
>
> Thanks
>
>
> CREATE TABLE [dbo].[Table1] (
> [TestDateTime] [datetime] NULL ,
> [Class] [char](1) NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','E')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','G')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','C')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','D')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','D')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','B')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','B')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','C')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','E')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','F')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','G')

6 Jan 2006 8:08 PM

SQL

One way
DECLARE @TestDateTime DateTime

SET @TestDateTime = '20050110'

SELECT t1.* FROM Table1 t1 join
(select max(TestDateTime) as MaxDate
FROM Table1
where TestDateTime <= @TestDateTime) t2 on t1.TestDateTime =
t2.MaxDate

http://sqlservercode.blogspot.com/

6 Jan 2006 8:13 PM

SQL

One way
DECLARE @TestDateTime DateTime

SET @TestDateTime = '20050110'

SELECT t1.* FROM Table1 t1 join
(select max(TestDateTime) as MaxDate
FROM Table1
where TestDateTime <= @TestDateTime) t2 on t1.TestDateTime =
t2.MaxDate

http://sqlservercode.blogspot.com/

6 Jan 2006 8:17 PM

Aaron Bertrand [SQL Server MVP]

SELECT TOP 1 <col_list>
FROM Table1
WHERE TestDateTime <= @TestDateTime;

Show quote

"Terri" <te***@cybernets.com> wrote in message
news:dpmh79$goh$1@reader2.nmix.net...
>I am passing a datetime parameter to a select.
>
> DECLARE @TestDateTime DateTime
>
> SET @TestDateTime = '20050110'
>
> SELECT * FROM Table1
> WHERE TestDateTime <= @TestDateTime
>
> There may not be a row with a datetime that matchs the parameter, in
> which
> case I want to select the next previous rows. I can't use top with an
> ORDER
> BY since the number of rows with a particular date will vary. Given the
> sample data and a parameter of '20050110' I want to select the 4 rows with
> the '20050105' datetime.
>
> Thanks
>
>
> CREATE TABLE [dbo].[Table1] (
> [TestDateTime] [datetime] NULL ,
> [Class] [char](1) NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','E')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','G')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','C')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','D')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','D')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','B')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','B')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','C')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','E')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','F')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','G')
>
>
>
>
>

6 Jan 2006 8:22 PM

Alexander Kuznetsov

SELECT * FROM #Table1
WHERE TestDateTime = (SELECT max(TestDateTime) FROM #Table1
WHERE TestDateTime <= '20050110'
)

6 Jan 2006 8:42 PM

Rick Sawtell

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uJ7Je3vEGHA.1032@TK2MSFTNGP11.phx.gbl...
> SELECT TOP 1 <col_list>
> FROM Table1
> WHERE TestDateTime <= @TestDateTime;
>
>

I think she wanted multiple possible rows back for this.

Maybe a slight modification to Aarons code..

SELECT TOP 1 WITH TIES <col_list>
FROM Table1
WHERE TestDateTime <= @TestDateTime;
ORDER BY TestDateTime DESC

Rick Sawtell
MCT, MCSD, MCDBA

6 Jan 2006 8:47 PM

Aaron Bertrand [SQL Server MVP]

> I think she wanted multiple possible rows back for this.

Yep, I read that part AFTER I cameup with the first knee-jerk solution.

I think my follow-up has a more complete answer...

6 Jan 2006 8:24 PM

wolfman

Will this work?

select * from table1
where (exists
    (select * from table1 where testdatetime = @testdatetime) )
   or
( not exists
    (select * from table1 where testdatetime = @testdatetime)
    and testdatetime = (select max(testdatetime) from table1 where
   testdatetime <= @testdatetime))

Alternatively, you can use a temp table and do it in a couple of steps.

Show quote

"Terri" <te***@cybernets.com> wrote in message
news:dpmh79$goh$1@reader2.nmix.net...
> I am passing a datetime parameter to a select.
>
> DECLARE @TestDateTime DateTime
>
> SET @TestDateTime = '20050110'
>
> SELECT * FROM Table1
> WHERE TestDateTime <= @TestDateTime
>
> There may not be a row with a datetime that matchs the parameter, in
which
> case I want to select the next previous rows. I can't use top with an
ORDER
> BY since the number of rows with a particular date will vary. Given the
> sample data and a parameter of '20050110' I want to select the 4 rows with
> the '20050105' datetime.
>
> Thanks
>
>
> CREATE TABLE [dbo].[Table1] (
> [TestDateTime] [datetime] NULL ,
> [Class] [char](1) NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','E')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','G')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','C')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','D')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','D')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','B')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','A')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','B')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','C')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','E')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','F')
> INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','G')
>
>
>
>
>

6 Jan 2006 8:30 PM

Alexander Kuznetsov

SELECT * FROM #Table1
WHERE TestDateTime = (SELECT max(TestDateTime) FROM #Table1
WHERE TestDateTime <= '20050110'
)

7 Jan 2006 2:37 PM

Aaron Bertrand [SQL Server MVP]

Why are you posting the same solution three times?

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1136579433.558455.12550@o13g2000cwo.googlegroups.com...
> SELECT * FROM #Table1
> WHERE TestDateTime = (SELECT max(TestDateTime) FROM #Table1
> WHERE TestDateTime <= '20050110'
> )
>

6 Jan 2006 8:41 PM

Steve Kass

Terri,

Will WITH TIES solve your problem?

select top 1 with ties *
from Table1
where TestDateTime <= @TestDateTime
order by TestDateTime desc

Steve Kass
Drew University

Terri wrote:

Show quote

>I am passing a datetime parameter to a select.
>
>DECLARE @TestDateTime DateTime
>
>SET @TestDateTime = '20050110'
>
>SELECT * FROM Table1
>WHERE TestDateTime <= @TestDateTime
>
>There may not be a row with a datetime that matchs the parameter, in which
>case I want to select the next previous rows. I can't use top with an ORDER
>BY since the number of rows with a particular date will vary. Given the
>sample data and a parameter of '20050110' I want to select the 4 rows with
>the '20050105' datetime.
>
>Thanks
>
>
>CREATE TABLE [dbo].[Table1] (
> [TestDateTime] [datetime] NULL ,
> [Class] [char](1) NULL
>) ON [PRIMARY]
>GO
>
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','A')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','E')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050101','G')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','A')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','C')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050102','D')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','D')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','B')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050103','A')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','A')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','B')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050104','C')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','E')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','F')
>INSERT INTO Table1 (TestDateTime,Class) VALUES ('20050105','G')
>
>
>
>
>
>
>

6 Jan 2006 8:46 PM

Aaron Bertrand [SQL Server MVP]

> There may not be a row with a datetime that matchs the parameter, in
> which
> case I want to select the next previous rows.

Sorry, I stopped reading here.

> I can't use top with an ORDER
> BY since the number of rows with a particular date will vary. Given the
> sample data and a parameter of '20050110' I want to select the 4 rows with
> the '20050105' datetime.

If they are only dates and all time portions are midnight, you can do this:

SELECT
    t.TestDateTime, t.Class
FROM
    Table1 t
INNER JOIN
(
    SELECT NextDate = MAX(TestDateTime)
        FROM Table1
        WHERE TestDateTime <= @TestDateTime
) sub
ON
    t.TestDateTime = sub.NextDate;

If your dates will have time portions, then a small change is necessary:

SELECT
    t.TestDateTime, t.Class
FROM
    Table1 t
INNER JOIN
(
    SELECT NextDate = CONVERT(SMALLDATETIME,
        CONVERT(CHAR(8), MAX(TestDateTime), 112))
        FROM Table1
        WHERE TestDateTime <= @TestDateTime
) sub
ON
    t.TestDateTime >= sub.NextDate
    AND t.TestDateTime < sub.NextDate + 1;

(By the way, I only find 3 rows in your sample data where TestDateTime =
20050105, not 4 rows.)

Of course, these queries will be most efficient if TestDateTime has an
index. And further to that, if you are not using the time portion at all,
consider changing the datatype to SMALLDATETIME. You should run some
experiments with different configurations and compare the query plans and
statistics i/o...

A

6 Jan 2006 8:46 PM

Aaron Bertrand [SQL Server MVP]

> There may not be a row with a datetime that matchs the parameter, in
> which
> case I want to select the next previous rows.

Sorry, I stopped reading here.

> I can't use top with an ORDER
> BY since the number of rows with a particular date will vary. Given the
> sample data and a parameter of '20050110' I want to select the 4 rows with
> the '20050105' datetime.

If they are only dates and all time portions are midnight, you can do this:

SELECT
    t.TestDateTime, t.Class
FROM
    Table1 t
INNER JOIN
(
    SELECT NextDate = MAX(TestDateTime)
        FROM Table1
        WHERE TestDateTime <= @TestDateTime
) sub
ON
    t.TestDateTime = sub.NextDate;

If your dates will have time portions, then a small change is necessary:

SELECT
    t.TestDateTime, t.Class
FROM
    Table1 t
INNER JOIN
(
    SELECT NextDate = CONVERT(SMALLDATETIME,
        CONVERT(CHAR(8), MAX(TestDateTime), 112))
        FROM Table1
        WHERE TestDateTime <= @TestDateTime
) sub
ON
    t.TestDateTime >= sub.NextDate
    AND t.TestDateTime < sub.NextDate + 1;

(By the way, I only find 3 rows in your sample data where TestDateTime =
20050105, not 4 rows.)

Of course, these queries will be most efficient if TestDateTime has an
index. And further to that, if you are not using the time portion at all,
consider changing the datatype to SMALLDATETIME. You should run some
experiments with different configurations and compare the query plans and
statistics i/o...

A