SQL Server: view CASE WHEN THEN ELSE END ROWNUMBER + the last value

Question

I'm trying to add on each row the value of the last QTY of the SAME item. How can I do this? Here is the idea I have that I think might work:

USE [Data_Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_Solde]
AS
SELECT [ItemCode]
  ,[Doc_#/Stock_Min]
  ,[DocDate]
  ,[Qty]
  ,[DESCR]
  , Solde =
  CASE ItemCode
  WHEN ItemCode.Row2"="ItemCode.Row1 THEN Qty.Row2"+" Solde.Row1 ELSE Qty.Row2 END
  WHEN ItemCode.Row3"="ItemCode.Row2 THEN Qty.Row3"+" Solde.Row2 ELSE Qty.Row3 END
  WHEN ItemCode.Row4"="ItemCode.Row3 THEN Qty.Row4"+" Solde.Row3 ELSE Qty.Row4 END
  WHEN ItemCode.Row5"="ItemCode.Row4 THEN Qty.Row5"+" Solde.Row4 ELSE Qty.Row5 END
  WHEN ItemCode.Row6"="ItemCode.Row5 THEN Qty.Row6"+" Solde.Row5 ELSE Qty.Row6 END
  WHEN ItemCode.Row7"="ItemCode.Row6 THEN Qty.Row7"+" Solde.Row6 ELSE Qty.Row7 END
  WHEN ItemCode.Row8"="ItemCode.Row7 THEN Qty.Row8"+" Solde.Row7 ELSE Qty.Row8 END
  WHEN ItemCode.Row9"="ItemCode.Row8 THEN Qty.Row9"+" Solde.Row8 ELSE Qty.Row9 END
  WHEN ItemCode.Row10"="ItemCode.Row9 THEN Qty.Row10"+" Solde.Row9 ELSE Qty.Row10 END
  WHEN ItemCode.Row11"="ItemCode.Row10 THEN Qty.Row11"+" Solde.Row10 ELSE Qty.Row11 END
  WHEN ItemCode.Row12"="ItemCode.Row11 THEN Qty.Row12"+" Solde.Row11 ELSE Qty.Row12 END
  WHEN ItemCode.Row13"="ItemCode.Row12 THEN Qty.Row13"+" Solde.Row12 ELSE Qty.Row13 END
  WHEN ItemCode.Row14"="ItemCode.Row13 THEN Qty.Row14"+" Solde.Row13 ELSE Qty.Row14 END
  WHEN ItemCode.Row15"="ItemCode.Row14 THEN Qty.Row15"+" Solde.Row14 ELSE Qty.Row15 END
  WHEN ItemCode.Row16"="ItemCode.Row15 THEN Qty.Ro16"+" Solde.Row15 ELSE Qty.Row16 END
  WHEN ItemCode.Row17"="ItemCode.Row16 THEN Qty.Row17"+" Solde.Row16 ELSE Qty.Row17 END
  WHEN ItemCode.Row18"="ItemCode.Row17 THEN Qty.Row18"+" Solde.Row17 ELSE Qty.Row18 END
  WHEN ItemCode.Row19"="ItemCode.Row18 THEN Qty.Row19"+" Solde.Row18 ELSE Qty.Row19 END
  WHEN ItemCode.Row20"="ItemCode.Row19 THEN Qty.Row20"+" Solde.Row19 ELSE Qty.Row20 END
ORDER BY [ItemCode]


GO

i tried using

SELECT TOP 100 [ItemCode]
  ,[Doc_#/Stock_Min]
  ,[DocDate]
  ,[Qty]
  ,[DESCR],
  sum(Qty) over (partition by ItemCode) as Solde
  FROM [Data_Warehouse].[dbo].[Inventory]
  order by ItemCode

It returns the result I want, but it does not exactly because I just see all of them with the same value I want to be able too see how much it was incremented since the last one.

Hi and welcome to SO. You seem to have missed several pieces here. There is no FROM clause in your view. And what is "=" supposed to be? or "+"? Given the names of what I assume to be columns in your view I have a feeling the real issue is the lack of normalization. As it sits right now this is not answerable because the view is completely invalid and not sure how that view has anything to do with the query you posted after it. — Sean Lange, Nov 14 '18 at 19:35
Perhaps... `qty + lag(qty) over (partition by ItemCode order by DocDate)`? It's hard to see what you want — S3S, Nov 14 '18 at 19:36
I'm confused, why do you have columns called things like `row1` and `row2`? Also, that first batch of SQL has your operators in double quotes and a load of `CASE` expressions not separated by commas, so won't compile. Sample data and expected results are going to help here. — Thom A, Nov 14 '18 at 19:37
Images of data are not helpful. [Why](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557)? To get real help you need to post real information. [Here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great place to start. — Sean Lange, Nov 14 '18 at 19:55

Utrolig · Answer 1 · 2018-11-14T20:09:06.667

Try something like this. It does a running total by partition.

DECLARE @t TABLE (
    EmployeeId BIGINT NOT NULL,
    Val INT NOT NULL,
    Stamp DATE NOT NULL
);

INSERT INTO @t
(EmployeeId,Val,Stamp)
VALUES
(1,10,'2018-10-01'),
(2,10,'2018-10-03'),
(3,10,'2018-10-02'),
(3,10,'2018-10-04'),
(4,10,'2018-10-06'),
(5,10,'2018-10-05'),
(6,10,'2018-10-07'),
(7,10,'2018-10-09'),
(7,10,'2018-10-08'),
(7,10,'2018-10-10');

SELECT
    t.EmployeeId,
    t.Val,
    t.Stamp,
    SUM(t.Val) OVER (PARTITION BY t.EmployeeId ORDER BY t.Stamp ROWS UNBOUNDED PRECEDING) AS RunningTotalByGroup
FROM @t AS t
ORDER BY t.Stamp

Here are a couple good references:

https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/

https://sqlwithmanoj.com/tag/unbounded-preceding/

The question was a little unclear, but from the sound of it you want a running total but just within each ItemCode, yes? — Utrolig, Nov 14 '18 at 19:57

SQL Server: view CASE WHEN THEN ELSE END ROWNUMBER + the last value

1 Answers1