CODEWITHSUNDEEP
sql-server
newbie
newbie

Reputation: 27

Creating a new column based on existing column within table

When you add a new column from an existing column - do you had to use add column or can you just drop it in? My orig table dosn't have Previous Day or Next Day - those are the columns I'm adding. Should this work?

SELECT
ACCT_NUM,
PRODUCT,
Date_local,
CURRENTDAY,
LAG[CURRENTDAY,1,0] OVER(PARTITION BY ACCT_NUM ORDER BY Date_local DESC) AS PREVIOUSDAY,
LEAD[CURRENTDAY,1,0] OVER(PARTITION BY ACCT_NUM ORDER BY Date_local DESC) AS NEXTDAY,
FROM FINANCE.REVENUE03

Or I could alter table?

ALTER TABLE REVENUE03
ADD COLUMN PREVIOUSDAY=
    LAG[CURRENTDAY,1,0] OVER(PARTITION BY ACCT_NUM ORDER BY Date_local DESC);
ADD COLUMN NEXTDAY=
    LEAD[CURRENTDAY,1,0] OVER(PARTITION BY ACCT_NUM ORDER BY Date_local DESC)

END

Upvotes: 0

Views: 78

Answers (1)

Ilyes
Ilyes

Reputation: 14928

Simply, you can't use a window function there, instead create a VIEW or try to create a FUNCTION and use it with the computed column.

Why?

Cause Windowed functions can only appear in the SELECT or ORDER BY clauses.

So, I sugget to create a VIEW like

CREATE VIEW YourViewName
AS
SELECT ACCT_NUM,
       PRODUCT,
       Date_local,
       CURRENTDAY,
       LAG[CURRENTDAY,1,0] OVER(PARTITION BY ACCT_NUM ORDER BY Date_local DESC) AS PREVIOUSDAY,
       LEAD[CURRENTDAY,1,0] OVER(PARTITION BY ACCT_NUM ORDER BY Date_local DESC) AS NEXTDAY,
FROM FINANCE.REVENUE03
GO

Upvotes: 1

Related Questions