June 29, 2004 at 5:57 am
This is more of a logic check as I have a solution BUT is it a good one? I am wondering whether I should just use some type of JOIN but I can't find a JOIN (or combination of JOINS) that will deliver so I worked on a UNION solution. The key issue is the number and type of one-to-many and pseudo one-to-one relationships that exist in the simple 3 table setup.
Below is a full dump of the problem along with all the SQL to create the tables with sample data so you can play around and see for yourself.
Objective:
----------------------
Be able to report upon a particular account and detail its budget and transactions for any particular month ... simple?
Overview
----------------------
I have the following set-up (derived from MS Great Plains Dynamics Accounting package) - if you are interested I will detail the underlying table names but its not strictly relevant.
Relationships
-----------
Well this is where it might get interesting:
R1 = FinAccount.AccIndex (1-Many) FinBudget.AccIndex
R2 = FinAccount.AccIndex (1-Many) FinTrans.AccIndex
R3 = Well see if you understand the following:
I need to match not only the relevant Account Budget with the appropriate Account Transactions but a Budget/Trans relates to a specific Year and Month so you get:
FinBudget.AccIndex = FinTrans.AccIndex
FinBudget.YearID = FinTrans.YearID
FinBudget.MonthID = FinTrans.MonthID
CUT & PASTE into Query Analyser or something
[AccIndex] [int] NOT NULL ,
[RegionCode] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[CostCentreCode] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[CostTypeCode] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
[AccIndex] [int] NOT NULL ,
[YearID] [smallint] NOT NULL ,
[MonthID] [tinyint] NOT NULL ,
[BudgetAMT] [decimal](19, 5) NOT NULL
) ON [PRIMARY]
GO
[AccIndex] [int] NOT NULL ,
[YearID] [smallint] NULL ,
[MonthID] [tinyint] NOT NULL ,
[TransAMT] [decimal](19, 5) NULL
) ON [PRIMARY]
GO
INSERT INTO FinAccount VALUES ('2', '10', '5420', '2')
INSERT INTO FinAccount VALUES ('3', '10', '5420', '3')
INSERT INTO FinAccount VALUES ('4', '10', '5420', '4')
GO
INSERT INTO FinTrans VALUES ('3', '2004', '1', '3000')
INSERT INTO FinTrans VALUES ('4', '2004', '1', '50')
INSERT INTO FinTrans VALUES ('4', '2004', '1', '75')
GO
INSERT INTO FinBudget VALUES ('2', '2004', '1', '1000')
INSERT INTO FinBudget VALUES ('4', '2004', '1', '150')
GO
You should get the following:
AccIndex RegionCode CostCentreCode CostTypeCode
----------- ---------- -------------- ------------
1 10 5420 1
2 10 5420 2
3 10 5420 3
4 10 5420 4
AccIndex YearID MonthID BudgetAMT
----------- ------ ------- ---------------------
1 2004 1 500.00000
2 2004 1 1000.00000
4 2004 1 150.00000
AccIndex YearID MonthID TransAMT
----------- ------ ------- ---------------------
1 2004 1 1000.00000
3 2004 1 3000.00000
4 2004 1 50.00000
4 2004 1 75.00000
UNION Solution
===============================================
(SELECT FinAccount.AccIndex,
FinAccount.RegionCode,
FinAccount.CostCentreCode,
FinAccount.CostTypeCode,
FinTrans.YearID,
FinTrans.MonthID,
FinBudgetAMT = Null,
FinTrans.TransAMT
FROM FinAccount INNER JOIN FinTrans ON
FinAccount.AccIndex = FinTrans.AccIndex)
UNION
(SELECT FinAccount.AccIndex,
FinAccount.RegionCode,
FinAccount.CostCentreCode,
FinAccount.CostTypeCode,
FinBudget.YearID,
FinBudget.MonthID,
FinBudget.BudgetAMT,
TransAMT = Null
FROM FinAccount INNER JOIN FinBudget ON
FinAccount.AccIndex = FinBudget.AccIndex)
AccIndex RegionCode CostCentreCode CostTypeCode YearID MonthID FinBudgetAMT TransAMT
----------- ---------- -------------- ------------ ------ ------- --------------------- ---------------------
1 10 5420 1 2004 1 NULL 1000.00000
1 10 5420 1 2004 1 500.00000 NULL
2 10 5420 2 2004 1 1000.00000 NULL
3 10 5420 3 2004 1 NULL 3000.00000
4 10 5420 4 2004 1 NULL 50.00000
4 10 5420 4 2004 1 NULL 75.00000
4 10 5420 4 2004 1 150.00000 NULL
Final Solution (Group)
===============================================
SELECT AccIndex, RegionCode, CostCentreCode, CostTypeCode, YearID, MonthID, SUM(FinBudgetAMT) AS BudgetMonth, SUM(TransAMT) AS TransMonth
FROM dbo.[UNION]
GROUP BY AccIndex, RegionCode, CostCentreCode, CostTypeCode, YearID, MonthID
AccIndex RegionCode CostCentreCode CostTypeCode YearID MonthID BudgetMonth TransMonth
----------- ---------- -------------- ------------ ------ ------- ------------- --------------
1 10 5420 1 2004 1 500.00000 1000.00000
2 10 5420 2 2004 1 1000.00000 NULL
3 10 5420 3 2004 1 NULL 3000.00000
4 10 5420 4 2004 1 150.00000 125.00000
June 29, 2004 at 9:18 am
You've unioned the data which, if you want to return a dataset, is the right thing to do. If you wanted a summation of the data you have to sum it and include the columns not specified in the select clause in the group by clause, which AccIndex=4 illustrates perfectly.
You can also insert the data into a temporary table and then select it along with your summations and group by clause.
Max
June 30, 2004 at 7:06 am
Hi,
If you want to use joins, then try this
SELECT A.AccIndex, A.RegionCode, A.CostCentreCode, A.CostTypeCode, B.YearID, B.MonthID, B.TotalBudget, B.TotalActual
FROM dbo.FinAccount A LEFT OUTER JOIN
(SELECT COALESCE (Budget.AccIndex, Actual.AccIndex) AS AccIndex, COALESCE (Budget.YearID, Actual.YearID) AS YearID,
COALESCE (Budget.MonthID, Actual.MonthID) AS MonthID, Budget.TotalBudget, Actual.TotalActual
FROM (SELECT AccIndex, YearID, MonthID, SUM(BudgetAMT) AS TotalBudget
FROM dbo.FinBudget B
GROUP BY AccIndex, YearID, MonthID) Budget FULL OUTER JOIN
(SELECT AccIndex, YearID, MonthID, SUM(TransAMT) AS TotalActual
FROM dbo.FinTrans T
GROUP BY AccIndex, YearID, MonthID) Actual ON Budget.AccIndex = Actual.AccIndex AND Budget.YearID = Actual.YearID AND
Budget.MonthID = Actual.MonthID) B ON A.AccIndex = B.AccIndex
basic theory is to sum up budget and actual separately first, then join the aggregations using full outer joins
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply