How to assign a running value to a row depending on some conditions

Question

I'm trying to give a certain number of point to my row depending on some condition, I can't figure out how to write it.

For a given Id, FormatCode and Price I want to give the value 1. For subsequent rows, for the same Id and Price, if the FormatCode is a multiple of a previous row with the same Id and price, I want to give the same value.

For instance :

00010405, 100, 0.3218 = 1

00010405, 400, 0.3218 = 1 (400 % 100 = 0)

00010405, 500, 0.3126 = 2 (500 % 100 = 0, but the price is different)

00010405, 1000, 0.3126 = 2 (1000 % 500 and 1000 % 100 = 0, but the price of the format code 100 is different, hence it will take the value 2 because it has the same price)

Id	Format Code	Price	Value	Row
00010405	100	0.3218	1	1
00010405	400	0.3218	1	2
00010405	500	0.3126	2	3
00010405	1000	0.3126	2	4

SELECT
   Id,
   FormatCode,
   Price,
   Value,
   ROW_NUMER() OVER (
      PARTITION BY Id
      ORDER BY FormatCode
   )
FROM Table

What is logic for the 4th row? is it 1000 % 500 or 1000 % 100? Why? There's really not enough information for us to do more than guess what your logic is beyond these 3 vague examples. — Larnu
You state in the question "For subsequent rows, for the same Id and Price, if the FormatCode is a multiple of a previous row", however, for when FormatCode has a value of 500 the previous row has a value of 400 yet your example expression states 100 is the comparison: "(500 % 100 = 0, but the price is different)". Does "A previous row", mean any of them? If so, why not state "(500 % 400 = 100 however, 500 % 100 = 0, but the price is different)" so that that condition is clear? — Larnu
@SandraGuilepZouaouiZandeh the expected output is as in the table shown in the question — Noone
@Larnu the forth row is 1000 % 500 because the price is the same as the format code 500, I have edited the question to make it clearer — Noone

Wirack Wirack · Accepted Answer · 2021-02-09T21:50:48

If I understand you correctly, you want to prioritise formatCode multiplier over price. Instead of ROW_NUMBER you should take a look at DENSE_RANK.

The description is still a bit unclear to me, but if I understood it correctly here is a working example:

--Setup some sample data

drop table if exists #tmp

select *
into #tmp
from (values 
('00010405',100, 0.3218 ),
('00010405',400, 0.3218 ),
('00010405',500, 0.3126 ),
('00010405',1000, 0.3126 ),
('00010405',1333, 0.3126 ),--not a multiple
('00010405',2666, 0.3126 )--multiple of previous row
) as tab(id, formatcode, price)

--Make the calculation
select 
    t.id,
    t.formatcode,
    t.price,
    DENSE_RANK() over(partition by id order by minMulti.formatCodeMin_multiplier, t.price) as Value
from #tmp t
cross apply(
    select min(formatCode) as formatCodeMin_multiplier
    from #tmp t2
    where t.id = t2.id and t.price = t2.price
    and t.formatcode % t2.formatcode = 0
) as minMulti
order by id, formatcode

The trick is to find the formatcode with the lowest value where the current row's value is a multiplier of.

How to assign a running value to a row depending on some conditions

1 Answers