Q
Problem solve Get help with specific problems with your technologies, process and projects.

Querying current and previous outstanding reserves

Greg Low offers help writing a query that gives both the current outstanding reserve and the prior outstanding reserve.

I need to write a query that gives both the current outstanding reserve and the prior outstanding reserve. The answer is derived from the same column only different line items. For example:

Claim #: 1, Date: 6/12/05, Seq. #: 1, OutRes $1500
Claim #: 1, Date: 10/1/05, Seq. #: 2, OutRes $2000
Claim #: 1, Date: 10/19/05, Seq. #: 3, OutRes $0

Required result:
Claim #1, Prior Reserve 2000, Current Reserve 0.

I can't seem to get the syntax right. Any thoughts on what I'm missing?

I suspect you're after:

SELECT TOP 2 OutRes,Seq FROM Claim ORDER BY Date DESC,Seq DESC

That'll return 2 rows, the first is the current reserve. The second (if there is one) will return the prior reserve.

Dig Deeper on SQL-Transact SQL (T-SQL)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close