A common problem in sql is how to calculate and process, for the current row, information that is based on the results (sum, product, difference, etc) of the rows read so far.
A solution (we use) to this problem is to create a self join query based on the primary key using the condition of "greater or equal".
Suppose we have the following table:
[Table1] :
[ID] [numeric](18, 0) NOT NULL ,
[TheDate] [smalldatetime] NULL ,
[Credit] [float] NULL ,
[Debit] [float] NULL
We 'd like to produce an output that for every record displays the ID, CreditValue, DebitValue and Result:
Where Result = (Credit-Debit)[of the current row] +
Sum(Credit-Debit)[of all rows currently read for this ID].
A Normalization Primer
For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.
2003-01-13
18,606 reads