I need to combine several tables together and I do not know the best way to go about it. I am selecting information about sales orders and the tables I am wanting to pull from are the following:
Sales Order Header (SOP10100)
Customer Main (RM00101)-----------------
---Sales Order Line Items (SOP10200)
Customer details (RMxxxx I still have to figure out this table name)
I then want to add these where statements to it...
Line Marging Check - Add check for $0.00 line items and line item margins less than 20% (from SOP10200)
Tax Percent Check - Add check for sales tax from customer tax schedule (rm00101) * document total (SOP10100)
Exmpetion Check - Add check for sales tax on tax exempt companies (RMxxx, SOP00100)
Ship To Check - Add check for ship to address id <> to '1' or 'SHIP TO' (SOP10100)
Terms Check - Add check for terms = credit card (RM00101)
I have posted some code for what I have so far. I am having trouble with the Joins for this statement.
What I would like to do is run each one of the "checks" and return a bit flag to state whether or not it failed or passed for each check. I only what to return rows where there is at least one failed check. I will then use the 1 or 0 to show a green 'check' or red 'x' in each of the check columns of the SSRS Report.
Order Number Margin Percent Exemption Terms
I001234 x 'check' 'check' x
I001235 x x x 'check'
I001236 'check' 'check' x 'check'
Let me know if you have any questions.
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