Syntax sql server [closed]

I have a problem in syntax sql, i have 3 tables like this

Table : Stock

StockCode | StockName  | BeginStock
0001      | Mouse      | 5
0002      | Keyboard   | 1
0003      | MousePad   | 4

Table : Purchase

NoDoc  | StockCode | PurchaseDate | Qty
P-0001 | 0001      | 06/12/2019   | 1
P-0002 | 0002      | 06/20/2019   | 5
P-0003 | 0002      | 06/21/2019   | 2
P-0004 | 0003      | 06/22/2019   | 1
P-0005 | 0001      | 07/01/2019   | 2

Table : Sales

NoDoc  | StockCode | PurchaseDate | Qty
S-0001 | 0001      | 06/30/2019   | 1
S-0002 | 0002      | 06/30/2019   | 1
S-0003 | 0002      | 06/30/2019   | 1
S-0004 | 0003      | 06/30/2019   | 1
S-0005 | 0003      | 07/01/2019   | 1

I want if i query for date >= ’07/01/2019′ and date <=’07/07/2019′ the stock will be

StockCode | StockName  | BeginStock | Purchase | Sales | Actual 
0001      | Mouse      | 5          | 1        | 0     | 6
0002      | Keyboard   | 6          | 0        | 0     | 6
0003      | MousePad   | 4          | 0        | 1     | 3

i want to know the beginstock in before month and after month, please help me how the query in sql. Sorry i’m the newbie. Thank’s for helping

Answer

Assuming all stock codes are present in the Stock table, you can use CROSS APPLY like this:

SELECT  St.StockCode, St.StockName, St.BeginStock,
        ISNULL(X.Purchase,0) AS Purchase, ISNULL(Y.Sales,0) AS Sales,
        St.BeginStock+ISNULL(X.Purchase,0)-ISNULL(Y.Sales,0) AS Actual
FROM Stock St
CROSS APPLY (
    SELECT SUM(P.Qty) AS Purchase
    FROM Purchase P
    WHERE P.StockCode=St.StockCode
    AND P.PurchaseDate BETWEEN '20190701' AND '20190707'
) X
CROSS APPLY (
    SELECT SUM(Sa.Qty) AS Sales
    FROM Sales Sa
    WHERE Sa.StockCode=St.StockCode
    AND Sa.PurchaseDate BETWEEN '20190701' AND '20190707'
) Y

Alternatively, you can use LEFT JOIN with GROUP BY in the derived tables, like this:

SELECT  St.StockCode, St.StockName, St.BeginStock,
        ISNULL(X.Purchase,0) AS Purchase, ISNULL(Y.Sales,0) AS Sales,
        St.BeginStock+ISNULL(X.Purchase,0)-ISNULL(Y.Sales,0) AS Actual
FROM Stock St
LEFT JOIN (
    SELECT P.StockCode, SUM(P.Qty) AS Purchase
    FROM Purchase P
    WHERE P.PurchaseDate BETWEEN '20190701' AND '20190707'
    GROUP BY P.StockCode
) X ON St.StockCode=X.StockCode
LEFT JOIN (
    SELECT Sa.StockCode, SUM(Sa.Qty) AS Purchase
    FROM Sales Sa
    WHERE Sa.PurchaseDate BETWEEN '20190701' AND '20190707'
    GROUP BY Sa.StockCode
) Y ON St.StockCode=Y.StockCode