Aggregate table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Aggregate table

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2010-04-20 : 07:12:36

Hi there,

I've recently been developing a financial report based on job costing for shop repairs. For example, a project will be to fix a ceiling and there can be many rows for material costs, labour costs for each project. there are 4 main tables that hold ledgers(C,D,E,A - Commited,Delivered,Retained and Actual costs)

Each invoice that is created will start in the C ledger and at the end it will be in the A as complete.

In the report there will be a period column which will show the state of each project on that period (How much was in each ledger for each project). This will be an aggregate for each project ledger

Because it gets complicated I'll cut a long story short:
The C,D,E ledgers are RE-built each night using procedures and put into thier corresponding ledger tables (A ledger is the last step where there is no movement) They are stamped with a period. Below is the C ledger table sample



ProjectCode Period  Ledger  Commited  Description
p01           201001    C       £100       Ceiling Tiles
p01           201001    C       £50        Electrical Tape

Because of this they will always have the current period. This is where it gets tricky. I've decided to run a job at the end of the month that will aggregate the total amounts for each ledger in each project for that period. I'll then store these results in a table. thus, a period can be selected for the report and the user can view the position the C ledger (or D and E) was in on that period:

Below is an example of the insert to the aggregate table for the C ledger


INSERT INTO dbo.JC_Ledger_Position --Aggregate table
	(
	posLDG_ID,
	posPeriod, 
	posProjectCode, 
	posCommited,
	posRetention,
	posDelivered,
	posActual
	)

	SELECT
		LDG_ID,
		PERIOD,
		ProjectCode,
		SUM(Commited) 'Commited', --£amount
		SUM(Retention)'Retention',
		SUM(Delivered)'Delivered',
		SUM(Actual) 'Actual'

	FROM 
		dbo.rptLedgerDailyC_CEP_T1 --Ledger table

	GROUP BY 	
		LDG_ID,--Ledger (c)
		PERIOD,
		ProjectCode

-dbo.rptLedgerDailyC_CEP_T1 is the C ledger table
-dbo.JC_Ledger_Position is the aggreagate table for each ledger

This is the part I'm not sure about. I want to include the aggregate table in the same dataset which means joining the LDG_ID, ProjectCode and Period. However, because the report shows the C,D and E ledgers with the current Period and the aggregate will eventually have previous periods recorded, only the current posPeriod will match with the ledger period. I was thinking of doing a LEFT OUTER JOIN with the aggregate table to get all my data back like so:

NOTE: prefix pos is Aggregate table


SELECT ProjectCode, posProjectCode,posCommited,Commited,Period, posPeriod, LDG_ID,posLDG_ID
FROM  
	--Start with the aggregate table to show all 
	dbo.JC_Ledger_Position P LEFT OUTER JOIN 
	dbo.rptLedgerDailyC_CEP_T1 T1 
	
		ON P.posLDG_ID = T1.LDG_ID
		AND P.posPERIOD = T1.PERIOD
		AND P.posProjectCode = T1.ProjectCode


Results:
Project	posProject  posCommited Commited  Period  posPeriod
NULL	P01	    117.9	NULL	  NULL	   2010003
NULL	P02	    22902.75	NULL	  NULL	   2010003
P02	P02	    275.03	31.2	  2010004  2010004
P02	P02	    275.03	20.64	  2010004  2010004
P02	P02	    275.03	20.64	  2010004  2010004

The above results show all data from both tables and it can be seen from the sample above that there aren't any records for 2010003 period in the C ledger table which is why there's NULLs in these fields. I could then use those results for my PERIOD column in the report.

Is this is a conventional way of doing things? Is there a better way? Perhaps a UNION etc. Any advice on the way I'm doing it would be much appreciated.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-04-20 : 11:21:19

Why are there separate ledgers? These four things are types of costs, not different entities, so they belong in the Costs table.

I would model {committed, delivered, retained, actual} as columns, then show the particular costs as VIEWs built on it. Then your aggregated totals are just a simple group by.

I think you are being fooled into modeling the Physical ledgers and not the Logical model under them.

CREATE TABLE Project_Costs
(project_nbr INTEGER NOT NULL,
item_code CHAR(25) NOT NULL,
report_period INTEGER NOT NULL,
PRIMARY KEY (project_nbr, item_code, report_period),
committed_cost DECIMAL (8,2) NOT NULL,
delivered_cost DECIMAL (8,2) NOT NULL,
retained_cost DECIMAL (8,2) NOT NULL,
actual_cost DECIMAL (8,2) NOT NULL,
-- business rule constraints here:
CONSTRAINT Within_Budget
CHECK (actual_costs <= committed_cost),
etc
);

Add the constraints you need, make VIEWs on each kind of cost and do a simple GROUP BY for the total project costs.

--CELKO--
Joe Celko, SQL Guru

cidr
Posting Yak Master

207 Posts

Posted - 2010-04-23 : 05:26:10

Hi jcelko, frist of all, thanks for responding. Perhaps I've not explained myself well hear.

I have no choice in the fact that the ledgers start in seperate tables as it's a third party product. Secondly I wanted to keep this short for reasons that it gets complicated:

There is a db that recieves transactions every hour within working hours (CEP). The following morning, the transactions are imported to a financial db that holds the 4 ledger tables.

Each night I rebuild three 'reporting' tables from the ledgers. one table holds the D,E and A from the current day's transactions.

Because the third party db is only imported once a day, I'll build another table with the A ledger and this will be all data prior to the current day. And the final table is the C ledger. This is a seperate table because the C ledger will be re-built every 30 minutes during the day with the data from CEP.

The plan is to UNION ALL the three tables for the report. However, I need to aggregate the periods. and the results will go into it's own table. This will take place at the end of the month before the period changes. This allows users to see the position at that period, specifically for the C,D and E.

I've decided to UNION ALL the aggregate table as well rather than joining by project and ledger etc


rptLedgerA_All --A ledger from third party app
UNION ALL 
rptLedgerAll_CEP --All ledgers from transactional db
UNION ALL 
rptLedgerDailyC_CEP_T1 --C ledger from transactional db
rptAGGR_Period --Aggregates of everything from transactional db

I am curious though about your idea of views. Do you mean instead of the aggregate table? I have to make a snap shot of the data every month and record the results somewhere as the C,D,E are moving targets and are always stamped with the current period.

Cheers
P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources