Ok I have the following query that is working on my ACTIVE sales order tables but I need to join it and that same exact query against my HISTORY tables...
select a.custnmbr, a.custname, sum(b.xtndprce) as dollars from
(select sopnumbe, custnmbr, custname from sop10100) A
right join
(select sopnumbe, itemnmbr, xtndprce from sop10200 where itemnmbr in (select itemnmbr from iv00101 where itmclscd='eng-parts' or itmclscd='husq parts' or itmclscd='jte-parts' or itmclscd='pre-parts' or itmclscd='sdi-parts' or itmclscd='smi parts'
)) B
on a.sopnumbe = b.sopnumbe
group by a.custnmbr, a.custname
order by dollars desc
The active tables are the SOP10100 and SOP10200 and the history tables are SOP30200 and SOP30300.
So this is the psuedo code of what I am looking for.
select a.custnmbr, a.custname, sum(b.xtndprce) as dollars from
(select sopnumbe, custnmbr, custname from sop10100) A
right join
(select sopnumbe, itemnmbr, xtndprce from sop10200 where itemnmbr in (select itemnmbr from iv00101 where itmclscd='eng-parts' or itmclscd='husq parts' or itmclscd='jte-parts' or itmclscd='pre-parts' or itmclscd='sdi-parts' or itmclscd='smi parts'
)) B
on a.sopnumbe = b.sopnumbe
group by a.custnmbr, a.custname
order by dollars desc
------>>>>JOIN<<<<--------
-
--History
--Parts Customers Largest To Smallest Query
select a.custnmbr, a.custname, sum(b.xtndprce) as dollars from
(select sopnumbe, custnmbr, custname from sop30200) A
right join
(select sopnumbe, itemnmbr, xtndprce from sop30300 where itemnmbr in (select itemnmbr from iv00101 where itmclscd='eng-parts' or itmclscd='husq parts' or itmclscd='jte-parts' or itmclscd='pre-parts' or itmclscd='sdi-parts' or itmclscd='smi parts'
)) B
on a.sopnumbe = b.sopnumbe
group by a.custnmbr, a.custname
order by dollars desc
Start Free Trial
