CODEWITHSUNDEEP
sql-server
JohnnyJP
JohnnyJP

Reputation: 1246

Calling a function repeatedly and joining its output

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?

Upvotes: 0

Views: 41

Answers (2)

SteveC
SteveC

Reputation: 6015

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;

Upvotes: 0

Thom A
Thom A

Reputation: 95554

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;

Upvotes: 1

Related Questions