I have a complex query. What I want is instead of storing the results of first 2 select statement in temporary tables I want to store it in some user defined functions and then call these 2 functions in the final query to get the result. How can I accompalish this?


Code:
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL
	DROP TABLE #tmp2
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL
	DROP TABLE #tmp1
	
SELECT DISTINCT  gcid, company, name AS Area
INTO #tmp1
FROM Global_Customers 
INNER JOIN areas_national  AS AN ON AN.arid = Global_Customers.arid_ptr
INNER JOIN job_headers ON gcid = cust_ptr 
WHERE customer_type = 0

UNION ALL
SELECT DISTINCT  gcid, company, 'No Area Defined' AS Area
FROM Global_Customers 

INNER JOIN job_headers ON gcid = cust_ptr 
WHERE customer_type = 0
and Global_Customers.arid_ptr=-1

Select  gcid, company,Area,COUNT(Distinct sid) as countsid
INTO #tmp2
from #tmp1
INNER JOIN sites_national AS SN ON SN.gcid_ptr = gcid
GROUP BY gcid, company,Area

Select  gcid  AS [Customer Number], company,Area,countsid AS [Total Sites], SUM(job_headers_acs.amtNET) AS [Net Turn Over] 
,COUNT(DISTINCT job_threads.jid_ptr) AS [Job Count]
FROM #tmp2
INNER JOIN job_headers ON gcid = cust_ptr 
INNER JOIN job_headers_acs ON gcid_si = gcid AND job_headers_acs.jid=job_headers.jid
INNER JOIN job_threads ON job_threads.jid_ptr = job_headers.jid
WHERE date_booked BETWEEN '18/04/2013 00:00:00' AND '19/04/2013 23:59:00' AND thread_status = 4 AND Component_Status = 5
GROUP BY gcid, company,Area,countsid