select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname from
(select sopnumbe, docid, docdate, custnmbr, custname, bachnumb, frtamnt, taxamnt from sop10100) A
left outer join
(select custnmbr, custname from rm00101) B
on a.custnmbr = b.custnmbr
left outer join
(select sopnumbe, itemnmbr, itemdesc, uofm, unitcost, unitprce from sop10200) C
on a.sopnumbe = c.sopnumbe
/*where /*(bachnumb in(@bachnumb)) and */(a.frtamnt <= '0' or a.taxamnt <= '0' or c.unitprce <= '0') /*or ((1-(c.unitcost/unitprce))<.2)*/*/
order by sopnumbe asc
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname
,case when a.bachnumb in (@bachnumb) then 1 else 0 end as check1
,case when a.frtamnt <= '0' then 1 else 0 end as check2
,case when a.taxamnt <= '0' then 1 else 0 end as check3
,case when c.unitprce <= '0' then 1 else 0 end as check4
,case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end as check5
from sop10100 A
left outer join rm00101 b on a.custnmbr = b.custnmbr
left outer join sop10200 c on a.sopnumbe = c.sopnumbe
where bachnumb in (@bachnumb)
OR a.frtamnt <= '0'
OR a.taxamnt <= '0'
OR c.unitprce <= '0'
OR (1-(c.unitcost/c.unitprce))<0.2
order by a.sopnumbe asc
select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname
,case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end as Margin
,case when b.TaxSchedule * a.DocTotal<>a.TaxAmnt then 1 else 0 end as [Percent]
,case when b.ExemptStatus = 1 and a.TaxAmnt>0 then 1 else 0 end as Excemption
,case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end as Ship
,case when b.CustomerTerms='Credit Card' then 1 else 0 end as Terms
from sop10100 a
left outer join rm00101 b on a.custnmbr = b.custnmbr
left outer join sop10200 c on a.sopnumbe = c.sopnumbe
) T
WHERE Margin+[Percent]+Excemption+Ship+Terms>0
order by sopnumbe asc
select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, MIN(c.itemnmbr) as itemnmbr, a.frtamnt, a.taxamnt, b.custname
,MAX(case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end) as Margin
,MAX(case when b.TaxSchedule * a.DocTotal<>a.TaxAmnt then 1 else 0 end) as [Percent]
,MAX(case when b.ExemptStatus = 1 and a.TaxAmnt>0 then 1 else 0 end) as Excemption
,MAX(case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end) as Ship
,MAX(case when b.CustomerTerms='Credit Card' then 1 else 0 end) as Terms
from sop10100 a
left outer join rm00101 b on a.custnmbr = b.custnmbr
left outer join sop10200 c on a.sopnumbe = c.sopnumbe
GROUP BY a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, a.frtamnt, a.taxamnt, b.custname
) T
WHERE Margin+[Percent]+Excemption+Ship+Terms>0
order by sopnumbe asc
select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, a.frtamnt, a.taxamnt, b.custname
,c.Margin
,case when b.TaxSchedule * a.DocTotal<>a.TaxAmnt then 1 else 0 end as [Percent]
,case when b.ExemptStatus = 1 and a.TaxAmnt>0 then 1 else 0 end as Excemption
,case when a.ShipTo NOT IN ('SHIP TO','1') then 1 else 0 end as Ship
,case when b.CustomerTerms='Credit Card' then 1 else 0 end as Terms
from sop10100 a
left outer join rm00101 b on a.custnmbr = b.custnmbr
left outer join (SELECT sopnumbe, MAX(case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end) AS Margin FROM sop10200 GROUP BY sopnumbe) c on a.sopnumbe = c.sopnumbe
) T
WHERE Margin+[Percent]+Excemption+Ship+Terms>0
order by sopnumbe asc
select *
from
(
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, a.frtamnt, a.taxamnt, c.Margin
,case when b.TAXSCHID = 1 and a.TaxAmnt>0 then 1 else 0 end as Excemption
,case when a.PRSTADCD NOT IN ('SHIP TO','1') then 1 else 0 end as Ship
,case when b.PYMTRMID='Credit Card' then 1 else 0 end as Terms
from sop10100 a
left outer join rm00101 b on a.custnmbr = b.custnmbr
left outer join (SELECT sopnumbe, MAX(case when (1-(c.unitcost/c.unitprce))<0.2 then 1 else 0 end) AS Margin FROM sop10200 GROUP BY sopnumbe) c on a.sopnumbe = c.sopnumbe
) T
WHERE Margin+Excemption+Ship+Terms>0
order by sopnumbe asc
Needs checking for conditions and field names, but should be something like :
Open in new window