CODEWITHSUNDEEP
sqlsql-server
Hind Ahmamad
Hind Ahmamad

Reputation: 51

MERGE with CASE in the same table

I am using SQL Server 2014. My table is called TF and this is what I have so far.

+-----------+------------+--------+--------------+
| IdProduct | Month      | Sales  | Accumulation |
+-----------+------------+--------+--------------+
| DSN101    | 01/01/2014 | 100    | ((1))        |
| DSN101    | 01/02/2014 | 50     | 50           |
| DSN101    | 01/03/2014 | 250    | 300          |
+-----------+------------+--------+--------------+

IdProduct is a string
Month is a Date
Sales and accumulation are float

The accumulation column was initially null and what I did next didn't work so I put the default value to 1. This is how I update the table and fill it : 

GO
MERGE INTO dbo.TF as A
USING dbo.TF as P
ON (A.IdProduct = P.IdProduct and MONTH(P.Month)=MONTH(A.Month)-1 and YEAR(P.Month)=YEAR(A.Month))
WHEN MATCHED THEN
UPDATE SET A.Accumulation = CASE
    WHEN P.Accumulation Is not null then P.Accumulation+A.Sales-1
    WHEN MONTH(A.Month)=1 and not exists(select P.Sales) then A.Sales
END;

So at first none of this would work obviously because of the first null that leads to a second null and then to third.. Now the first case works fine, the second doesn't and I just don't get why.
I tried many combinations with no success. What I need is that for the first month in every year the accumulation column equals simply the sales column.
I understand my code makes every line look for the previous one but I don't know how to make it stop when it's January.
Please help me !

Upvotes: 1

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

It seems that you want accumulative to contain the cumulative sum of the values in the column. You don't need to store this in the table, you can just get it from a query:

select tf.*, sum(sales) over (order by month) - sales
from tf;

The - sales is because the accumulation appears to not include the current month's sales. If you only want it for the current year (which the merge statement suggests), then add a partition by:

select tf.*, sum(sales) over (partition by year(month) order by month) - sales
from tf;

And, if you really want to include this in the table, these are updatable expressions:

with toupdate as (
      select tf.*, sum(sales) over (order by month) - sales as newval
      from tf
     )
update toupdate
    set accumulation = newval;

Upvotes: 2

Related Questions