CODEWITHSUNDEEP
sqlrowsubtraction
pape
pape

Reputation: 249

SQL - subtraction row

I have the following query

SELECT VALUE
    ,Source1
    ,Source2
    ,DocNo
FROM myTable

it returns the below data:

enter image description here

I want to calculate the subtraction based on the below condition:

for DocNo A1
if(Source1=1 and Source2=0) VALUE 34
if(Source1=1 and Source2=0) VALUE 21
subtraction  two row 34 - 21 = 13

Any idea?

Expected result:

enter image description here

Upvotes: 1

Views: 114

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

If you are using QL server 2012 or higher version,you can use LEAD() function.

WITH cte_1
AS
(SELECT VALUE, 
        VALUE-LEAD(VALUE) OVER(PARTITION BY DocNo ORDER BY DocNo) Result,
       Source1, 
       Source2,
       LEAD(Source1) OVER(PARTITION BY DocNo ORDER BY DocNo) NxtSource1,
       LEAD(Source2) OVER(PARTITION BY DocNo ORDER BY DocNo) NxtSource2,
       DocNo   
     FROM #myTable)
SELECT Case WHEN (Source1=1 AND NxtSource1=1) AND (Source2=0 AND NxtSource2=1) THEN Result ELSE VALUE END as Value,
       Source1,Source2,DocNo
FROM cte_1
WHERE (Source1<>1 OR source2 <>1)

OUTPUT :

enter image description here

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13247

If I understand your issue correctly, the query below is you want:

SELECT Value, Score1, Score2, DocNo
FROM TestTable 
WHERE Score1 = 0

UNION 

SELECT MAX(Value) - MIN(Value) AS Value, 1, 1, DocNo
FROM TestTable 
WHERE Score1 = 1
GROUP BY DocNo
ORDER BY DocNo, Score1

DEMO for the same.

Upvotes: 1

Related Questions