Not able to use LAG function in defining computed column in SQL Server 2014

Question

I am trying to create table where I want computed column which will have column value from previous row from the same table. But I am not able to use LAG function successfully.

CREATE TABLE DYL(
    DY_DT DATE NOT NULL,
    DY_SEN_NME varchar(20) NOT NULL,
    DY_OEN_PRC numeric(14,2) NOT NULL,
    DY_HEL_PRC numeric(14,2) NOT NULL,
    DY_LEL_PRC numeric(14,2) NOT NULL,
    DY_CLN_PRC numeric(14,2) NOT NULL,
    DY_REG AS (DY_HEL_PRC - DY_LEL_PRC) PERSISTED,
    DY_PRV_PRC AS LAG(DY_CLN_PRC,1,0) OVER (PARTITION BY DY_SEN_NME ORDER BY DT)
)

I get error Windows Functions can only appear in SELECT or ORDER BY clauses.

If this is not possible then how can this be achieved using function. I tried writing a scalar function and associated it to DY_PRV_PRC.

ALTER TABLE DYL 
    ADD DY_PRV_PRC AS F_PRV_PRC()

but this is giving me same value in DY_PRV_PRC instead of giving previous row value of column DY_CLN_PRC

Below is the code for the Function F_PRV_PRC:

CREATE FUNCTION [dbo].[F_PRV_PRC] ()
RETURNS NUMERIC(14,2)
AS
BEGIN

    DECLARE @pcp NUMERIC(14,2)
    SELECT @pcp = LAG(DY_CLN_PRC,1,0) OVER (PARTITION BY DY_SEN_NME ORDER BY DY_DT)
    FROM DYL;
    RETURN @pcp;

END;

If possible, please suggest change to the function so it can give previous row column value. I am extremely sorry as I am new to SQL coding.

Many Thanks in advance

Answer 1

This is not really a good thing to do (from a performance perspective). But, if you wanted to do it, start with a user valued function:

DY_PRV_PRC AS (get_prev_value(DY_CLN_PRC, DY_SEN_NME, DT))

Then define the function something like this:

CREATE FUNCTION dbo.get_prev_value (
    @DY_CLN_PRC numeric(14,2)
    @DY_SEN_NME varchar(20)
    @DT date
)
RETURNS NUMERIC(14,2)
AS
BEGIN
    DECLARE @pcp NUMERIC(14,2)
    SELECT @pcp = (SELECT TOP 1 DY_CLN_PRC
                   FROM DYL
                   WHERE DY_SEN_NME = @DY_SEN_NME
                         DY_DT < @DY_DT
                   ORDER BY DY_DT DESC
                  );
    RETURN @pcp;
END;

If you want to use LAG() use a view:

create view v_dyl as
    select dyl.*,
           lag(DY_CLN_PRC) over (partition by DY_SEN_NME order by DT) as prev_dy_cln_prc
    from dyl;