SQL - AR AP - Non-allocated tx

use  [NHR Investments - UA]
go
select 
	v.Account, v.Name
	,sum(case when p.outstanding > 0 then 1 else 0 end) as posOutstanding
	,sum(case when p.outstanding < 0 then 1 else 0 end) as negOutstanding
	,sum(case when p.outstanding > 0 then outstanding else 0 end) as posOutstandingVal
	,sum(case when p.outstanding < 0 then outstanding else 0 end) as negOutstandingVal
	,round(sum(p.outstanding ),2) as Outstanding
	
	from postap p
		left join vendor v on p.AccountLink = v.DCLink 
		--where outstanding < 0.0
		group by 
			v.Account, v.Name
		having 
			sum(case when p.outstanding > 0 then 1 else 0 end) > 0 AND
			sum(case when p.outstanding < 0 then 1 else 0 end) > 0


--AR:
select 
	v.Account, v.Name
	,sum(case when p.outstanding > 0 then 1 else 0 end) as posOutstanding
	,sum(case when p.outstanding < 0 then 1 else 0 end) as negOutstanding
	,sum(case when p.outstanding > 0 then outstanding else 0 end) as posOutstandingVal
	,sum(case when p.outstanding < 0 then outstanding else 0 end) as negOutstandingVal
	,round(sum(p.outstanding ),2) as Outstanding
	
	from postar p
		left join client v on p.AccountLink = v.DCLink 
		--where outstanding < 0.0
		group by 
			v.Account, v.Name
		having 
			sum(case when p.outstanding > 0 then 1 else 0 end) > 0 AND
			sum(case when p.outstanding < 0 then 1 else 0 end) > 0