Here is my current script:
select ih.DOC_NO as [Work Order] , SUM(ih.C_DOC_TOTAL) as [Sales] , case when ih.PARENT_DOCCATEGORY = 'S2' then a.cost else b.Cost end as [Cost] from INV_HDR ih outer apply ( select sum(SUBTOTAL_cost) as Cost from WO_HDR as A where DOC_CATEGORY = 'S2' and A.DOC_NO = ih.PARENT_DOC_NO ) as a outer apply ( select SUM(cost) as Cost from INV_LINE as B where PARENT_DOCCATEGORY = 'TK' and b.DOC_NO = ih.DOC_NO ) as B where ih.POST_STK_DATE > '2016-11-22' and ih.POST_STK_DATE < '2016-11-23' group by ih.doc_no, a.Cost, b.Cost, ih.PARENT_DOCCATEGORY order by ih.DOC_NO desc
A. I'm trying to make this script execute the quickest I can due to issues with Power BI auto-refresh so if this can be written more efficient, please give me your suggestions.
B. Sometimes, a Work Order has two lines, a line type 15 and line type 9. Sometimes, a Work Order has 4 lines, two line type 15 and two line type 9. When line type 15 Exists, I need to subtract the Cost from line type 9 from the Cost of line type 15......something like sum(c.cost)-sum(d.cost) where line type 15 is C and line type 9 is D. This statement will be first in my Case statement to see if a line type 15 Exists. If it does not exist, then we go to the next line in the case statement.
Anyway, thank you for your time.