Where EXISTS, subtract one line from the other

usates · November 23, 2016, 1:06pm

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.Preformatted text

ScottPletcher · November 23, 2016, 2:57pm

It's not 100% clear what you want, but maybe something like this will help:

select	  ih.DOC_NO as [Work Order]
		, ABS(ISNULL(SUM(case when ih.PARENT_DOCCATEGORY = 'S2' then ih.C_DOC_TOTAL else 0 end), 0) /*type 15*/ -
		  ISNULL(SUM(case when ih.PARENT_DOCCATEGORY = 'S1' then ih.C_DOC_TOTAL else 0 end), 0)) /*type 9*/ 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

usates · November 23, 2016, 3:17pm

Thanks for your help. Let me try to clear this up a little.

select ih.doc_no (gives me my work order number) - this is what I want
,sum(ih.c_doc_total) as [Sales] (gives me my total sales amount)-this is what I want

Now, I want to figure out my cost.

First, I want to check if a line type 15 exists in my Case Statment(I'll write this out not using sql language so you will know what I need)

Case when line_type 15 exists on a work order, subtract the sum cost of each line_type 9 from the sum cost of each line_type 15 and give me that number as the cost.

If a line_type 15 doesn't exists, then ih.partent_doccategory = 'S2' then a.cost else b.cost

I hope this helps.

ScottPletcher · November 23, 2016, 5:38pm

Sorry, I still don't fully follow. Perhaps someone else can help. Or you could post some sample data and the expected results for that data.

usates · November 23, 2016, 7:52pm

INV_HDR
DOC_NO	         PARENT_DOC_NO	   PARENT_DOCCATEGORY	       C_DOC_TOTAL
169879	         175152	                TK	               2600.00
169892	         149620	                S2	               4691.00
169898	         175172	                TK	               31565.00

INV_LINE
DOC_NO	LINE_TYPE	COST
169879	15	        5465.00
169879	09	        4000.00
169898	15 	        28408.50
169898	09	        100.00
169898  15              1000.00
169898  09              500.00

WO_HDR
DOC_NO	DOC_CATEGORY	SUBTOTAL_COST
149620	S2	        2621.74

Desired Results
DOC_NO	Sales	   Cost
169898	31565.00   28808.50
169892	4691.00	   2621.74
169879	2600.00	   1465.00

So basically, the script pulls the three doc_no from the INV_HDR. (Works)
then, it gives me Sales, (Works)

Then, the script first needs to check the three doc_no in INV_LINE to see if a line type 15 exists, if it does, I need it to sum the line type 15's and subtract the sum of line type 09's, and use that as my Cost.

If line type 15 does not exist, then check parent_doccategory for 'S2', then a.cost, else b.cost

usates · November 28, 2016, 2:07pm

Anyone have any suggestions?

Ifor · November 29, 2016, 3:42pm

Help people to help you.

Post consumable test data. Here is an example for the first table:

CREATE TABLE #INV_HDR
(
    DOC_NO int NOT NULL PRIMARY KEY
    ,PARENT_DOC_NO int NOT NULL
    ,PARENT_DOCCATEGORY char(2) NOT NULL
    ,C_DOC_TOTAL money NOT NULL
);
INSERT INTO #INV_HDR
VALUES (169879, 175152, 'TK', 2600.00)
    ,(169892, 149620, 'S2', 4691.00)
    ,(169898, 175172, 'TK', 31565.00);

Post what you want the test data to look like after it has been processed. Very few people are going to be bothered to read a long description.

usates · December 13, 2016, 5:55pm

I hope this works.

CREATE TABLE #INV_H
    (
        DOC_NO int NOT NULL PRIMARY KEY
        ,PARENT_DOC_NO int NOT NULL
        ,PARENT_DOCCATEGORY char(2) NOT NULL
        ,C_DOC_TOTAL money NOT NULL
    );
    INSERT INTO #INV_H
    VALUES (169879, 175152, 'TK', 2600.00)
        ,(169892, 149620, 'S2', 4691.00)
        ,(169898, 175172, 'TK', 31565.00);


        
    CREATE TABLE #INV_L
    (
        DOC_NO int NOT NULL PRIMARY KEY
        ,LINE_TYPE int NOT NULL
        ,COST money NOT NULL
    );
    INSERT INTO #INV_L
    VALUES (169879, 15, 5465.00)
        ,(169879, 09, 4000.00)
        ,(169898, 15, 28408.50)
        ,(169898, 09, 100.00)
        ,(169898, 15, 1000.00)
        ,(169898, 09, 500.00); 


        
    CREATE TABLE #WO_H
    (
        DOC_NO int NOT NULL PRIMARY KEY
        ,DOC_CATEGORY char(2) NOT NULL
        ,SUBTOTAL_COST money NOT NULL
    );
    INSERT INTO #WO_H
    VALUES (149620, 'S2', 2621.74);

Desired Results
DOC_NO Sales Cost
169898 31565.00 28808.50
169892 4691.00 2621.74
169879 2600.00 1465.00

The following script pulls the three doc_no from the INV_H. (Works)
then, it gives me Sales, (Works)

Then, the script first needs to check the three doc_no in INV_L to see if a line type 15 exists, if it does, I need it to sum the line type 15's and subtract the sum of line type 09's, and use that as my Cost.

If line type 15 does not exist, then check parent_doccategory for 'S2', then a.cost, else b.cost

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_H ih

outer apply
(
select sum(SUBTOTAL_cost) as Cost
from WO_H 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_L as B
where PARENT_DOCCATEGORY = 'TK'
and b.DOC_NO = ih.DOC_NO
) as B