Finding items only added after certain date

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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding items only added after certain date

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2007-10-22 : 05:07:12

Hi,

This is probably simple, but I'm having trouble figuring it out.

Let's say I have a table like this:


locationID  version     modified                                               
----------- ----------- ------------------------------------------------------ 
4           1           2007-10-14 11:09:33.000
4           2           2007-10-16 16:23:03.000
4           3           2007-10-16 16:23:20.000
4           4           2007-10-16 16:26:21.000
4           5           2007-10-18 14:43:36.000
4           6           2007-10-19 09:07:41.000
4           7           2007-10-19 09:08:01.000
4           8           2007-10-19 11:36:59.000
4           9           2007-10-19 14:46:18.000
4           10          2007-10-19 15:27:17.000
4           11          2007-10-22 09:47:04.000
5           1           2007-10-14 11:12:12.000
5           2           2007-10-19 11:36:01.000
5           3           2007-10-19 11:36:39.000
5           4           2007-10-22 09:47:21.000
6           1           2007-10-19 14:41:41.000
6           2           2007-10-19 14:45:41.000
7           1           2007-10-19 14:42:41.000
7           2           2007-10-19 14:45:58.000

What I need to do is select a single record of minimum date value for only those locations for which the modified date is higher than a given date and (and this is the tricky bit) don't have any records at all with a modified date which is less than the given date.

So my desired output would look something like, for a date of 18th October:


locationID  version     modified                                               
----------- ----------- ------------------------------------------------------ 
6           1           2007-10-19 14:41:41.000
7           1           2007-10-19 14:42:41.000

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-22 : 05:18:47

select max(id) as id, modified
from yourTable t1
where modified >= yourGivenDate and not exists (select * from yourTable t2 where t1.id = t2.id and t2.modified < yourGivenDate)
group by modified

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

mattt
Posting Yak Master

194 Posts

Posted - 2007-10-22 : 05:31:18

Hi,

Great, thanks. I knew it'd be something to so with exists, but I just couldn't line it up in my head :).

Cheers,
Matt

quote:
Originally posted by spirit1

select max(id) as id, modified
from yourTable t1
where modified >= yourGivenDate and not exists (select * from yourTable t2 where t1.id = t2.id and t2.modified < yourGivenDate)
group by modified

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-22 : 09:05:04

[code]DECLARE @Sample TABLE (LocationID INT, Version INT, Modified DATETIME)

INSERT @Sample
SELECT 4, 1, '2007-10-14 11:09:33' UNION ALL
SELECT 4, 2, '2007-10-16 16:23:03' UNION ALL
SELECT 4, 3, '2007-10-16 16:23:20' UNION ALL
SELECT 4, 4, '2007-10-16 16:26:21' UNION ALL
SELECT 4, 5, '2007-10-18 14:43:36' UNION ALL
SELECT 4, 6, '2007-10-19 09:07:41' UNION ALL
SELECT 4, 7, '2007-10-19 09:08:01' UNION ALL
SELECT 4, 8, '2007-10-19 11:36:59' UNION ALL
SELECT 4, 9, '2007-10-19 14:46:18' UNION ALL
SELECT 4, 10, '2007-10-19 15:27:17' UNION ALL
SELECT 4, 11, '2007-10-22 09:47:04' UNION ALL
SELECT 5, 1, '2007-10-14 11:12:12' UNION ALL
SELECT 5, 2, '2007-10-19 11:36:01' UNION ALL
SELECT 5, 3, '2007-10-19 11:36:39' UNION ALL
SELECT 5, 4, '2007-10-22 09:47:21' UNION ALL
SELECT 6, 1, '2007-10-19 14:41:41' UNION ALL
SELECT 6, 2, '2007-10-19 14:45:41' UNION ALL
SELECT 7, 1, '2007-10-19 14:42:41' UNION ALL
SELECT 7, 2, '2007-10-19 14:45:58'

DECLARE @dt DATETIME
SET @dt = '20071018'

-- Spirit1
SELECT MAX(t1.ID) AS ID,
t1.Modified
FROM @Sample AS t1
WHERE t1.Modified >= @dt
AND NOT EXISTS (SELECT * FROM @Sample AS t2 WHERE t1.ID = t2.ID AND t2.Modified < @dt)
GROUP BY t1.Modified

-- Peso
SELECT LocationID,
Version,
Modified
FROM (
SELECT LocationID,
Version,
Modified,
ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY Modified) AS RowID,
SUM(CASE WHEN Modified < @dt THEN 1 ELSE 0 END) OVER (PARTITION BY LocationID) AS Items
FROM @Sample
) AS d
WHERE RowID = 1
AND Items = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources