CODEWITHSUNDEEP
sql-servercalculated-columns
Nox
Nox

Reputation: 241

Trying to access previous record in SQL Server

I'm trying to do a balance column in a accounting app with c# winforms, i'm doing it just for using by myself, being the formula of that column like this one:

balance[i] = debit[i] - credit[i] + balance[i-1]

So, I thought a calculated column would be the best solution. I'm using Visual Studio 2013 Community and SQL Server, I tried to do it in the "table view" in the CREATE TABLE script:

CREATE TABLE [dbo].[CONTAT1] 
(
    [NASIENTO] INT            IDENTITY (1, 1) NOT NULL,
    [FECHA]    DATE           NOT NULL,
    [CONCEPTO] NVARCHAR (MAX) NOT NULL,
    [DEBIT]     INT            DEFAULT ((0)) NOT NULL,
    [CREDIT]    INT            DEFAULT ((0)) NOT NULL,
    [BALANCE]    AS             ([DEBIT]-[CREDIT] + lag([BALANCE], 1, 0)),
    [FACTURA]  INT            NULL,
    [RECIBO]   INT            NULL,

    PRIMARY KEY CLUSTERED ([NASIENTO] ASC)
);

I specified the default lag's parameter so in the first record the function lag just add 0 (oh, I've translated the so-called rows names so anyone can follow the question). When i update the table it don't work and gives the following message:

Dropping unnamed constraint on [dbo].[CONTAT1]...
Dropping unnamed constraint on [dbo].[CONTAT1]...
Starting rebuilding table [dbo].[CONTAT1]...
(116,1): SQL72014: .Net SqlClient Data Provider: Msg 10753, Level 15, State 1, Line 13 The function 'lag' must have an OVER clause.
(104,0): SQL72045: Script execution error.  The executed script:
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_CONTAT1] (
    [NASIENTO] INT            IDENTITY (1, 1) NOT NULL,
    [FECHA]    DATE           NOT NULL,
    [CONCEPTO] NVARCHAR (MAX) NOT NULL,
    [DEBIT]     INT            DEFAULT ((0)) NOT NULL,
    [CREDIT]    INT            DEFAULT ((0)) NOT NULL,
    [BALANCE]    AS             ([DEBIT] - [CREDIT] + lag([BALANCE], 1, 0)),
    [FACTURA]  INT            NULL,
    [RECIBO]   INT            NULL,
    PRIMARY KEY CLUSTERED ([NASIENTO] ASC)
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[CONTAT1])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_CONTAT1] ON;
        INSERT INTO [dbo].[tmp_ms_xx_CONTAT1] ([NASIENTO], [FECHA], [CONCEPTO], [DEBIT], [CREDIT], [FACTURA], [RECIBO])
    SELECT   [NASIENTO],
             [FECHA],
             [CONCEPTO],
             [DEBIT],
             [CREDIT],
             [FACTURA],
             [RECIB
An error occurred while the batch was being executed.

The last time I used SQL was like in the early 2000's, so I'm not sure why don't work... honestly, the batch thing is killing me.

I have searched the lag function and seems correct to me, and since the table have a primary key AND if I just try it without the lag function (using just [BALANCE] AS ([DEBIT]-[CREDIT]) ) it works perfectly, I suppose I have no need to specify the order or something else, so I suppose again the problem is the lag function.

The questions are:

The lag function can be used with CREATE TABLE, right? And if not, should i just make a query in the form OnLoad event to create that column?

Are there any other way to access the previous record? Yes, i could do it via DataSet with a very simple foreach, but i don't want to create... how do you call it in english? Gaps? Just different information in the database and the app, and duplicate queries and what not... so i thought it would be better that the database manage it automagically :P , right?

Upvotes: 0

Views: 190

Answers (1)

TomTom
TomTom

Reputation: 62093

There is no previous record in SQL - tables have no order.

Standard for accounting - and in most jurisdictions even legally quite required - is to record the change AND THE NEW VALUE in the table, together with a running number (per account).

Upvotes: 1

Related Questions