sql - Join a Query with another Query while using a common table expression -

i have following 2 queries:

query 1:

with jobtransactionssumtypes ( select        [job],       [cost_code],       sum(case when [transaction_type] = 'ap cost' , [commitment] <>''        [amount]-[noncosted_tax] else 0 end) apcostamountcomsum,       sum(case when [transaction_type] = 'ap cost' , [commitment] =''     [amount]-[noncosted_tax] else 0 end) apcostamountnoncomsum,       sum(case when [transaction_type] = 'approved est changes'             [amount]-[noncosted_tax] else 0 end) approvedestchangesamountsum,       sum(case when [transaction_type] = 'aprvd cmmtt cst chng'             [amount]-[noncosted_tax] else 0 end) aprvdcmmttcstchngamountsum,       sum(case when [transaction_type] = 'aprvd schdl val chn'          [amount]-[noncosted_tax] else 0 end) aprvdschdlvalchnamountsum,       sum(case when [transaction_type] = 'cash receipt'                 [amount]-[noncosted_tax] else 0 end) cashreceiptamountsum,       sum(case when [transaction_type] = 'committed cost'           [amount]-[noncosted_tax] else 0 end) committedcostamountsum,       sum(case when [transaction_type] = 'dollars paid'                 [amount]-[noncosted_tax] else 0 end) dollarspaidamountsum,       sum(case when [transaction_type] = 'general ledger only'          [amount]-[noncosted_tax] else 0 end) generalledgeronlyamountsum,       sum(case when [transaction_type] = 'jc cost'              [amount]-[noncosted_tax] else 0 end) jccostamountsum,       sum(case when [transaction_type] = 'misc worksheet 1'             [amount]-[noncosted_tax] else 0 end) miscworksheet1amountsum,       sum(case when [transaction_type] = 'misc worksheet 2'             [amount]-[noncosted_tax] else 0 end) miscworksheet2amountsum,       sum(case when [transaction_type] = 'misc worksheet 3'             [amount]-[noncosted_tax] else 0 end) miscworksheet3amountsum,       sum(case when [transaction_type] = 'misc worksheet 4'             [amount]-[noncosted_tax] else 0 end) miscworksheet4amountsum,       sum(case when [transaction_type] = 'original estimate'            [amount]-[noncosted_tax] else 0 end) originalestimateamountsum,       sum(case when [transaction_type] = 'pending est changes'          [amount]-[noncosted_tax] else 0 end) pendingestchangesamountsum,       sum(case when [transaction_type] = 'percent complete'             [amount]-[noncosted_tax] else 0 end) percentcompleteamountsum,       sum(case when [transaction_type] = 'pndng cmmtt cst chng'             [amount]-[noncosted_tax] else 0 end) pndngcmmttcstchngamountsum,       sum(case when [transaction_type] = 'pndng schd val chg 2'         [amount]-[noncosted_tax] else 0 end) pndngschdvalchg2amountsum,       sum(case when [transaction_type] = 'pndng schd val chg 3'         [amount]-[noncosted_tax] else 0 end) pndngschdvalchg3amountsum,       sum(case when [transaction_type] = 'pndng schdl val chng'             [amount]-[noncosted_tax] else 0 end) pndngschdlvalchngamountsum,       sum(case when [transaction_type] = 'pr cost'              [amount]-[noncosted_tax] else 0 end) prcostamountsum,       sum(case when [transaction_type] = 'receivable adjstment'             [amount]-[noncosted_tax] else 0 end) receivableadjstmentamountsum,       sum(case when [transaction_type] = 'retention billed'             [amount]-[noncosted_tax] else 0 end) retentionbilledamountsum,       sum(case when [transaction_type] = 'scheduled value'          [amount]-[noncosted_tax] else 0 end) scheduledvalueamountsum,       sum(case when [transaction_type] = 'work billed'              [amount]-[noncosted_tax] else 0 end) workbilledamountsum   [adcdata_doric].[dbo].[jct_current__transaction]    group       [job],       [cost_code] ) select     job,     cost_code,     apcostamountcomsum,     apcostamountnoncomsum,     approvedestchangesamountsum,     aprvdcmmttcstchngamountsum,     aprvdschdlvalchnamountsum,     cashreceiptamountsum,     committedcostamountsum,     dollarspaidamountsum,     generalledgeronlyamountsum,     jccostamountsum,     miscworksheet1amountsum,     miscworksheet2amountsum,     miscworksheet3amountsum,     miscworksheet4amountsum,     originalestimateamountsum,     pendingestchangesamountsum,     percentcompleteamountsum,     pndngcmmttcstchngamountsum,     pndngschdvalchg2amountsum,     pndngschdvalchg3amountsum,     pndngschdlvalchngamountsum,     prcostamountsum,     receivableadjstmentamountsum,     retentionbilledamountsum,     scheduledvalueamountsum,     workbilledamountsum,     (scheduledvalueamountsum) startcontractamount,     (aprvdschdlvalchnamountsum) approvedvariations,   (originalestimateamountsum + approvedestchangesamountsum + pendingestchangesamountsum) totalbudgetoriginalapprovedpending,   (originalestimateamountsum + approvedestchangesamountsum)*(miscworksheet1amountsum/100)+(pendingestchangesamountsum*(miscworksheet2amountsum/100)) totalcommitmentsbudget, (committedcostamountsum + aprvdcmmttcstchngamountsum + pndngcmmttcstchngamountsum) approvedpendingcommitedcosts     -- add additional calculations here.     [jobtransactionssumtypes] 

query 2:

select      apm_master__distribution.job      , apm_master__distribution.cost_code      ,sum(case when apm_master__distribution.onhold_status <>0                jct_current__transaction.amount-jct_current__transaction.noncosted_tax else 0 end) onholdamount      ,sum(case when apm_master__distribution.onhold_status =0                  jct_current__transaction.amount-jct_current__transaction.noncosted_tax else 0 end) notonholdamount   dbo.jct_current__transaction jct_current__transaction   left outer join dbo.apm_master__distribution apm_master__distribution     on jct_current__transaction.vendor = apm_master__distribution.vendor , jct_current__transaction.invoice = apm_master__distribution.invoice , jct_current__transaction.dist_sequence = apm_master__distribution.dist_seq group   apm_master__distribution.job , apm_master__distribution.cost_code 

i can join these in crystal reports, move single query through left outer joining (getting query 1 results) , linking through job , cost code query 2.

i suppose asking is, query 1 uses common table expression query 2 not, , i'm not sure how join them.

thanks.

you can use multiple cte in 1 query

simple example:

;with cte1 (  select *  table1  ), cte2 (   select *  table2  )  select *  cte1 c1 left join cte2 c2 on c1.id = c2.id  

your query multiple cte

;with jobtransactionssumtypes ( select        [job],       [cost_code],       sum(case when [transaction_type] = 'ap cost' , [commitment] <>''        [amount]-[noncosted_tax] else 0 end) apcostamountcomsum,       sum(case when [transaction_type] = 'ap cost' , [commitment] =''     [amount]-[noncosted_tax] else 0 end) apcostamountnoncomsum,       sum(case when [transaction_type] = 'approved est changes'             [amount]-[noncosted_tax] else 0 end) approvedestchangesamountsum,       sum(case when [transaction_type] = 'aprvd cmmtt cst chng'             [amount]-[noncosted_tax] else 0 end) aprvdcmmttcstchngamountsum,       sum(case when [transaction_type] = 'aprvd schdl val chn'          [amount]-[noncosted_tax] else 0 end) aprvdschdlvalchnamountsum,       sum(case when [transaction_type] = 'cash receipt'                 [amount]-[noncosted_tax] else 0 end) cashreceiptamountsum,       sum(case when [transaction_type] = 'committed cost'           [amount]-[noncosted_tax] else 0 end) committedcostamountsum,       sum(case when [transaction_type] = 'dollars paid'                 [amount]-[noncosted_tax] else 0 end) dollarspaidamountsum,       sum(case when [transaction_type] = 'general ledger only'          [amount]-[noncosted_tax] else 0 end) generalledgeronlyamountsum,       sum(case when [transaction_type] = 'jc cost'              [amount]-[noncosted_tax] else 0 end) jccostamountsum,       sum(case when [transaction_type] = 'misc worksheet 1'             [amount]-[noncosted_tax] else 0 end) miscworksheet1amountsum,       sum(case when [transaction_type] = 'misc worksheet 2'             [amount]-[noncosted_tax] else 0 end) miscworksheet2amountsum,       sum(case when [transaction_type] = 'misc worksheet 3'             [amount]-[noncosted_tax] else 0 end) miscworksheet3amountsum,       sum(case when [transaction_type] = 'misc worksheet 4'             [amount]-[noncosted_tax] else 0 end) miscworksheet4amountsum,       sum(case when [transaction_type] = 'original estimate'            [amount]-[noncosted_tax] else 0 end) originalestimateamountsum,       sum(case when [transaction_type] = 'pending est changes'          [amount]-[noncosted_tax] else 0 end) pendingestchangesamountsum,       sum(case when [transaction_type] = 'percent complete'             [amount]-[noncosted_tax] else 0 end) percentcompleteamountsum,       sum(case when [transaction_type] = 'pndng cmmtt cst chng'             [amount]-[noncosted_tax] else 0 end) pndngcmmttcstchngamountsum,       sum(case when [transaction_type] = 'pndng schd val chg 2'         [amount]-[noncosted_tax] else 0 end) pndngschdvalchg2amountsum,       sum(case when [transaction_type] = 'pndng schd val chg 3'         [amount]-[noncosted_tax] else 0 end) pndngschdvalchg3amountsum,       sum(case when [transaction_type] = 'pndng schdl val chng'             [amount]-[noncosted_tax] else 0 end) pndngschdlvalchngamountsum,       sum(case when [transaction_type] = 'pr cost'              [amount]-[noncosted_tax] else 0 end) prcostamountsum,       sum(case when [transaction_type] = 'receivable adjstment'             [amount]-[noncosted_tax] else 0 end) receivableadjstmentamountsum,       sum(case when [transaction_type] = 'retention billed'             [amount]-[noncosted_tax] else 0 end) retentionbilledamountsum,       sum(case when [transaction_type] = 'scheduled value'          [amount]-[noncosted_tax] else 0 end) scheduledvalueamountsum,       sum(case when [transaction_type] = 'work billed'              [amount]-[noncosted_tax] else 0 end) workbilledamountsum   [adcdata_doric].[dbo].[jct_current__transaction]   --where your_condition group       [job],       [cost_code] ), jobtransactionssumtypes_query2 (  select      apm_master__distribution.job      , apm_master__distribution.cost_code      ,sum(case when apm_master__distribution.onhold_status <>0                jct_current__transaction.amount-jct_current__transaction.noncosted_tax else 0 end) onholdamount      ,sum(case when apm_master__distribution.onhold_status =0                  jct_current__transaction.amount-jct_current__transaction.noncosted_tax else 0 end) notonholdamount    dbo.jct_current__transaction jct_current__transaction   left outer join dbo.apm_master__distribution apm_master__distribution     on jct_current__transaction.vendor = apm_master__distribution.vendor , jct_current__transaction.invoice = apm_master__distribution.invoice , jct_current__transaction.dist_sequence = apm_master__distribution.dist_seq  group   apm_master__distribution.job  ,apm_master__distribution.cost_code  )  select     job,     cost_code,     apcostamountcomsum,     apcostamountnoncomsum,     approvedestchangesamountsum,     aprvdcmmttcstchngamountsum,     aprvdschdlvalchnamountsum,     cashreceiptamountsum,     committedcostamountsum,     dollarspaidamountsum,     generalledgeronlyamountsum,     jccostamountsum,     miscworksheet1amountsum,     miscworksheet2amountsum,     miscworksheet3amountsum,     miscworksheet4amountsum,     originalestimateamountsum,     pendingestchangesamountsum,     percentcompleteamountsum,     pndngcmmttcstchngamountsum,     pndngschdvalchg2amountsum,     pndngschdvalchg3amountsum,     pndngschdlvalchngamountsum,     prcostamountsum,     receivableadjstmentamountsum,     retentionbilledamountsum,     scheduledvalueamountsum,     workbilledamountsum,     (scheduledvalueamountsum) startcontractamount,     (aprvdschdlvalchnamountsum) approvedvariations,   (originalestimateamountsum + approvedestchangesamountsum + pendingestchangesamountsum) totalbudgetoriginalapprovedpending,   (originalestimateamountsum + approvedestchangesamountsum)*(miscworksheet1amountsum/100)+(pendingestchangesamountsum*(miscworksheet2amountsum/100)) totalcommitmentsbudget, (committedcostamountsum + aprvdcmmttcstchngamountsum + pndngcmmttcstchngamountsum) approvedpendingcommitedcosts,     j2.onholdamount,     j2.notonholdamount     [jobtransactionssumtypes] j1 left outer join jobtransactionssumtypes_query2 j2       on j1.job = j2.job , j1.cost_code = j2.cost_code