HELP ME!!! :)

Question

Dear colleagues can somebody help me?

I need to determine the agreement status which to certain date.

for example:

+----+---------------------+--------+--------+
| ID |        date         | status |        |
+----+---------------------+--------+--------+
| 10 | 2016-06-24 00:00:00 |      4 |        |
| 10 | 2016-06-27 00:00:00 |      5 |        |
| 10 | 2016-06-30 00:00:00 |      7 | needed |
|  8 | 2016-06-03 00:00:00 |      3 |        |
|  8 | 2016-06-27 00:00:00 |      6 |        |
|  8 | 2016-06-29 00:00:00 |      9 | needed |
+----+---------------------+--------+--------+

needed to determine which status to be on (2016-06-30 00:00:00) 

+----+--------+
| ID | status |
+----+--------+
| 10 |      7 |
|  8 |      9 |
+----+--------+

I`m sorry for my english! :)

Answer 1

Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

Answer 2

;with cte as (select *, row_number() over (partition by AgreementId order by [Date] DESC) as Rn

from dbo.Agreements where [date] <=@DateParameter)

select [AgreementId], [Status] from cte where Rn = 1 -- latest status for the passed date parameter

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 3

Thank you for answer. This was helpful, but i need to do this as query, because i wanna add it to my earlier query.

Answer 4

Naomi gave you a query. She did not modify your existing query, since she don't know how it looks like. Or even knew about it at the time for her post.

Answer 5

So, can you provide more context? You can easily incorporate the above into existing query, show your current query and tell us what exactly do you want to achieve.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 6

I`m sorry, i`m mistaked.

Many thank you for your support.