Adrian Reszka Adrian Reszka - 12 days ago 11
SQL Question

SQL Server: run function for each row based on the provided row value

I am still learning SQL and I was wandering how can I pass a row value into a function in a

cross apply
query.

Assuming that this is my first table

SELECT
[Project], [emp_id],
[Allocation_start], [Allocation_end]
FROM
First_table


I have a function that takes 2 arguments - start and end date, and returns dates split by the week intervals...

Example :

select * from [udf_GetIntervals]('2017-01-01','2017-01-30')


Data:

dt_start dt_end
----------------------
2016-12-26 2017-01-01
2017-01-02 2017-01-08
2017-01-09 2017-01-15
2017-01-16 2017-01-22
2017-01-23 2017-01-29
2017-01-30 2017-02-05


What I did was I run the mentioned function with dates starting from the 1st July to the 30th December and stored it in virtual table and then used cross apply with that table.

select *
from [First_table]
cross apply
(select * from #temp) b


Which works, but it works independently of the
first_tables
start and end date always returning all weeks per 1 project record from first table.

I was wondering, how can i do this, using first_tables start_date and end_date values, so that my cross apply returns only records with the week_intervals contained in the rows start/end date.

I would appreciate any hints on the matter.

Answer

You can use APPLY do perform row-wise actions. CROSS APPLY behaves similar to an INNER JOIN, while OUTER APPLY is rather like a LEFT JOIN.

A Table-Valued-Function returns a table. You must provide an alias and inlcude this resultset into your column list:

SELECT [Project]
      ,[emp_id]
      ,[Allocation_start]
      ,[Allocation_end]
      ,WeekDates.*
From First_table
CROSS APPLY dbo.[udf_GetIntervals]([Allocation_start],[Allocation_end]) AS WeekDates