1

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))
SELECT *
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!

Community
  • 1
  • 1
Richard Griffiths
  • 758
  • 1
  • 11
  • 23

1 Answers1

1

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)
     )
SELECT *
FROM datesTbl dr CROSS APPLY
     dbo.MoveDateDiff(dr.Datevalue, DATEADD(day, 1, dr.DateValue), 14792) pm
WHERE DATEDIFF(Day, dr.dateValue, pm.MovementDate) <= 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786