March 31, 2008 at 11:26 am
I have a query that does a comparison between two tables for indifferences. The two tables are a one to one relationship. thus it is a tick for a tie.
Now to add the monkey wrench, there is now a one to many relationships between the tables, I am trying to figure out the best way for the query to keep its structure and also have a CASE situation when the situation is one to many relationship
Would a CURSOR need to be done? CASE statement? I would like to have the SELECT statement remain intact and then have like an IF/THEN when a.component_id = '8933' and a.cost_element_id LIKE '____3'
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 = @posting_yr AND
a.amt_class_type = @amt_class_type AND
c.curr_type = 'B1' AND
c.amt_class_type = @amt_class_type AND
c.processing_yr = @posting_yr 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
March 31, 2008 at 11:30 am
I'm not clear on what it is you're trying to accomplish.
Without the tables, and some sample data, I have to say at a first glance that the query looks pretty standard and should work regardless of whether it's a 1:1 or 1:many relationship.
(By the way, I think the word you're looking for is "differences". I'm not sure how one would go about comparing "indifferences", since "indifference" usually means "lacking in care or concern".)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 11:34 am
The DDL for the tables, some sample data, and the expected output based on the sample data would be helpful.
😎
March 31, 2008 at 11:53 am
Thanks for the quick response. Attached is the report that is stemmed off the query. Hopefully this will give a clearer picture.
With the example shown. 01023 8933 76011 shows a total of Zero. But the ledger account associated with 01023 8933 76011 shows 4717.00.
This is because component_id 8933 can have two types of dbs_transaction_type (10,30). The current report assumes an out of balance because the current query is comparing
01023 8933 76011 with 7601 10 00 731 177 01023 8933
when it needs to associate with
01023 8933 76011 with 7601 10 00 731 177 01023 8933 AND 7601 10 30 731 177 01023 8933
I need to say
when component_id = '8933' compare with dbs_transaction_type IN ('00','30')
Again this is intense because I am trying to simplify it with stripping out the accounting aspect...
Sorry if you need more info let me know.
March 31, 2008 at 12:42 pm
More info..the more I look at this..the more I just don't want to do it! 😉
Let me try this again
One table has this:
01001 9696 76011 $50.00
Table two has this:
76011 01001 9696 10 $25.00
76011 01001 9696 30 $25.00
I need Table #1 total to sum total against table two grouped by 76011, 01001, 9696
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply