Recursive CTE help please - SQL Server Forums

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)

Recursive CTE help please

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2014-11-14 : 11:05:20

Hi, its been a long time since I was here and its not like me to ask for help but I've never been comfortable with recursive queries and now I think need to use one.

I have a RegionDimension table which holds regions and the relative exchange rates to the UK pound (don't get hung up on the values because I made them up for this example). The valuse change over time and that's what's giving me problems.

This is my table and some dummy data.


CREATE TABLE [dbo].[RegionDimension](
	[RegionKey] [int] NOT NULL,
	[Region] [varchar](20) NOT NULL,
	[ConversionRate] [numeric](6, 4) NOT NULL,
	[SupercedeDateKey] [int] NULL)
go

insert into RegionDimension ([RegionKey],Region,[ConversionRate],[SupercedeDateKey])
values (1,'UK',1,NULL),(2,'France',0.79,20140301),(3,'Japan',0.0055,20140601),(4,'France',0.81,20140801),(5,'France',0.85,NULL),(6,'Japan',0.0066,NULL)
go

What this means is that the exchange rate for Japan has changed once this year while France has changed a couple of times and its these 2 supercessions that are giving me grief. The Supercede date key has been used to show the date that that exchange rate was replaced. We assume that the new rate is effective from the following day.

What I want is to be able to show the start and end dates for each rate by Region, so we'll see something like:

Region | Start | End | Rate
UK | NULL | NULL | 1.0000
Japan | NULL | 20140601 | 0.0055
Japan | 20140602 | NULL | 0.0066
France | NULL | 20140301 | 0.7900
France | 20140302 | 20140801 | 0.8100
France | 20140802 | NULL | 0.8500

I've said it needs to be a recursive query but if there's a simpler way, then I'll be happy with that. I know you SQL folks love a nice challenge and I'd be very grateful for your help. I've been busting my brains on this all afternoon and I have to admit, I'm stuck.

Many thanks,
Nick

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2014-11-14 : 12:00:11

Try something like this:


SELECT RD.Region, S.Start, RD.SupercedeDateKey AS [End], RD.ConversionRate AS Rate
  FROM dbo.RegionDimension AS RD
       OUTER APPLY
       (SELECT TOP(1) S.SupercedeDateKey + 1 AS Start
          FROM dbo.RegionDimension AS S
         WHERE S.Region = RD.Region
           AND S.SupercedeDateKey < ISNULL(RD.SupercedeDateKey, 99999999)
         ORDER BY S.SupercedeDateKey DESC) AS S

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

theboyholty
Posting Yak Master

226 Posts

Posted - 2014-11-17 : 03:33:45

Thank you malpashaa. That is perfect!

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources