I have the table as per the script below. The data that I want finally is shown in the screenshot. The logic that is to be implemented is : If SUM(FPR_QTY) > QPA, Use QPA without summing it up. Else, use FPR_QTY.
Eg explanation: For the first 4 rows, TOT_FPR > QPA, so I just need 1. While for rest 4 rows, TOT_FPR < QPA, i need to use TOT_FPR. So, ultimately, I want 21 against each record.
Please let me know if my explanation is not clear.
create table #TEMP (QPA int ,FPR_QTY int , key1 varchar(2) , key2 varchar(10) ) insert into #TEMP values (1,1,'K1','kk1') ,(1,0,'k1','kk1') ,(1,1,'k1','kk1') ,(1,0,'k1','kk1') ,(50,5,'k2','kk1') ,(50,5,'k2','kk1') ,(50,5,'k2','kk1') ,(50,5,'k2','kk1') select * ,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR from #TEMP
Advertisement
Answer
Here you go…I was able to write the query within two selects. I wish I could accept my own answer as it is most simplest and will run without failure.
select * ,SUM(IIF(TOT_FPR>QPA,IIF(QPA_IND = 1,QPA,0),FPR_QTY)) OVER (PARTITION BY key2) FINAL from ( select * ,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR ,ROW_NUMBER() OVER (PARTITION BY key1 order by QPA) AS QPA_IND from #TEMP )T