Here's the script I came up with that matches up 1099 amounts from the Detail to the Summary tables
WITH
GPTEN99SUMMARY_CTE (GPVENDORID, GPPERIODID, GPYEAR1, GPTEN99AMNT, GPTEN99TYPE, GPTEN99BOXNUMBER)
AS (SELECT RTRIM(VENDORID),PERIODID,YEAR1,TEN99AMNT, TEN99TYPE, TEN99BOXNUMBER
FROM PM00204
WHERE YEAR1 = 2011
AND TEN99AMNT <> 0.00
)
,
--Calculation that I have found that matches the 1099 Summary Table
MY1099SUMMARY_CTE (MYVENDORID, MYPMPERIODID, MYPMYEAR1, MYPMTEN99AMNT, MYTEN99TYPE, MYTEN99BOXNUMBER)
AS
(
SELECT
RTRIM(VENDORID) as [MYVENDORID]
,MONTH(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END) as [MYPMPERIODID]
,YEAR(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END) as [MYPMYEAR1]
,SUM(TEN99AMNT - Credit1099Amount) as [MYPMTEN99AMNT]
,DEFTEN99TYPE as MYTEN99TYPE
,DEFTEN99BOXNUMBER as MYTEN99BOXNUMBER
FROM PM30300 as a WITH(NOLOCK)
WHERE 1=1
AND YEAR(DATE1) = 2011
GROUP BY VENDORID, MONTH(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END), YEAR(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END), DEFTEN99TYPE , DEFTEN99BOXNUMBER
HAVING SUM(TEN99AMNT - Credit1099Amount) <> 0.00
)
--Full Join them Together
SELECT
a.MYVENDORID
, a.MYPMPERIODID
, a.MYPMYEAR1
, a.MYPMTEN99AMNT
, a.MYTEN99TYPE
, a.MYTEN99BOXNUMBER
, b.GPVENDORID
, b.GPPERIODID
, b.GPYEAR1
, b.GPTEN99AMNT
, b.GPTEN99TYPE
, b.GPTEN99BOXNUMBER
FROM MY1099SUMMARY_CTE as a
FULL JOIN GPTEN99SUMMARY_CTE b ON a.MYVENDORID = b.GPVENDORID AND a.MYPMPERIODID = b.GPPERIODID AND a.MYPMYEAR1 = b.GPYEAR1
-- AND MYTEN99TYPE = GPTEN99TYPE AND GPTEN99BOXNUMBER = MYTEN99BOXNUMBER
-- AND MYPMTEN99AMNT = GPTEN99AMNT
WHERE 1=1
--Comment this out to get everything, leave uncommented to see any conditions that do not match 100%
--AND a.MYPMTEN99AMNT <> b.GPTEN99AMNT OR a.MYVENDORID IS NULL OR b.GPVENDORID IS NULL
ORDER BY 1, 2--, 7, 8
