USP_SIMPLEDATALIST_CURRENCYEXCHANGERATES

Returns a list of exchange rates for converted from the given currency to teh given currency.
Parameters

Parameter	Parameter Type	Mode	Description
@FROMCURRENCYID	uniqueidentifier	IN	From currency
@TOCURRENCYID	uniqueidentifier	IN	To currency
@TYPECODE	tinyint	IN	Type
@ASOFDATE	datetime	IN	As of date
Definition

Copy


                CREATE procedure dbo.USP_SIMPLEDATALIST_CURRENCYEXCHANGERATES
                (
                    @FROMCURRENCYID uniqueidentifier = null,
                    @TOCURRENCYID uniqueidentifier = null,
                    @TYPECODE tinyint = null,
                    @ASOFDATE datetime = null
                )
                as
                    set nocount on;

                    set @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

                    declare @APPLICABLERATE table(
                        ID uniqueidentifier,
                        SOURCECODEID uniqueidentifier,
                        ASOFDATE datetime,
                        DATEADDED datetime
                    )

                    insert into @APPLICABLERATE(
                        ID, SOURCECODEID, ASOFDATE, DATEADDED
                    )
                    select
                        CURRENCYEXCHANGERATE.ID,
                        CURRENCYEXCHANGERATE.SOURCECODEID,
                        CURRENCYEXCHANGERATE.ASOFDATE,
                        CURRENCYEXCHANGERATE.DATEADDED
                    from
                        dbo.CURRENCYEXCHANGERATE
                    where
                        (@FROMCURRENCYID is null or @FROMCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID)
                        and (@TOCURRENCYID is null or @TOCURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID)
                        and (@TYPECODE is null or @TYPECODE = CURRENCYEXCHANGERATE.TYPECODE) 
                        and (CURRENCYEXCHANGERATE.ASOFDATE <= @ASOFDATE)


                    select
                        RATE.ID as VALUE,
                        case
                            when RATE.SOURCECODEID is null
                                then convert(nvarchar,RATE.ASOFDATE)
                            else 
                                CURRENCYEXCHANGERATESOURCECODE.DESCRIPTION + ' (' + convert(nvarchar,RATE.ASOFDATE) + ')'
                        end as LABEL
                    from
                        @APPLICABLERATE RATE
                        inner join (
                            select 
                                SOURCECODEID,
                                max(ASOFDATE) MAXDATE
                            from 
                                @APPLICABLERATE RATE
                            group by SOURCECODEID
                        ) SOURCEMAXDATE 
                            on SOURCEMAXDATE.MAXDATE = RATE.ASOFDATE 
                                and(SOURCEMAXDATE.SOURCECODEID = RATE.SOURCECODEID
                                    or (SOURCEMAXDATE.SOURCECODEID is null and RATE.SOURCECODEID is null)
                                )
                        left join dbo.CURRENCYEXCHANGERATESOURCECODE on CURRENCYEXCHANGERATESOURCECODE.ID = SOURCEMAXDATE.SOURCECODEID
                    order by RATE.ASOFDATE desc, CURRENCYEXCHANGERATESOURCECODE.DESCRIPTION, RATE.DATEADDED desc;