Select Last Record For Each Day

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)

Select Last Record For Each Day

Author

Topic

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-04-01 : 10:02:49

I'm always not so good at these DateTime querys so apologys if this has been asked before!

I have a table with a DateTime Column

Entry_DT OtherDataColumn
2010/04/01 10:50 x
2010/04/01 11:00 y
2010/05/01 10:50 z
2010/07/01 10:50 q
2010/07/01 14:50 q

What I'm after is the last record for every date.
So from the table above I'd want

2010/04/01 11:00 y
2010/05/01 10:50 z
2010/07/01 14:50 q

Thanks in advance

Chris

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 10:12:53

[code]
select *
from
(
select *, row_no = row_number() over (partition by dateadd(day, datediff(day, 0, Entry_DT), 0) order by Entry_DT desc)
from yourtable
) d
where d.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-04-01 : 11:17:51

[quote]Originally posted by khtan


select *
from
(
     select *, row_no = row_number() over (partition by dateadd(day, datediff(day, 0, Entry_DT), 0) order by Entry_DT desc)
     from   yourtable
) d
where d.row_no = 1

That's great Thanks Khtan

That's got me where quite far...
I then got stuck on the next bit!

Table 1

Entry_DT identityInTable2 OtherDataColumn
2010/04/01 10:50 10 x
2010/04/01 11:00 11 y
2010/05/01 10:50 12 z
2010/07/01 10:50 13 q
2010/07/01 14:50 14 q

Table 2
Identity DataToBeUpdated
10 -
11 -
12 -
13 -
14 -

I need to update table 2's DataToBeUpdated column based on the output of Khtans Select statement...

I've tried something along the lines of
UPDATE [table2]
SET [DataToBeUpdated] = (select [OtherDataColumn]
from
(
select *, row_no = row_number() over (partition by dateadd(day, datediff(day, 0, Entry_DT), 0) order by Entry_DT desc)
from [Table1]
) d
where d.row_no = 1 and [identityInTable2]=[Identity] )

That's failed with a "Cannot insert the value NULL into column DataToBeUpdated"

Thanks,
Chris

Mr ChriZ
Starting Member

10 Posts

Posted - 2010-04-01 : 12:14:40

Ignore my previous post, I've sorted it.
I was trying to insert in the wrong place anyway.

Many Thanks to all those who've read this!

Chris.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources