Skip to content
Advertisement

Complicated logic of group by and partition

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

enter image description here

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement