Posted - 04/30/2013 : 05:25:57
| I have a requirement in SQL Server where in one of the DB tables i need to group the data on the basis of a Grouping flag. And this GroupingFlag is generated on the 0 value occurence of another column in this table called 'Price'. For EX, plz refer below table
Product Price GroupingFlag
P1 -10 1
P1 -10 2
P2 10 3
P2 20 4
P2 0 4
P3 20 5
P3 -15 6
P3 0 6
P3 0 6
P3 0 6
Now, on the basis of 0 occurrence in Price value, Groupingflag value is set. first row of price is -10, hence Groupingflag is marked as 1, in second row again this is non zero, hence Grouping flag is marked as 2. This goes on till fourth row, where 0 appears now, Groupingflag should retain the previous occurrence of GroupingFlag value i.e.4.
SO I need to populate this GroupingFlag column in a table which has huge data. I have achieved this with the help of While/For Loop but these are taking considerable amount of item. Is there any approach where we can do this, by single update statement or select statement. I have tried to implement it using below Update query, however with huge data this is giving abrupt results but working for less data.
DECLARE @runningValue BIGINT = 0
UPDATE #cteILIDetails SET GroupingFlag = @runningValue, @runningValue = CASE WHEN Price IS NULL OR Price <> 0 THEN @runningValue + 1 ELSE @runningValue END
Any help would be highly appreciated.