CODEWITHSUNDEEP
sqlsql-server
TapeHead
TapeHead

Reputation: 120

SQL Server : calculate column value based on value in previous row/id

I have what I hope is a simple problem to solve, but one that is puzzling me. My table can be created with the below sql:

CREATE TABLE test
(
     [Id] [int] IDENTITY(1, 1) NOT NULL, 
     [lang] varchar(13), 
     [TotalProjectCost] [decimal](16, 2)
);

INSERT INTO test ([lang], [TotalProjectCost])
VALUES
    ('en',  CAST(341412.00 AS Decimal(16, 2))),
    ('es-mx',  CAST(342.00 AS Decimal(16, 2))),
    ('en',  CAST(341412.00 AS Decimal(16, 2))),
    ('es-mx',  CAST(34.00 AS Decimal(16, 2))),
    ('en',  CAST(341412.00 AS Decimal(16, 2))),
    ('es-mx',  CAST(34.00 AS Decimal(16, 2)));

Which looks like this:

enter image description here

This is basically a currency conversion task (the costs in the es-mx row are arbitrary in this example). What I wish to do is update all the TotalProjectCost values of rows with lang of 'es-mx': for these rows, I wish to calculate the value as whatever the TotalProjectCost value is of the previous 'en' row * 15.8735.

The way my actual table is set up, values are always submitted in pairs (one en, one es-mx)...so odd Ids should always be en, even ones should always be es-mx. As you can see, there's probably tons of ways to approach this and maybe that's why it's hard for me to know where to start.

I was kinda getting somewhere with this select statement:

select * 
from test as table1
inner join test as table2 on table1.ID = (table2.ID + 1)

But I still fall short of actually getting to the Update that calculates the correct values.

I appreciate any thoughts on this! I've tried looking around for an example on the boards that somewhat matches my scenario, but they seem a bit more involved than what I'm trying to accomplish.

Upvotes: 2

Views: 1044

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use a lateral join:

select t.*,
       (case when t.lang = 'es-mx' then t2.TotalProjectCost * 15.8735
             else t.TotalProjectCost
        end) as imputed_TotalProjectCost
from t cross apply
     (select t2.*
      from t t2
      where t2.lang = 'en' and t2.id <= t.id
      order by t2.id desc
     ) t2;

This can easily be incorporated into an update, if you want to change the data:

update t
    set t.TotalProjectCost = t2.TotalProjectCost * 15.8735
    from t cross apply
         (select t2.*
          from t t2
          where t2.lang = 'en' and t2.id <= t.id
          order by t2.id desc
         ) t2
    where t.lang = 'es-mx'

Upvotes: 1

forpas
forpas

Reputation: 164089

Use LAG() widow function in an updatable CTE:

WITH cte AS (
  SELECT *, LAG([TotalProjectCost]) OVER (ORDER BY [Id]) prev_cost 
  FROM test
)
UPDATE cte 
SET [TotalProjectCost] = 15.8735 * prev_cost
WHERE [lang] = 'es-mx'

See the demo.
Results:

> Id | lang  | TotalProjectCost
> -: | :---- | ---------------:
>  1 | en    |        341412.00
>  2 | es-mx |       5419403.38
>  3 | en    |        341412.00
>  4 | es-mx |       5419403.38
>  5 | en    |        341412.00
>  6 | es-mx |       5419403.38

Upvotes: 2

Serg
Serg

Reputation: 22811

Try

update table1
   set [TotalProjectCost] = table2.[TotalProjectCost] * 15.8735
from test as table1
inner join test as table2 on table1.[ID] = (table2.[ID] + 1)
    and table2.[lang] = 'en'

Upvotes: 1

Related Questions