It seems that you (or someone else?) deleted the other two questions, so I guess is the one to work with.
Here is a solution. Before I present it, I need to say few things. This is nothing you can take as spoonfood, but you need to read it closely and make sure that you understand it. I recommend that you look at the output from each CTE.
The reason for this is that I had make some assumptions of what result you want when there is more data. My assumptions may be wrong. But you may be able to correct my query. A few things:
- I've assumed that when there is a new C row after an A, it all starts over.
- Other status values can be ignored.
- The customers are indepedent of each other.
- The grpno that appears group each sequence of C and A rows. A C row that comes just before an A row, counts as an A row.
Here it goes. I added some more sample data:
DROP TABLE IF EXISTS tbl_Fact
create table tbl_Fact(
insert into tbl_Fact
; WITH CTE1 AS (
SELECT IdCmd, DateCMD, DateLIV, CA, CUST, Stat,
ModStat = IIF (Stat = 'C' AND LEAD(Stat) OVER (PARTITION BY CUST ORDER BY IdCmd) = 'A', 'A', Stat),
CASign = IIF(Stat = 'A', -1, 1)
WHERE Stat IN ('C', 'A')
), CTE2 AS (
SELECT IdCmd, DateCMD, DateLIV, CA, CASign, CUST, Stat,ModStat,
cstrowno = row_number() OVER(PARTITION BY CUST ORDER BY DateCMD, IdCmd),
cststatrowno = row_number() OVER(PARTITION BY CUST, ModStat ORDER BY DateCMD, IdCmd)
), CTE3 AS (
SELECT IdCmd, DateCMD, DateLIV, CA, CASign, CUST, Stat, ModStat,
grpno = cststatrowno - cstrowno
SELECT IdCmd, DateCMD, DateLIV, Stat, CA, CUST,
MaxVal = CASE WHEN ModStat = 'C' THEN
CASE WHEN CA = MAX(CA) OVER (PARTITION BY CUST, grpno) THEN CA END
WHEN ModStat = 'A' THEN
SUM(CA*CASign) OVER(PARTITION BY CUST, grpno
ORDER BY DateCMD, IdCmd ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY CUST, IdCmd