Just the most recent TOP or ??

  • Thread starter Thread starter warway
  • Start date Start date
W

warway

The following procedure returns the data I want but I need to show just the
latest instance of each s.CurrencyCode in order to populate a combobox, any
help greatly appreciated!


Alter PROCEDURE dbo.sp_ROECurrent3 @LessDate datetime

AS

SELECT s.ROEID,s.CurrencyCode, s.ExchangeRATE, s.ROEDate, o.ROEWeighting,
o.BaseCurrency, 'Weighted ROE' =

CASE

WHEN s.CurrencyCode <> o.BaseCurrency THEN s.exchangeRate - (s.ExchangeRATE
*(1+o.ROEWeighting) - s.exchangeRate)

ELSE 1

END,

CAST(o.CurrencyName AS varchar(20)) AS 'Currency'

FROM ROECurrentView o JOIN tblkpROE s ON o.ROEDate = s.ROEdate AND
s.CurrencyCode=o.CurrencyCode

GROUP BY
s.ROEID,s.CurrencyCode,s.ExchangeRate,s.ROEDate,o.ROEWeighting,o.BaseCurrenc
y,o.CurrencyName,o.CurrencyCode

HAVING MAX(DISTINCT o.ROEDate) <= @LessDate

ORDER by o.CurrencyCode



Warway
 
Warway,

I'd try MAX(s.ROEDate) in the SELECT list and remove s.ROEDate from the
GROUP BY clause, but I'm likely to be wrong.

I'd also try posting to microsoft.public.access.adp.sqlserver and/or
microsoft.public.sqlserver.programming. You are more likely to find someone
able to help you.

If worst comes to worst you could try something like this.

Declare @n as integer
Declare @s as nvarchar(255)

Find the number of unique currency codes
Select @n = Count(CurrencyCode) From (Select Distinct CurrencyCodeFrom
Orders where CurrencyCode is not null) AS a

Select the top @n records from your query where XX is the text of your query
set @s = 'Select Top ' + cast(@n as nvarchar(255)) + ' * From (XX) As b'
exec sp_executesql @s
 
Back
Top