I have two queries from two views

Query 1:

SELECT sum(cancels) as cancelcount,codeprogram 
FROM CanceliTems 
WHERE date_save BETWEEN '1/21/2022' AND 
'2/20/2022' and usercode = 8066
GROUP By codeprogram

and the result is:

Query 2:

SELECT SUM(sellcount) sellcount,codeprogram,price 
FROM sells
WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
GROUP By codeprogram,price

and the result is:

How can I join two views and then subtract the number of cancel from the number of sales and finally multiply the number by the price, At a specified date range?

In every view I have field date_save (datetime) and it filled by getdate() and usercode.

finalcount = (sellcount - cancelcount) * price

I want the final output to be as follows:

Thanks

CodePudding user response：

One way you can try to use two subqueries with OUTER JOIN

SELECT sellcount - ISNULL(cancelcount,0) finalcount,
       t1.codeprogram,
       t1.price
FROM (
    SELECT SUM(sellcount) sellcount,codeprogram,price 
    FROM sells
    WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
    GROUP By codeprogram,price
) t1
LEFT JOIN (
    SELECT sum(cancels) as cancelcount,codeprogram 
    FROM CanceliTems 
    WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
    GROUP By codeprogram
) t2 ON t1.codeprogram = t2.codeprogram

From your query, you might rewrite as below, use UNION ALL combine sells & CanceliTems and make a flag to represent that 1 is positive number, 2 is negative number then use condition aggregate function.

SELECT SUM(CASE WHEN flag = 1 THEN sellcount ELSE - sellcount END),codeprogram,SUM(price) price
FROM (
    SELECT sellcount,codeprogram,price , 1 flag
    FROM sells
    WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
    UNION ALL
    SELECT cancels,codeprogram,0 , 2
    FROM CanceliTems
    WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
) t1
GROUP BY codeprogram