Thanks, you can tell its Monday as I am going about this backwards...thanks again

Jeff


-----Original Message-----
From: DSymanowicz [mailto:mssqldba-ezmlmshield-x60209819.[Email address protected]
Sent: Monday, March 31, 2008 3:28 PM
To: LazyDBA Discussion
Subject: Re: Adding a condition to an existing SELECT query

You could add :

AND (
(a.component_id <> '8933')
OR (c.dbs_transaction_type = b.dbs_transaction_type OR
c.dbs_transaction_type = '10' )
)

but you may need to take a look at the execution plan. Sometimes those
multi-OR criteria can be a performance problem if you don't write it in
the most efficient way.






"Sheldon Jeff " <mssqldba-ezmlmshield-x8677617.[Email address protected]
03/31/2008 02:19 PM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Adding a condition to an existing SELECT query






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



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page