Getting last know data from a table

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 last know data from a table

Author

Topic

dosteroid
Starting Member

29 Posts

Posted - 2011-11-11 : 05:33:54

Hi
I have a project where im creating a spend Analysis view, where I need to show Spend per company etc... But the demand it to be in USD, so I need to use a second conversion table in order to get the conversion factor... However spend data sometimes exists for the future aswell, but the conversion factor does not (cause the tax rate is unknown ofcause), so I to write a code saying something like: if Conversion factor (CXCRR) is zero, then take the latest known echange rate based on currency from(CCCRCD), currenct to(CXCRDC),month(CFEFT) and year(CFEFT)... What I've written so far is:
(GLAA = Spend Amount, GLDGJ = Transaction Date)

,case when (select CXCRR from F0015
where CXCRCD = CCCRCD and CXCRDC = 'USX'
and Month(dbo.JDEJulian(F0015.CXEFT)) = MONTH(dbo.JDEJulian(F0911.GLDGJ))
and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F0911.GLDGJ))) IS null
then (GLAA/100*(select CXCRR from F0015
where CXCRCD = CCCRCD and CXCRDC = 'USX'
and Month(dbo.JDEJulian(F0015.CXEFT)) = MONTH(dbo.JDEJulian(F0911.GLDGJ))
and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F0911.GLDGJ))
GROUP BY CXCRR,CXEFT
HAVING Month(dbo.JDEJulian(F0015.CXEFT)) = MAX(Month(dbo.JDEJulian(F0015.CXEFT)))))

But I only get NULL values for e.g december, where it's suppose to do: GLAA * ConversionFactor From LastknownMonth

Any help is appreciated :D
p.s. Not used to use the HAVING CLAUSE so I guess Im doing something wrong with that...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 06:11:51

i cant make out the tables from above query. so i will give you stub. use this


SELECT othercolumns...,
t.column/100 * t1.Taxratecolumn
FROM yourtable t
CROSS APPLY (SELECT TOP 1 Taxratecolumn
             FROM conversiontable
             where basecurrency=t.yourcurrency
             and convertedcurrency='USD'
             and Date <= t.Date
             ORDER BY Date DESC)t1

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

dosteroid
Starting Member

29 Posts

Posted - 2011-11-11 : 07:33:30

Cheers... Never thought of using top 1 + desc which is much more simple :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 08:50:22

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources