Deriving result based on multiple conditions

Question

Hi All,

This is my input source wherein c1 and c5 column determines the uniqueness of that table. C2 and C3 are factual columns and C4 is Flag column

These are the rules for deriving the results:

1. If C5 column is 1 then for the corresponding partition of C1 the value should be taken from C3
2. For all other values of C5, for same C1 value and different c5 value other then 1 we need to do some previous iteration calculation
   If C4 is 1 and c5 is not equal to 1 then for other iterations it should be C3-sum(previous iterations of the result)
3. If C4 is 0, take c2

How to achieve this result column?
Is it possible to achieve this without using any loop?

Answer 1

Hi @Priya Jha

it should be C3-sum(previous iterations of the result)

When you calculate Previous sum, you always need a sort field. Is C5 the ORDER BY column?
If I guess right, then check this query:

create table #temp(c1 int,c5 int,c2 int,c3 int,c4 int)  
insert into #temp values  
(3,1,10,11,1),(3,2,5,4,1),(3,3,11,13,1),(3,4,2,4,0),(3,5,3,5,1),(2,1,3,4,1),(1,1,12,12,1),(1,2,4,4,1)  
  
;WITH CTE AS  
(  
 SELECT *,CASE WHEN C5=1 THEN C3 ELSE C2 END AS RESULTS  
 FROM #temp  
 WHERE C5=1 OR (C5<>1 AND C4=0)  
 UNION ALL  
 SELECT *,C3-LAG(C3,1,0)OVER(PARTITION BY C1 ORDER BY C5)AS RESULTS  
 FROM #temp  
 WHERE C5<>1 AND C4<>0  
)  
SELECT C1,C5,C2,C3,C4,  
       CASE WHEN C5<>1 AND C4<>0 AND (LAG(C5)OVER(PARTITION BY C1 ORDER BY C5)=1 OR LAG(C4)OVER(PARTITION BY C1 ORDER BY C5) =0)  
	        THEN RESULTS-LAG(RESULTS)OVER(PARTITION BY C1 ORDER BY C5) ELSE RESULTS END AS RESULTS  
FROM CTE  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.