April 2, 2008 at 9:55 am
Not sure how to achieve this.
I have data from an output from a query that looks like this:
Project Ledger
01023 8933 76011 0.00 7601 10 30 01023 8933 4717.00
01023 8933 76013 4717.00 7601 10 30 01023 8933 4717.00
The data compares the Project vs Ledger amounts for a one to one relationship.
I want to sum up the 7601x cost elements on the Project side to match the one account string on the Ledger side
So it would look like this:
Project Ledger
01023 8933 76011/76013 4717.00 7601 10 30 01023 8933 4717.00
Here is the script, also attached is the file output.
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
LEFT OUTER JOIN DBSglep.dbo.ldr_acct_bal as c
ON
a.project_id = c.dbs_proj_code AND
a.component_id = c.dbs_funding_source AND
LEFT(a.cost_element_id,4) = c.dbs_gl_account
WHERE
a.curr_type = 'P' AND
a.fiscal_year = '2008' AND
a.amt_class_type = 'ACTUAL' AND
a.component_id = '8933' AND
c.curr_type = 'B1' AND
c.amt_class_type = 'ACTUAL' AND
c.processing_yr = '2008'
GROUP BY
a.project_id,a.component_id,a.cost_element_id,a.project_ytd_amt,c.dbs_proj_code,c.dbs_funding_source,
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
ORDER BY
a.project_id
April 2, 2008 at 10:19 am
It's hard to test without sample data, but I think what you want can be done with windowed functions ...
Specifically, the SUM(...) OVER (PARTITION BY ...)
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)
OVER (PARTITION BY c.dbs_proj_code, c.dbs_funding_source, c.dbs_gl_account)
)), 99999.99) AS ProjVariance
FROM
DBSproj.dbo.component_monetary_balance AS a
LEFT OUTER JOIN DBSglep.dbo.ldr_acct_bal as c
ON a.project_id = c.dbs_proj_code
AND a.component_id = c.dbs_funding_source
AND LEFT(a.cost_element_id, 4) = c.dbs_gl_account
WHERE
a.curr_type = 'P'
AND a.fiscal_year = '2008'
AND a.amt_class_type = 'ACTUAL'
AND a.component_id = '8933'
AND c.curr_type = 'B1'
AND c.amt_class_type = 'ACTUAL'
AND c.processing_yr = '2008'
GROUP BY
a.project_id
,a.component_id
,a.cost_element_id
,a.project_ytd_amt
,c.dbs_proj_code
,c.dbs_funding_source
,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
ORDER BY
a.project_id
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 2, 2008 at 11:03 am
Hi Jason,
This is what I want to accomplish if you can streamline it as I always over complicate my T-SQL...
/* Retrieve all data for component 8933 */
SELECT
a.project_id AS Project,
a.component_id AS Component,
LEFT (a.cost_element_id,4) AS 'CE',
SUBSTRING (a.cost_element_id,5,1) AS 'CE_End',
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
INTO #temp
FROM
DBSproj.dbo.component_monetary_balance AS a
LEFT OUTER JOIN DBSglep.dbo.ldr_acct_bal as c
ON
a.project_id = c.dbs_proj_code AND
a.component_id = c.dbs_funding_source AND
LEFT(a.cost_element_id,4) = c.dbs_gl_account
WHERE
a.curr_type = 'P' AND
a.fiscal_year = '2008' AND
a.amt_class_type = 'ACTUAL' AND
a.component_id = '8933' AND
c.curr_type = 'B1' AND
c.amt_class_type = 'ACTUAL' AND
c.processing_yr = '2008'
GROUP BY
a.project_id,a.component_id,a.cost_element_id,a.project_ytd_amt,c.dbs_proj_code,c.dbs_funding_source,
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
ORDER BY
a.project_id
/* Strip cost element 5th character, sum up amt by Project, Component, CE, place in new temp table */
SELECT Project,Component,CE,SUM(ProjYTD)AS 'SUMProjYTD'
INTO #projtemp
FROM #temp
GROUP BY Project, Component,CE
/* Place Ledger accounts and amounts in new temp table */
SELECT DISTINCT Acct,Sub,NAT,TT,RC,Line,Proj,Source,TBD,LdrYTD
INTO #ldrtemp
FROM #temp
GROUP BY Acct, Sub,NAT,TT,RC,Line,Proj,Source,TBD,LdrYTD
/* Compare the two new table table amounts for variances */
SELECT a.*,b.*,ISNULL((a.SUMProjYTD - b.LdrYTD),99999.99) AS Variance
FROM #projtemp a INNER JOIN #ldrtemp b
ON a.Project = b.Proj AND
a.Component = b.Source AND
a.CE = b.Acct
ORDER BY a.Project
/* Drop temp tables */
DROP TABLE #temp
DROP TABLE #projtemp
DROP TABLE #ldrtemp
April 2, 2008 at 11:39 am
1. Swap your tables around the LEFT JOIN clause.
2. Use the OVER .. PARTITION BY like I mentioned earlier...
Create some sample data
DECLARE @project TABLE (project_id CHAR(5), component_id CHAR(5), cost_element_id CHAR(5), project_ytd_amt MONEY)
DECLARE @ledger TABLE (dbs_gl_account CHAR(4), dbs_prod_line CHAR(2), dbs_responsibility_center CHAR(2)
,dbs_proj_code CHAR(5), dbs_funding_source CHAR(5), ldr_amt_1 MONEY)
INSERT @project
SELECT '01023','8933','76011', 0 UNION ALL
SELECT '01023','8933','76013', 4717.00 UNION ALL
SELECT '01099','9903','76021', 2000.00 UNION ALL
SELECT '01099','9903','76023', 1900.00
INSERT @ledger
SELECT '7601','10','30','01023','8933', 4717.00 UNION ALL
SELECT '7602','10','30','01099','9903', 4000.00
The Query
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_responsibility_center AS RC,
c.dbs_prod_line AS Line,
c.dbs_proj_code AS Proj,
c.dbs_funding_source AS Source,
ISNULL (SUM(c.ldr_amt_1), 99999.99) AS LdrYTD,
ISNULL ((SUM(a.project_ytd_amt) OVER (PARTITION BY a.project_id, a.component_id) - (SUM(c.ldr_amt_1))),99999.99) AS ProjVariance
FROM @ledger AS c
LEFT JOIN @project AS a
ON a.project_id = c.dbs_proj_code
AND a.component_id = c.dbs_funding_source
AND LEFT(a.cost_element_id,4) = c.dbs_gl_account
GROUP BY
a.project_id,
a.component_id,
a.cost_element_id,
a.project_ytd_amt,
c.dbs_gl_account,
c.dbs_responsibility_center,
c.dbs_prod_line,
c.dbs_proj_code,
c.dbs_funding_source
The results
Project Component CE ProjYTD Acct RC Line Proj Source LdrYTD ProjVariance
------- --------- ----- --------------------- ---- ---- ---- ----- ------ --------------------- ---------------------
01023 8933 76011 0.00 7601 30 10 01023 8933 4717.00 0.00
01023 8933 76013 4717.00 7601 30 10 01023 8933 4717.00 0.00
01099 9903 76021 2000.00 7602 30 10 01099 9903 4000.00 -100.00
01099 9903 76023 1900.00 7602 30 10 01099 9903 4000.00 -100.00
Is this what you're trying to get to?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 2, 2008 at 12:22 pm
Hi Jason,
Again thanks for the help...
The script I copied does work, I read up on the OVER clause and not sure how to incorporate it.
Project Component CE ProjYTD Acct RC Line Proj Source LdrYTD ProjVariance
------- --------- ----- --------------------- ---- ---- ---- ----- ------ --------------------- ---------------------
01023 8933 76011 0.00 7601 30 10 01023 8933 4717.00 0.00
01023 8933 76013 4717.00 7601 30 10 01023 8933 4717.00 0.00
01099 9903 76021 2000.00 7602 30 10 01099 9903 4000.00 -100.00
01099 9903 76023 1900.00 7602 30 10 01099 9903 4000.00
Ok here it goes
cost elements ending in '1' and '3' have the same ledger string
thus 01023 8933 76011 and 01023 8933 76013 = 7601 30 10 01023 8933
So I want to sum up 76011 and 76013 amounts and compare it to the 7601 30 10 01023 8933 amount
The output from your PARTITION is not giving me the results I need. I am not going to be able to get one row of output for two cost elements, so trim off the last character so that you can join the ledger account (76011 becomes 7601 which then mapps to Ledger 7601)
Before I go further in detail let me know if you grasp what I am talking about.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply