Calling a function repeatedly and joining its output

Question

I have 1 function given to me that takes parameters of @month and @year. The function returns a table like this: Assuming @month=9 and @year=2020

select * FROM dbo.TotalClicksForMonth(9,2020)
Partner,Percentage
Partner1,0.25
Partner2,0.5
Partner3,0.25

I have a scalar function that returns a float

select dbo.TotalSpendForMonth(9,2020)
100

If I run a select statement

 select dbo.TotalSpendForMonth(9,2020)*cp.PercentageClicks as percentsplit from dbo.TotalClicksForMonth(9,2020) as cp

This works, and I get an output like

Partner PercentageClicks percentsplit
Partner1    0.25    25
Partner2    0.50    50
Partner3    0.25    25

Taking the total amount and allocating it across all the rows in the right ratios.

Now, thats ok for Sept 2020 (9,2020). But I need the output in for all the months and years specified in a 3rd table

select datepart(MONTH,[date]) as mh,datepart(year,[date]) as yr
  from sales

I cant work out how to do this.

I tried

  select datepart(MONTH,sales.date) as mh,datepart(year,sales.date) as yr
  join (select dbo.TotalspendForMonth(sales.mh,sales.yr)*cp.PercentageClicks as percentsplit from dbo.TotalClicksForMonth(sales.mh,sales.yr) as cp) as xx on 1=1
  from sales
  group by datepart(MONTH,sales.date),datepart(year,sales.date)

But that doesnt work.

I think my options are to create a temporary table and then repeatedly in a cursor call the select statement to add the rows, but there must be a better way,

Can you help please?

I cant change the functions. I have to use them as they are. They need month and year passing to them, and return multiple rows, a list of partners that the total amount should be split over. The last select in my question is what I tried but doesnt execute, i think because theres no condition for the join to be on. I articicially used 1=1 for all rows, but it doesnt work. — JohnnyJP, Nov 19 '20 at 14:47
the number of times I need to call these functions is variable and is controlled by the dates in the sales table, but typically 10-20 months. — JohnnyJP, Nov 19 '20 at 14:49

score 1 · Accepted Answer · answered Nov 19 '20 at 14:48

1

I appears what you need to do is APPLY to the function:

SELECT DATEPART(MONTH,s.[date]) AS mh,
       DATEPART(year,s.[date]) AS yr,
       dbo.TotalSpendForMonth(DATEPART(MONTH,s.[date]),DATEPART(year,s.[date])) * cp.PercentageClicks AS percentsplit
FROM dbo.sales s
     CROSS APPLY dbo.TotalClicksForMonth(DATEPART(MONTH,s.[date]),DATEPART(year,s.[date])) cp;

If you prefer, you can derive the DATEPARTs in a VALUES table construct, so as not the repeat the expressions:

SELECT V.mh,
       V.yr,
       dbo.TotalSpendForMonth(V.mh,V.yr) * cp.PercentageClicks AS percentsplit
FROM dbo.sales s
     CROSS APPLY (VALUES(DATEPART(MONTH,s.[date]),DATEPART(year,s.[date])))V(mh,yr)
     CROSS APPLY dbo.TotalClicksForMonth(V.mh,V.yr) cp;

answered Nov 19 '20 at 14:48

Thom A

88,727
11
45
75

A step closer, but for 9,2020 theres now many rows (>100) instead of 3 – JohnnyJP Nov 19 '20 at 15:00
I don't understand you're point there, @JohnnyJP . If `sales` has multiple rows then it will call the function for each row, not just one of them. – Thom A Nov 19 '20 at 15:01
So I want to call select dbo.TotalSpendForMonth(9,2020)*cp.PercentageClicks as percentsplit from dbo.TotalClicksForMonth(9,2020) as cp – JohnnyJP Nov 19 '20 at 15:03
I want to call it for values defined in sales. so, (9,2020) (10,2020), (11,2020) and so on – JohnnyJP Nov 19 '20 at 15:03
So the number of lines when (9,2020) is called on its own, running that select is 3, if it calls other date ranges, the number in Sept. is still three. – JohnnyJP Nov 19 '20 at 15:04
Can I add a Group by to make sure that each date in sales is only used once?, Looks like I have several dates in September in the file. – JohnnyJP Nov 19 '20 at 15:06
*"I want to call it for values defined in sales. so, (9,2020) (10,2020), (11,2020) and so on"* That's what is *is* doing... `dbo.TotalClicksForMonth(DATEPART(MONTH,s.[date]),DATEPART(year,s.[date]))` Notice the `s.[date]` – Thom A Nov 19 '20 at 15:08
*"Can I add a Group by to make sure that each date in sales is only used once?, Looks like I have several dates in September in the file."* Use a subquery or CTE to limit the results first (you likely don't want a `GROUP BY` but a "last row in each group", where there are literally [100's of](https://stackoverflow.com/q/4751913/2029983) [duplicates](https://stackoverflow.com/q/6841605/2029983) out there on [so]), and then call the functions (like I have) in the outer/final `SELECT`. – Thom A Nov 19 '20 at 15:09

score 0 · Answer 2 · answered Nov 19 '20 at 15:09

To calculate the click percentages you could CROSS APPLY the table valued function. To summarize across MONTH and YEAR you could use the SUM aggregate function and GROUP BY. Something like this.

select dt.mo, dt.yr,
       sum(dbo.TotalSpendForMonth(dt.mo, dt.yr)*cp.PercentageClicks) total_amount
from dbo.sales s
     cross apply (select datepart(month,s.[date]) as mo,
                         datepart(year,s.[date]) as yr) dt
     cross apply dbo.TotalClicksForMonth(dt.mo, dt.yr) cp
group by dt.mo, dt.yr
order by dt.yr, dt.mo;

Calling a function repeatedly and joining its output

2 Answers2