Friday, March 23, 2012

Help with a subquery join

I have a query that produces the following results:

wbs2 wbs3 Name AmtBud BTD BillExt
100 100 Traffic Impact 10000 13563.75 0
140 Highway/Signal 0 0 0
140 100 Highway/Signal 0 0 0

and another query that produces the follwing results:

wbs2 FeeType
100 SC
140 HM&SC
150 HM
160 HM&SC
180 SC
190 SC

So, I want to join both queries to produce the ending result:

wbs2 wbs3 Name AmtBud BTD BillExt FeeType
100 100 Traffic Impact 10000 13563.75 0 SC
140 Highway/Signal 0 0 0 HM&SC
140 100 Highway/Signal 0 0 0 HM&SC

Here are my queries:
first query:

SELECT CASE WHEN WM_Template.WBS2 = '500' THEN '1750.00' + WM_Template.WBS2 ELSE '2000.20' + WM_Template.WBS2 END AS WBS2,
WM_Template.WBS3 AS WBS3,
CASE WHEN WM_Template.WBS2 = '260' THEN 'Pre Con Mtg / Inspections/ Punch List' WHEN WM_Template.WBS2 = '250' THEN 'All Environmental Permits & Approvals'
WHEN WM_Template.WBS2 = '150' THEN 'Project Admin / Clerical / Status Reports ' ELSE WM_Template.LongName END AS LongName,
(ISNULL(LB.AmtBud, 0) + ISNULL(EB.AmtBud, 0)) AS AmtBud, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period <= '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BTD, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period = '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BillExt
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND WM_Template.WBS2 <> '210' AND pr.wbs1 = '001-298'
GROUP BY WM_Template.WBS2, WM_Template.WBS3, WM_Template.LongName, lb.amtbud, eb.amtbud

second query

SELECT WM_Template.WBS2, CASE WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0')
THEN 'HM & SC' WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN 'HM' WHEN (SUM(isnull(LB.AmtBud, 0))
= '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'SC' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0')
THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') THEN 'SC' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0))
= '0') THEN (CASE WHEN (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND
(SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') AND (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0')
THEN 'HM & SC' END) ELSE 'N/A' END AS FeeType
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND pr.wbs1 = '001-333'
GROUP BY WM_Template.WBS2

Any suggestions would be helpful.

Thank You.Generically, you could do this:

SELECT <columns>
FROM
<Subquery1> q1
JOIN
<Subquery2> q2
on q1.Key = q2.Key

It may be more efficient to combine the logic of the two queries but I haven't looked to see how practical that would be.|||Thanks for your suggestion it helped me tremendously.

-Laura

No comments:

Post a Comment