The query below is a comparison of data from two tables from two databases (component_monetary_balance and ldr_acct_bal)

I am trying to wrap around my head how I can incorporate a specific condition to the query within the result set of the SELECT.

The SELECT is 'select the accounts of one table and compare to the values linked to the other table'. But we have one condition where
when a.component_id = '8933' then c.dbs_transaction_type = b.dbs_transaction_type OR c.dbs_transaction_type = '10'

What is the best way to incorporate the SELECT for all and this exception?

SELECT
a.project_id AS Project,
a.component_id AS Component,
a.cost_element_id AS CE,
a.project_ytd_amt AS ProjYTD,
c.dbs_gl_account AS Acct,
c.dbs_sub_account AS Sub,
c.dbs_net_asset_type AS NAT,
c.dbs_transaction_type AS TT,
c.dbs_responsibility_center AS RC,
c.dbs_prod_line AS Line,
c.dbs_proj_code AS Proj,
c.dbs_funding_source AS Source,
c.dbs_tbd AS TBD,
ISNULL (SUM(c.ldr_amt_1 + c.ldr_amt_2 + c.ldr_amt_3 + c.ldr_amt_4 + c.ldr_amt_5 + c.ldr_amt_6 + c.ldr_amt_7 +
c.ldr_amt_8 + c.ldr_amt_9 + c.ldr_amt_10 + c.ldr_amt_11 + c.ldr_amt_12), 99999.99) AS LdrYTD,
ISNULL ((a.project_ytd_amt - (SUM(c.ldr_amt_1 + c.ldr_amt_2 + c.ldr_amt_3 + c.ldr_amt_4 + c.ldr_amt_5 + c.ldr_amt_6 + c.ldr_amt_7 +
c.ldr_amt_8 + c.ldr_amt_9 + c.ldr_amt_10 + c.ldr_amt_11 + c.ldr_amt_12))),99999.99) AS ProjVariance
FROM
DBSproj.dbo.component_monetary_balance AS a
INNER JOIN DBSproj.dbo.component_cost_element AS b
ON a.project_id = b.project_id
and a.component_id = b.component_id
and a.cost_element_id = b.cost_element_id
INNER JOIN DBSglep.dbo.ldr_acct_bal AS c
ON c.dbs_gl_account = b.dbs_gl_account
and c.dbs_sub_account = b.dbs_sub_account
and c.dbs_net_asset_type = b.dbs_net_asset_type
and c.dbs_transaction_type = b.dbs_transaction_type
and c.dbs_responsibility_center = b.dbs_responsibility_center
and c.dbs_prod_line = b.dbs_prod_line
and c.dbs_proj_code = b.dbs_proj_code
and c.dbs_funding_source = b.dbs_funding_source
and c.dbs_tbd = b.dbs_tbd
WHERE
a.curr_type = 'P' AND
a.fiscal_year = '2008' AND
a.amt_class_type = 'ACTUAL' AND
c.curr_type = 'B1' AND
c.amt_class_type = 'ACTUAL' AND
c.processing_yr = '2008' AND
NOT a.cost_element_id like '8499%' AND
NOT a.cost_element_id like '7012%' AND
NOT a.cost_element_id like '8001%'
GROUP BY
a.project_id,a.component_id,a.cost_element_id,a.project_ytd_amt,c.dbs_gl_account,c.dbs_sub_account,
c.dbs_net_asset_type,c.dbs_transaction_type,c.dbs_responsibility_center,c.dbs_prod_line,
c.dbs_proj_code,c.dbs_funding_source,c.dbs_tbd
HAVING
a.project_ytd_amt <> SUM(c.ldr_amt_1 + c.ldr_amt_2 + c.ldr_amt_3 + c.ldr_amt_4 + c.ldr_amt_5 + c.ldr_amt_6 +
c.ldr_amt_7 + c.ldr_amt_8 + c.ldr_amt_9 + c.ldr_amt_10 + c.ldr_amt_11 + c.ldr_amt_12)
GO

Jeff


MS Sql Server LazyDBA home page