SQL Server Using T-SQL, I am creating a calculated column and I want to retrieve the value of the previous row of that column - is it possible?

ss2ws0br 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(104)

I have a requirement to populate some data with some grouping.

DDL Script:

CREATE TABLE #TEMP 
(
    OTHERCOL VARCHAR(10),
    RANK_NO INT,
    TAG INT
)

INSERT INTO #TEMP VALUES ('OC1',1,0)
INSERT INTO #TEMP VALUES ('OC1',2,0)
INSERT INTO #TEMP VALUES ('OC1',3,0)
INSERT INTO #TEMP VALUES ('OC2',1,0)
INSERT INTO #TEMP VALUES ('OC2',2,0)
INSERT INTO #TEMP VALUES ('OC2',3,9999)
INSERT INTO #TEMP VALUES ('OC2',4,0)
INSERT INTO #TEMP VALUES ('OC2',5,0)
INSERT INTO #TEMP VALUES ('OC3',1,0)
INSERT INTO #TEMP VALUES ('OC3',2,0)
INSERT INTO #TEMP VALUES ('OC3',3,0)

Using DENSE_RANK over OTHERCOL , I have got FINAL_TAG as shown here:

OTHERCOL	RANK_NO	TAG	FINAL_TAG
OC1	1	0	1
OC1	2	0	1
OC1	3	0	1
OC2	1	0	2
OC2	2	0	2
OC2	3	9999	2
OC2	4	0	2
OC2	5	0	2
OC3	1	0	3
OC3	2	0	3
OC3	3	0	3

However, I want the result to be as shown below in the desired value. I could achieve this in Excel, but I'm unable to so in SQL Server.

Details of the activity done is attached here

I have tried to use SELECT CASE but it is not working as the value is getting reset... Is there any way to so that we can update the values of variable and use the same based on the condition?

sql-server

来源：https://stackoverflow.com/questions/76752934/using-t-sql-i-am-creating-a-calculated-column-and-i-want-to-retrieve-the-value

2条答案

按热度按时间

o2rvlv0m1#

You can get the desired value by applying a conditional running total using analytic function sum() and case clause :

select *, DENSE_RANK() over (order by OTHERCOL) as FINAL_TAG,
          DENSE_RANK() over (order by OTHERCOL) 
          + sum(case when TAG > 0 then 1 else 0 end) over(order by OTHERCOL, RANK_NO ROWS UNBOUNDED PRECEDING) as DESIRED_VALUE
from #TEMP

Demo here

赞(0）回复(0）举报 2023-08-02

osh3o9ms2#

You can do first a conditional count of Tag , excluding (by nulling out) the cases where Tag = 0 .

Then do DENSE_RANK using that column as another ordering column.

SELECT *,
  DENSE_RANK() OVER (ORDER BY OTHERCOL, c)
FROM (
    SELECT *,
      c = COUNT(NULLIF(TAG, 0)) OVER (PARTITION BY OTHERCOL ORDER BY RANK_NO)
    FROM #TEMP t
) t;

db<>fiddle

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Using T-SQL, I am creating a calculated column and I want to retrieve the value of the previous row of that column - is it possible?

2条答案

相关问题

热门标签

最新问答