Eu tenho essas tabelas:
Payments
vendor | VendorLocation | VendorName | CheckDate | Amount | TransactionID | InvoiceID
---------------------------------------------------------------------
777 | | Sony | 5/1/2020 | 1.50 | 345 | ABC555
777 | | Sony | 5/1/2020 | 1.20 | 444 | ABC555
777 | 103 | Sony Music | 5/1/2020 | 8.50 | 888 | XYZ888
777 | 105 | Sony VR | 5/1/2020 | 2.50 | 789 | XYZ999
777 | 105 | Sony VR | 5/1/2020 | 2.50 | 833 | XYZ111
Vendor Location
vendor | VendorLocation | LocationType |Address 1 | State | City
---------------------------------------------------------------------
777 | 103 | X |123 Ave | CA | Rivertown
777 | 105 | Z |666 Ave | CA | Northtown
777 | 106 | Z |888 Ave | CA | Southtown
Vendor Address
vendor | VendorLocation | Address 1 | State | City
---------------------------------------------------------------------
777 | | 1 Main St | CA | Rivertown
777 | 103 | 123 Ave | CA | Rivertown
777 | 105 | 666 Ave | CA | Northtown
O que eu quero que seja a saída:
Vendor Spending
vendor | VendorLocation | LocationType | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777 | | Z | Sony | 2 | $2.70 | 1 Main St
777 | 105 | Z | Sony VR | 2 | $5.00 | 666 Ave
No entanto, estou obtendo o resultado abaixo, que é duplicado e o valor totalSpend incorreto:
Vendor Spending
vendor | VendorLocation | LocationType | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777 | | Z | Sony | 2 | $2.70 | 1 Main St
777 | 105 | Z | Sony VR | 2 | $7.70 | 1 Main St
This is query I have:
select distinct
ap.vendor Vendor_ID
,vl.VendorLocation
,vl.LocationType
,ap.VendorName
,count(ap.transactionID) as TotalTransactions
,sum( ap.amount) as TotalSpend
,ad.Address1
from payments ap
join VendorLocation vl
on ( (vl.vendor=ap.vendor and vl.VendorLocation=ap.VendorLocation)
or (vl.vendor=ap.vendor and vl.VendorLocation='')
or (vl.vendor=ap.vendor and ap.VendorLocation='')
and vl.LocationType in ('Z')
) join VENDORADDRESS ad
on (ad.VENDOR=ap.vendor AND ad.VendorLocation=ap.VendorLocation)
where
vl.LocationType in ('Z') and
ap.CheckDate>='4/1/2022'
group by
vl.vendor
,vl.LOCATION_CODE
,vl.LocationType
,ap.VendorName
,ad.Address1
