TSQL to reconsile/exclude the cancelled claims based on quanity

create table ##test
(ndc  varchar(10),
nabp varchar(10),
utilqty int,
rowind char(1),
rownum int )

insert into ##test values(0,0,100,null,1)
insert into ##test values(1,1,100,null,2)
insert into ##test values(1,1,-100,null,3)
insert into ##test values(2,2,200,null,4)
insert into ##test values(2,2,200,null,5)
insert into ##test values(2,2,-200,null,6)
insert into ##test values(3,3,-300,null,7)
insert into ##test values(3,3,-300,null,8)
insert into ##test values(3,3,-300,null,9)
insert into ##test values(3,3,300,null,10)
insert into ##test values(3,3,300,null,10)
insert into ##test values(4,4,-400,null,11)
insert into ##test values(4,4,-400,null,12)
insert into ##test values(4,4,-400,null,13)
insert into ##test values(4,4,300,null,14)
insert into ##test values(5,5,300,null,15)
insert into ##test values(5,5,300,null,16)
insert into ##test values(5,5,300,null,17)
insert into ##test values(5,5,-300,null,18)
insert into ##test values(5,5,-300,null,19)


select ndc,nabp,abs(utilQty) absutilqty,COUNT(*) noofrows,
SUM(
                 CASE
                       WHEN utilQty <0
                       THEN  1
                       ELSE 0
                 END
           )                            AS NumberOfNeg
      ,     SUM(
                 CASE
                       WHEN utilQty <0
                       THEN  0
                       ELSE 1
                 END
           )                            AS NumberOfPos
      ,     SUM(utilQty)           AS Total_utilQty
      into #dataset1
      from ##test
      group by ndc,nabp,abs(utilQty)
     
      HAVING           COUNT(*)   > 1
      AND         SUM(
                 CASE
                       WHEN utilQty <0
                       THEN  1
                       ELSE 0
                 END
                 )          > 0
      AND         SUM(
                 CASE
                       WHEN utilQty <0
                      THEN  0
                       ELSE 1
                 END  
                 )          > 0
                
                 select * from #dataset1
                
                 select * from ##test
                
update ##test
set rowind='Y'
from ##test a  innerjoin #dataset1 b on
a.ndc=b.ndc and
a.nabp=b.nabp and absutilqty=abs(a.utilqty)
and b.Total_utilQty=0;


select a.ndc,a.nabp,a.utilqty,a.rownum into #POSUTI from (
select ndc,nabp,utilqty,rownum ,
row_number() over(PARTITIONby  ndc,nabp,utilqty order by rownum ) as rank
from ##test) a inner join #dataset1b on

a.ndc=b.ndc and a.nabp=b.nabp and abs(a.utilqty)=absutilqty
and b.Total_utilQty>0 --and a.utilqty<0

and a.rank<=b.NumberOfNeg

select * from #POSUTI

select a.ndc,a.nabp,a.utilqty,a.rownum  into #NEGUTI from (
select ndc,nabp,utilqty,rownum ,
row_number() over(PARTITIONby  ndc,nabp,utilqty order by rownum ) as rank
from ##test) a inner join #dataset1b on

a.ndc=b.ndc and a.nabp=b.nabp and abs(a.utilqty)=absutilqty
and b.Total_utilQty<0 --and a.utilqty<0

and a.rank<=b.NumberOfPos

select * from #NEGUTI

update ##test
set rowind='Y'
from ##test a  innerjoin #POSUTI b on
a.ndc=b.ndc and
a.nabp=b.nabp and a.utilqty=a.utilqty
and a.rownum=b.rownum

update ##test
set rowind='Y'
from ##test a  innerjoin #NEGUTI b on
a.ndc=b.ndc and
a.nabp=b.nabp and a.utilqty=a.utilqty
and a.rownum=b.rownum

Reasons-JOIN IN TSQL IS SLOW

If your JOIN in TSQL is slow, it probably has something to do with:

1. inappropriate or missing indexes
2. outdated statistics
3. non-SARGable arguments in the WHERE clause
4. incompatible datatypes in the ON clause of the JOIN, forcing the engine to do an implicit conversion, resulting in indexes not being used