How to update a column based on values of other columns

Question

I have a tables as below

row_wid id code sub_code item_nbr orc_cnt part_cnt variance reporting_date var_start_date
1      1   ABC   PQR     23AB      0       1        1   11-10-2019  NULL
2      1   ABC   PQR     23AB      0       1        1   12-10-2019  NULL
3      1   ABC   PQR     23AB      1       1        0   13-10-2019  NULL
4      1   ABC   PQR     23AB      1       2        1   14-10-2019  NULL
5      1   ABC   PQR     23AB      1       3        2   15-10-2019  NULL

I have to update var_start_date column with min(reporting_date) for each combination of id,code,sub_code and item_nbr only till variance field is zero. Row with variance = 0 should have null var_start_date. and next row after that should have next min(var_start_date.). FYI, variance is calculated as par_cnt-orc_cnt

so my output should look like this -

row_wid id code sub_code item_nbr orc_cnt part_cnt variance reporting_date var_start_date
1      1   ABC   PQR     23AB      0       1        1   11-10-2019  11-10-2019
2      1   ABC   PQR     23AB      0       1        1   12-10-2019  11-10-2019
3      1   ABC   PQR     23AB      1       1        0   13-10-2019  NULL
4      1   ABC   PQR     23AB      1       2        1   14-10-2019  14-10-2019
5      1   ABC   PQR     23AB      1       3        2   15-10-2019  14-10-2019

I am trying to write a function using below query to divide the data into sets.

SELECT DISTINCT MIN(reporting_date) 
        OVER (partition by id, code,sub_code,item_nbr ORDER BY row_wid ),
        RANK() OVER (partition by id, code,sub_code,item_nbr ORDER BY row_wid)
        AS rnk,id, code,sub_code,item_nbr,orc_cnt,part_cnt,variance,row_wid
FROM TABLE T1

.But dont know how to include variance field to split the sets.

score 1 · Answer 1 · answered Oct 23 '19 at 11:55

1

I would suggest:

select t.*,
       (case when variance <> 0
             then min(reporting_date) over (partition by id, code, sub_code, item_nbr, grouping)
        end) as new_reporting_date
from (select t.*,
             sum(case when variance = 0 then 1 else 0 end) over (partition by id, code, sub_code, item_nbr) as grouping
      from t
     ) t;

Note that this does not use a JOIN. It should be more efficient than an answer that does.

answered Oct 23 '19 at 11:55

Gordon Linoff

1,242,037
58
646
786

Yes it will as mine was one of the way to achieve. Cheers :) – Vignesh Kumar A Oct 23 '19 at 12:12

Vignesh Kumar A · Accepted Answer · 2019-10-23T08:42:57.020

0

Try as below

SELECT T.*, CASE WHEN T.variance = 0 THEN NULL ELSE MIN(reporting_date) OVER (PARTITION BY T1.RANK ORDER BY T1.RANK) END AS New_var_start_date 
FROM mytbl T
LEFT JOIN (
           SELECT row_wid, variance, COUNT(CASE variance WHEN 0 THEN 1 END) OVER (ORDER BY row_wid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +1 AS [Rank]
           FROM mytbl 
          ) T1 ON T.row_wid = T1.row_wid

SQL FIDDLE DEMO

edited Oct 23 '19 at 08:42

answered Oct 23 '19 at 07:36

Vignesh Kumar A

27,863
13
63
115

If you look at my sample data there are two different var_start_date for the same set of id,code,sub_code and item_nbr . As we encounter a row with variance=0, a different var_start_date has to be used. Your query will update all the records with the same var_start_date. – rach Oct 23 '19 at 08:19
@rach Updated the solution – Vignesh Kumar A Oct 23 '19 at 08:43
Thanks a ton Vignesh. This is exactly what I was looking for. – rach Oct 23 '19 at 09:05

How to update a column based on values of other columns

2 Answers2