Customer Names change over time and when looking at states in SOP10100 and SOP30200 I would always get garbage in there. This is just a quick baseline query I use and then add elements to it as needed.
select
CUSTNMBR,
(select CUSTNAME from RM00101 x where x.CUSTNMBR = b.CUSTNMBR) AS CUSTNAME,
b.sopnumbe as DocumentNumber,
b.glpostdt as DocumentDate,
case
when b.soptype = '3' then b.docamnt
when b.soptype = '4' then b.docamnt * -1
end as DocSubtotal,
case
when b.soptype = '3' then 'INVOICE'
when b.soptype = '4' then 'RETURN'
else 'NA'
end as DocumentType,
b.state as UnformattedState,
YEAR(glpostdt) as fldyear,
case
when month(b.glpostdt) = '1' then 'JANUARY'
when month(b.glpostdt) = '2' then 'FEBRUARY'
when month(b.glpostdt) = '3' then 'MARCH'
when month(b.glpostdt) = '4' then 'APRIL'
when month(b.glpostdt) = '5' then 'MAY'
when month(b.glpostdt) = '6' then 'JUNE'
when month(b.glpostdt) = '7' then 'JULY'
when month(b.glpostdt) = '8' then 'AUGUST'
when month(b.glpostdt) = '9' then 'SEPTEMBER'
when month(b.glpostdt) = '10' then 'OCTOBER'
when month(b.glpostdt) = '11' then 'NOVEMBER'
when month(b.glpostdt) = '12' then 'DECEMBER'
else 'NA'
end as DocumentMonth,
case
when b.state = 'AL' then 'AL'
when b.state ='AK' then 'AK'
when b.state ='AZ' then 'AZ'
when b.state ='AR' then 'AR'
when b.state ='CA' then 'CA'
when b.state ='CO' then 'CO'
when b.state ='CT' then 'CT'
when b.state ='DE' then 'DE'
when b.state ='FL' then 'FL'
when b.state ='GA' then 'GA'
when b.state ='HI' then 'HI'
when b.state ='ID' then 'ID'
when b.state ='IL' then 'IL'
when b.state ='IN' then 'IN'
when b.state ='IA' then 'IA'
when b.state ='KS' then 'KS'
when b.state ='KY' then 'KY'
when b.state ='LA' then 'LA'
when b.state ='ME' then 'ME'
when b.state ='MD' then 'MD'
when b.state = '' then ''
when b.state ='MA' then 'MA'
when b.state ='MI' then 'MI'
when b.state ='MN' then 'MN'
when b.state ='MS' then 'MS'
when b.state ='MO' then 'MO'
when b.state ='MT' then 'MT'
when b.state ='NE' then 'NE'
when b.state ='NV' then 'NV'
when b.state ='NH' then 'NH'
when b.state ='NJ' then 'NJ'
when b.state ='NM' then 'NM'
when b.state ='NY' then 'NY'
when b.state ='NC' then 'NC'
when b.state ='ND' then 'ND'
when b.state ='OH' then 'OH'
when b.state ='OK' then 'OK'
when b.state ='OR' then 'OR'
when b.state ='PA' then 'PA'
when b.state ='RI' then 'RI'
when b.state ='SC' then 'SC'
when b.state ='SD' then 'SD'
when b.state ='TN' then 'TN'
when b.state ='TX' then 'TX'
when b.state ='UT' then 'UT'
when b.state ='VT' then 'VT'
when b.state ='VA' then 'VA'
when b.state ='WA' then 'WA'
when b.state ='WV' then 'WV'
when b.state ='WI' then 'WI'
when b.state ='WY' then 'WY'
when b.state ='DC' then 'DC'
else 'Unformatted'
end as FormattedState,
from sop30200 b
where b.VOIDSTTS = 0 and b.soptype in ('3', '4')
and b.glpostdt between '1/1/2020' AND '1/1/2021'