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.
