Getting most current record - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Getting most current record

Author

Topic

kungfuman
Starting Member

2 Posts

Posted - 2011-11-22 : 14:34:35

Hi Guys
I hope u can help me.

I have a table that stores all transactions associated to a specific contract.
each transaction has a status which can be either open or close throughout the life of a transaction , it can fluctuate between open and close and vice versa.

Whenever there is an update on the transaction, a new row is added.

I am trying to run a query that should only retrieve contracts for any given month where the status of the last added transaction record during that month is closed

How do i makes that only the most recent record for that period is returned?

Thanks a millions

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-22 : 15:03:21

You can use the row_number function - something like this:

WITH CTE AS
(
	SELECT 
		*,
		ROW_NUMBER() OVER (PARTITION BY contract_number, period ORDER BY last_update_time DESC) RN
	FROM
		YourTable
)
SELECT * FROM CTE WHERE RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 23:46:42

you can add below condition to where condition above to check that status is closed


AND Status='Closed'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-23 : 10:48:14

I am trying to run a query that should only retrieve contracts for any given month where the status of the last added transaction record during that month is closed
I thought that's better to use top 1 Last_update_time

select Last_update_time from [contracts tablename] where  contracts.Date WHERE  =
      (SELECT contracts.Date
       FROM Northwind.dbo.Products
       WHERE ProductName = 'Sir Rodney''s Scones')
=@date and status=closed

i believe that will return you the last date with status = false

kungfuman
Starting Member

2 Posts

Posted - 2011-11-23 : 14:18:51

Thanks guys..this worked.

quote:
Originally posted by sunitabeck

You can use the row_number function - something like this:
WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY contract_number, period ORDER BY last_update_time DESC) RN
FROM
YourTable
)
SELECT * FROM CTE WHERE RN = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources