Richard Griffiths Richard Griffiths - 1 year ago 53
SQL Question

Can't access CTE via inner join SQL Server

I know I'm missing something obvious but it's not so obvious to me!

I've got a table valued function that produces a nice interval range of dates given a start, end, interval (thanks to another SO answer!).

I've another TVF that produces the latest part transaction given a date.

However, I was after being able to produce the last parts transaction in a series of dates lying between the start and end dates given. So, given March to May and an interval of say, 2 days, I'd get a sort of time series between the two.

However, I've hit a wall now with CTE's and was trying to avoid going into procedural/cursor style looping to do this.

This is the code:

WITH datesTbl(DateValue)
AS (SELECT DateValue
FROM [dbo].[DateRange]('2016-03-18', '2016-04-27', 1))
FROM datesTbl dr
INNER JOIN dbo.MoveDateDiff(dr.Datevalue, DATEADD(day, 1, dr.DateValue), 14792) pm
ON DATEDIFF(Day, dr.dateValue, pm.MovementDate) <= 1;

I know I've other conceptual errors in the underlying TVF's however here I'm wanting to find a way past the fact I can't seem to access the CTE in the first part of the Inner Join statement (there is no syntax error after the ON declaration!).

Any guidance would be gratefully received!

Answer Source

When you use a TVF, you want APPLY, not JOIN:

WITH datesTbl(DateValue) as (
      SELECT DateValue
      FROM [dbo].[DateRange]('2016-03-18', '2016-04-27', 1)
     dbo.MoveDateDiff(dr.Datevalue, DATEADD(day, 1, dr.DateValue), 14792) pm
WHERE DATEDIFF(Day, dr.dateValue, pm.MovementDate) <= 1;