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 2012 Forums
 Transact-SQL (2012)
 CROSS APPLY - Currency

Author  Topic 

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:24:25
I will first paste test scripts:

-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#Test_Currency')) <> 0
BEGIN
DROP TABLE #Test_Currency
END

-- Create Temp tables
CREATE TABLE [dbo].#Test_Currency
([TheCompany] [varchar](14) NOT NULL,
[Currency Code] [varchar](10) NULL,
[Currency Starting Date] [datetime] NULL,
[Exchange Rate Amount] [decimal](38, 20) NULL
)
;

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:24:47
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2006-09-30 00:00:00.000','1.45');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2008-01-01 00:00:00.000','1.3');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2008-05-01 00:00:00.000','1.25');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2008-09-01 00:00:00.000','1.2');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2009-04-01 00:00:00.000','1.1');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2009-10-03 00:00:00.000','1.09');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2009-10-28 00:00:00.000','1.104');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2009-10-31 00:00:00.000','1.104');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2009-11-28 00:00:00.000','1.085');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2009-12-24 00:00:00.000','1.101');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-01-23 00:00:00.000','1.136');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-02-20 00:00:00.000','1.13');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-03-20 00:00:00.000','1.103');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-04-17 00:00:00.000','1.125');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-05-15 00:00:00.000','1.15');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-06-12 00:00:00.000','1.202');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-07-10 00:00:00.000','1.18');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-08-07 00:00:00.000','1.192');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-09-04 00:00:00.000','1.191');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-10-02 00:00:00.000','1.162');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-10-30 00:00:00.000','1.158');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-11-27 00:00:00.000','1.188');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2010-12-25 00:00:00.000','1.185');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-01-22 00:00:00.000','1.177');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-02-19 00:00:00.000','1.173');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-03-19 00:00:00.000','1.135');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-04-16 00:00:00.000','1.117');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-05-14 00:00:00.000','1.134');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-06-11 00:00:00.000','1.118');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-07-09 00:00:00.000','1.115');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-08-06 00:00:00.000','1.131');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-09-03 00:00:00.000','1.124');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-10-01 00:00:00.000','1.152');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-10-29 00:00:00.000','1.129');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-11-26 00:00:00.000','1.151');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2011-12-24 00:00:00.000','1.186');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-01-21 00:00:00.000','1.181');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-02-18 00:00:00.000','1.189');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-03-17 00:00:00.000','1.188');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-04-14 00:00:00.000','1.222');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-05-12 00:00:00.000','1.26');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-06-09 00:00:00.000','1.248');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-07-07 00:00:00.000','1.266');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-08-04 00:00:00.000','1.285');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-09-01 00:00:00.000','1.274');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-09-29 00:00:00.000','1.269');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-10-29 00:00:00.000','1.259');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-11-24 00:00:00.000','1.236');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2012-12-22 00:00:00.000','1.229');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-01-19 00:00:00.000','1.194');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-02-16 00:00:00.000','1.161');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-03-16 00:00:00.000','1.156');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-04-13 00:00:00.000','1.174');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-05-11 00:00:00.000','1.184');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-06-08 00:00:00.000','1.176');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-07-06 00:00:00.000','1.164');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-08-03 00:00:00.000','1.147');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-08-31 00:00:00.000','1.171');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-09-28 00:00:00.000','1.191');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-10-26 00:00:00.000','1.173');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-11-23 00:00:00.000','1.199');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2013-12-21 00:00:00.000','1.198');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-01-18 00:00:00.000','1.205');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-02-15 00:00:00.000','1.219');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-03-15 00:00:00.000','1.197');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-04-12 00:00:00.000','1.206');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-05-10 00:00:00.000','1.224');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-06-07 00:00:00.000','1.231');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-07-05 00:00:00.000','1.261');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-08-02 00:00:00.000','1.258');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-08-30 00:00:00.000','1.259');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-09-27 00:00:00.000','1.28');
INSERT INTO #Test_Currency VALUES ('United Kingdon','EUR','2014-10-25 00:00:00.000','1.268');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2006-09-30 00:00:00.000','141.175');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2012-04-14 00:00:00.000','130.29');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2012-11-24 00:00:00.000','131.42');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2012-12-22 00:00:00.000','136.54');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-02-15 00:00:00.000','170.09');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-03-15 00:00:00.000','168.78');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-04-12 00:00:00.000','170.2');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-05-10 00:00:00.000','171.83');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-06-07 00:00:00.000','172.12');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-07-05 00:00:00.000','175.13');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-08-02 00:00:00.000','173.23');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-08-30 00:00:00.000','172.29');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-09-27 00:00:00.000','177.64');
INSERT INTO #Test_Currency VALUES ('United Kingdon','JPY','2014-10-25 00:00:00.000','173.48');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2004-01-01 00:00:00.000','1.75');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2004-09-30 00:00:00.000','1.75');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2006-09-30 00:00:00.000','1.75');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2007-03-01 00:00:00.000','1.85');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2008-09-01 00:00:00.000','1.75');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2009-04-01 00:00:00.000','1.75');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2009-10-03 00:00:00.000','1.58');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2009-10-31 00:00:00.000','1.639');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2009-11-28 00:00:00.000','1.615');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2009-12-24 00:00:00.000','1.584');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-01-23 00:00:00.000','1.61');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-02-20 00:00:00.000','1.526');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-03-20 00:00:00.000','1.504');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-04-17 00:00:00.000','1.536');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-05-15 00:00:00.000','1.447');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-06-12 00:00:00.000','1.458');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-07-10 00:00:00.000','1.503');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-08-07 00:00:00.000','1.575');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-09-04 00:00:00.000','1.527');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-10-02 00:00:00.000','1.589');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-10-30 00:00:00.000','1.619');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-11-27 00:00:00.000','1.58');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2010-12-25 00:00:00.000','1.559');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-01-22 00:00:00.000','1.611');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-02-19 00:00:00.000','1.604');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-03-19 00:00:00.000','1.608');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-04-16 00:00:00.000','1.618');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-05-14 00:00:00.000','1.602');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-06-11 00:00:00.000','1.608');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-07-09 00:00:00.000','1.582');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-08-06 00:00:00.000','1.623');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-09-03 00:00:00.000','1.603');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-10-01 00:00:00.000','1.539');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-10-29 00:00:00.000','1.588');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-11-26 00:00:00.000','1.537');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2011-12-24 00:00:00.000','1.555');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-01-21 00:00:00.000','1.534');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-02-18 00:00:00.000','1.565');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-03-17 00:00:00.000','1.557');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-04-14 00:00:00.000','1.61');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-05-12 00:00:00.000','1.624');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-06-09 00:00:00.000','1.561');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-07-07 00:00:00.000','1.569');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-08-04 00:00:00.000','1.569');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-09-01 00:00:00.000','1.595');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-09-29 00:00:00.000','1.642');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-10-29 00:00:00.000','1.628');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-11-24 00:00:00.000','1.596');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2012-12-22 00:00:00.000','1.623');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-01-19 00:00:00.000','1.594');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-02-16 00:00:00.000','1.551');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-03-16 00:00:00.000','1.511');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-04-13 00:00:00.000','1.537');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-05-11 00:00:00.000','1.541');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-06-08 00:00:00.000','1.557');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-07-06 00:00:00.000','1.498');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-08-03 00:00:00.000','1.518');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-08-31 00:00:00.000','1.55');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-09-28 00:00:00.000','1.609');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-10-26 00:00:00.000','1.619');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-11-23 00:00:00.000','1.62');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2013-12-21 00:00:00.000','1.635');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-01-18 00:00:00.000','1.638');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-02-15 00:00:00.000','1.669');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-03-15 00:00:00.000','1.662');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-04-12 00:00:00.000','1.676');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-05-10 00:00:00.000','1.689');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-06-07 00:00:00.000','1.681');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-07-05 00:00:00.000','1.715');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-08-02 00:00:00.000','1.685');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-08-30 00:00:00.000','1.659');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-09-27 00:00:00.000','1.629');
INSERT INTO #Test_Currency VALUES ('United Kingdon','USD','2014-10-25 00:00:00.000','1.605');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2006-09-30 00:00:00.000','1.45');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2008-01-01 00:00:00.000','1.3');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2008-05-01 00:00:00.000','1.25');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2008-09-01 00:00:00.000','1.2');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2009-10-03 00:00:00.000','1.082');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2010-10-02 00:00:00.000','1.162');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2010-10-30 00:00:00.000','1.158');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2010-11-27 00:00:00.000','1.188');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2010-12-25 00:00:00.000','12.122');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-01-22 00:00:00.000','1.177');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-02-19 00:00:00.000','1.173');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-03-19 00:00:00.000','1.135');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-04-16 00:00:00.000','1.117');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-05-14 00:00:00.000','1.134');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-06-11 00:00:00.000','1.118');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-07-09 00:00:00.000','1.115');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-08-06 00:00:00.000','1.131');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-09-03 00:00:00.000','1.124');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-10-01 00:00:00.000','1.152');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-10-29 00:00:00.000','1.129');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-11-26 00:00:00.000','1.151');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2011-12-24 00:00:00.000','1.186');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-01-21 00:00:00.000','1.181');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-02-18 00:00:00.000','1.189');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-03-17 00:00:00.000','1.189');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-04-14 00:00:00.000','1.222');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-05-12 00:00:00.000','1.26');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-06-09 00:00:00.000','1.248');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-07-07 00:00:00.000','1.266');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-08-04 00:00:00.000','1.285');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-09-01 00:00:00.000','1.274');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-09-29 00:00:00.000','1.269');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-11-24 00:00:00.000','1.236');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2012-12-22 00:00:00.000','1.229');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-01-19 00:00:00.000','1.594');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-02-16 00:00:00.000','1.161');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-03-16 00:00:00.000','1.156');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-04-13 00:00:00.000','1.174');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-05-11 00:00:00.000','1.184');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-06-08 00:00:00.000','1.176');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-07-06 00:00:00.000','1.164');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-08-03 00:00:00.000','1.147');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-08-31 00:00:00.000','1.171');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-10-26 00:00:00.000','1.173');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-11-23 00:00:00.000','1.199');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2013-12-21 00:00:00.000','1.198');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-01-18 00:00:00.000','1.205');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-02-15 00:00:00.000','1.219');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-03-15 00:00:00.000','1.197');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-04-12 00:00:00.000','1.206');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-05-10 00:00:00.000','1.224');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-06-07 00:00:00.000','1.231');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-07-05 00:00:00.000','1.261');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-08-02 00:00:00.000','1.258');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-08-30 00:00:00.000','1.259');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-09-27 00:00:00.000','1.28');
INSERT INTO #Test_Currency VALUES ('United States','EUR','2014-10-25 00:00:00.000','1.268');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2006-09-30 00:00:00.000','220');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2012-11-24 00:00:00.000','131.42');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2012-12-22 00:00:00.000','136.54');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-02-15 00:00:00.000','170.09');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-03-15 00:00:00.000','168.78');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-04-12 00:00:00.000','170.2');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-05-10 00:00:00.000','171.83');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-06-07 00:00:00.000','172.12');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-07-05 00:00:00.000','175.13');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-08-02 00:00:00.000','173.23');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-08-30 00:00:00.000','172.29');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-09-27 00:00:00.000','177.64');
INSERT INTO #Test_Currency VALUES ('United States','JPY','2014-10-25 00:00:00.000','173.48');
INSERT INTO #Test_Currency VALUES ('United States','USD','2001-01-01 00:00:00.000','1');
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:25:08
-- One one currency

Select * From #Test_Currency
where [Currency Code] = 'EUR'
and [TheCompany] = 'United Kingdon'
order by [Currency Starting Date]
;
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:26:27
I need to create a Currency Table with Start and End Dates so I can join to a sales table and do WHERE Sales Date BETWEEN Currency Start and End Date to get the Currency Rate on the date.

This script I have working OK on one company and currency code:

-- Looks like this works OK 73 records

select
d.[Currency Starting Date]
,ISNULL(nxt.[Currency Starting Date]-1,getdate()) AS [Currency Ending Date]
,d.[Exchange Rate Amount]
from #Test_Currency d

cross apply
(
select
MIN(nxt.[Currency Starting Date]) [Currency Starting Date]
,MIN(nxt.[Exchange Rate Amount]) as [Exchange Rate Amount]
from #Test_Currency nxt
where d.[TheCompany] = nxt.[TheCompany]
and d.[Currency Code] = nxt.[Currency Code]
and d.[Currency Starting Date] < nxt.[Currency Starting Date]
) nxt

WHERE d.[Currency Code] = 'EUR'
AND d.[TheCompany] = 'United Kingdon'

order by d.[Currency Starting Date]
;
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:27:47
But when I add [The company] and [Currency Code] I miss the last record and can't work out why at the moment:

-- Not selecting last date 25-10-2014 1.268 72 records

select d.[TheCompany]
,d.[Currency Code]
,d.[Currency Starting Date]
,ISNULL(nxt.[Currency Starting Date]-1,getdate()) AS [Currency Ending Date]
,d.[Exchange Rate Amount]
from #Test_Currency d

cross apply
(
select nxt.[TheCompany]
,nxt.[Currency Code]
,MIN(nxt.[Currency Starting Date]) [Currency Starting Date]
,MIN(nxt.[Exchange Rate Amount]) as [Exchange Rate Amount]
from #Test_Currency nxt
where d.[TheCompany] = nxt.[TheCompany]
and d.[Currency Code] = nxt.[Currency Code]
and d.[Currency Starting Date] < nxt.[Currency Starting Date]
group by nxt.[TheCompany]
,nxt.[Currency Code]
) nxt

WHERE d.[Currency Code] = 'EUR'
AND d.[TheCompany] = 'United Kingdon'

order by d.[Currency Starting Date]
;
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:28:18
What I need to end up with is a script running on everything in the table like:

- I need to get it to run on all in the tbale too, all companies and currency

select d.[TheCompany]
,d.[Currency Code]
,d.[Currency Starting Date]
,ISNULL(nxt.[Currency Starting Date]-1,getdate()) AS [Currency Ending Date]
,d.[Exchange Rate Amount]
from #Test_Currency d

cross apply
(
select nxt.[TheCompany]
,nxt.[Currency Code]
,MIN(nxt.[Currency Starting Date]) [Currency Starting Date]
,MIN(nxt.[Exchange Rate Amount]) as [Exchange Rate Amount]
from #Test_Currency nxt
where d.[TheCompany] = nxt.[TheCompany]
and d.[Currency Code] = nxt.[Currency Code]
and d.[Currency Starting Date] < nxt.[Currency Starting Date]
group by nxt.[TheCompany]
,nxt.[Currency Code]
) nxt

order by d.[TheCompany], d.[Currency Starting Date]
;
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:30:29
I guess I am missing the last record in post: 10/30/2014 : 07:27:47
because there is no nxt.[Currency Starting Date] on the last record to check against..
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:42:54
Looking at this agin mayb my problem is in the cross join I have grouped:

I don't need this bit in cross apply

select nxt.[TheCompany]
,nxt.[Currency Code]
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 07:46:53
I think I may have fixed it but not sure why, I removed the Group By within the Cross Join, does this look right to an expert SQL writer?

-- ON ALL

-- on all currency check

Select * From #Test_Currency
order by [TheCompany],[Currency Code],[Currency Starting Date]
;


select d.[TheCompany]
,d.[Currency Code]
,d.[Currency Starting Date]
,ISNULL(nxt.[Currency Starting Date]-1,getdate()) AS [Currency Ending Date]
,d.[Exchange Rate Amount]
from #Test_Currency d

cross apply
(
select MIN(nxt.[Currency Starting Date]) [Currency Starting Date]
,MIN(nxt.[Exchange Rate Amount]) as [Exchange Rate Amount]
from #Test_Currency nxt
where d.[TheCompany] = nxt.[TheCompany]
and d.[Currency Code] = nxt.[Currency Code]
and d.[Currency Starting Date] < nxt.[Currency Starting Date]
) nxt

order by d.[TheCompany], d.[Currency Code], d.[Currency Starting Date]
;
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2014-10-30 : 08:21:22
As you seem to be using SQL2012, the LEAD windowed function will be much more readable:


SELECT TheCompany, [Currency Code], [Currency Starting Date]
	,LEAD([Currency Starting Date] - 1, 1, DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0))
		OVER (PARTITION BY TheCompany, [Currency Code] ORDER BY [Currency Starting Date]) AS [Currency Ending Date]
	,[Exchange Rate Amount]
FROM #Test_Currency
ORDER BY TheCompany, [Currency Code], [Currency Starting Date]


I would also be inclined to get rid of the spaces in the column names and the square brackets.
Go to Top of Page

rogerclerkwell
Yak Posting Veteran

85 Posts
Posted - 2014-10-30 : 09:33:41
HI Ifor,

I'm on SQL 2005, that does look a much better clearer script in 2012.

Yes I can see now it would be cleaner without the spaces or brackets.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site