Complex Queries in SQL Server 2008
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Complex Queries in SQL Server 2008

  1. #1
    Join Date
    Apr 2005
    Posts
    1,750

    Complex Queries in SQL Server 2008

    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

  2. #2
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,354

    Re: Complex Queries in SQL Server 2008

    Did you consider to create table-valued functions?
    Victor Nijegorodov

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center