0 Replies Latest reply: Dec 13, 2012 11:25 AM by Alex Peasley RSS

    SQL Temp table Query

      Whats the best way to handle this query in sql? I have two previous queries that I union together and forms a temp table. Now this query takes the temp table then joins to more tables for the final result.

       

      The issue is that the last query has multiple dimensions that look at the temp and the newly joined tables like below. I just can't wrap my head around the best way to handle this.

       

      Should I create multiple different load statments and concatanate them together? And then join the two temp tables together.

       

       

      case

                when b.PAProjectType = 1 then case when a.pabllngtype = 1 then a.BillRate * a.quantity else c.PAFFProfitAmount * a.quantity end

                else c.paffprofitamount * a.quantity

      END as StdRevenue,

       

       

      from @projDetails a

      left outer join BWIT..PA01201 b on a.paprojnumber = b.paprojnumber

      left outer join BWIT..RM00101 rm on rm.CUSTNMBR = b.CUSTNMBR

      left outer join BWIT..UPR00100 upr on upr.EMPLOYID = a.employid

      left outer join BWIT..PA00601 c on upr.employid = c.employid

      left outer join GPCustom..BacklogReportBranches brb on brb.Department = upr.DEPRTMNT

      left outer join GPCustom..Regions reg on reg.RegionID = brb.RegionID

      where PADT between @startDate and @endDate

      and b.paprojnumber like @projNum + '%'

       

       

      Any guidance would be helpful. The full stored procedure is below.