May 18, 2010 at 10:05 am
For example, I have a table that contains all debit and credit entries and the balance is calculated by a function that subtracts the sum of all debits (from the first record) from the sum of all credits.
Now, at some point, the table will have too many records which may have to be stripped. How can I replace the deleted rows with the summary of debits and credits just at the point above the first remaining row?
Alternatively, there may be no deletion of old rows, but a summary record may be required at a certain point after the table has been audited and reconciled so as to reduce load borne by the computation function.
The end result is the same, a summary record has to be inserted at a specific point in the table, which will act as the starting point for all balance calculations thereafter.
Any assistance will be appreciated.
May 18, 2010 at 10:10 am
do you have any table layouts and sample data? What have you tried so far? Is there a reconciled flag in the table? If you provide sample data, it'll be easier for us to help
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2010 at 12:34 pm
[Code]
CREATE TABLE [dbo].[dbo_transactions_master](
[transaction_id] [int] NOT NULL,
[transaction_date] [smalldatetime] NOT NULL,
[department_id] [int] NOT NULL,
[transaction_type] [tinyint] NOT NULL,
CONSTRAINT [PK_dbo_sales_master] PRIMARY KEY CLUSTERED
(
[sale_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dbo_transactions_detail](
[transaction_id] [int] NOT NULL,
[item_id] [int] NOT NULL,
[quantity] [decimal](8, 0) NOT NULL,
[unit_id] [tinyint] NOT NULL,
[unit_price] [money] NOT NULL,
[vat_rate] [decimal](5, 0) NOT NULL,
[price] AS (([unit_price]*((100)+[vat_rate]))/(100)),
[total_price] AS ((([unit_price]*((100)+[vat_rate]))/(100))*[quantity]),
CONSTRAINT [PK_dbo_sales_product] PRIMARY KEY CLUSTERED
(
[sale_id] ASC,
[item_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[dbo_StocksByDepartment](
[stock_item_id] [int] NOT NULL,
[department_id] [int] NOT NULL,
[stock_item_balance] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_dbo_LocationsStockItems] PRIMARY KEY CLUSTERED
CONSTRAINT [PK_dbo_LocationsStockItems] PRIMARY KEY CLUSTERED
(
[stock_item_id] ASC,
[location_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
[/Code]
The transaction_id column on transactions_detail table is a foreign key of the transactions_master table. While the transaction_type flag column is part of the master table, the individual stock items affected by the transaction are listed in the details table. The transaction types are: 1 = Sales; 2 = Purchases; 3 = Transfer Inward; 4 = Transfer Outward; 5 = Stock Adjustment; 6 = Disposals. Each trans type represents a distinct transaction model for which aggregate totals are computed for reporting and reconciling purposes.
The stock on hand for any item is calculated as follows:
Balance on hand = Purchases + Transfer Inwards - Sales - Transfers Outwards - Disposals - Stock Adjustments.
This is done via a view; the stock_item_balance field in the table StocksByDepartment acts as a control value to audit the computed totals.
May 18, 2010 at 12:48 pm
goodguy, why not build a view that creates the summary that you are looking for?
I avoid at all costs replacing detail data with summary data...besides the possbility of a mistake, which makes the data wrong forever unless you go back to an original backup, i don't think that's good accounting practices....
auditors always want the ability to recreate your calculations from the underlying data....it avoids hijinx and helps fix errors in calculations.
it's fairly easy to create a rollup view that groups/partitions by periods of time or whatever you need to group with.
Lowell
May 19, 2010 at 3:34 am
Agreed, but, FYI, stripped data is first backed up for future retrieval before being purged.
All I want to know is, even if I do NOT delete old records:
How to capture summary totals to accelerate the balance calculation function?
How do banks and others do it?
And how does other software handle stripped data?
How does one insert the summary total at a specific point in the table?
As there is no way to insert a record at 'a specific point' in the table, I suspect the only way out is using the index property to push the desired record to the top. Any ideas?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply