How to find stock value through SQL?

Question

I have two tables, one for Purchase and the other for Sales. Data is being entered on a daily basis. I want to find out the value of my stock at the end of the month. Below is an example of an Excel seat in the picture.
My stock value will be the result of subtraction of first month sales value from my purchase value in the first month.
Subtraction results from the first + second month sales value of my first + second month sales value in the second month.
First + second + third month sales value minus first + second + third month sales value.
It will continue like this.
I would have benefited if an experienced person had wasted his precious time and cooperated in the solution. Thanks in advance

Answer 1

CREATE TABLE dbo.Purchases (date  date NOT NULL,
                            qty   int  NOT NULL,
                            CONSTRAINT pk_Purchases PRIMARY KEY (date))
go
INSERT dbo.Purchases(date, qty)
   VALUES('20210101', 100),
         ('20210108', 121),
         ('20210110', 421),
         ('20210206', 610),
         ('20210209', 521),
         ('20210210', 124)
go
CREATE TABLE dbo.Sales (date  date NOT NULL,
                       qty   int  NOT NULL,
                       CONSTRAINT pk_Sales PRIMARY KEY (date))
go
INSERT dbo.Sales(date, qty)
   VALUES('20210101',  95),
         ('20210102', 150),
         ('20210113', 169),
         ('20210201', 521),
         ('20210202', 148),
         ('20210213', 290)
go
WITH TheUnion (Month, Year, Purchases, Sales) AS (
   SELECT month(date), year(date), qty, NULL
   FROM   dbo.Purchases
   UNION ALL
   SELECT month(date), year(date), NULL, qty
   FROM   dbo.Sales
), aggr (Month, Year, Purchases, Sales) AS (
   SELECT Month,  Year, SUM(Purchases), SUM(Sales)
   FROM   TheUnion
   GROUP  BY Month, Year
)
SELECT Month, Year, Purchases, Sales,
       Stock = SUM(Purchases - Sales) OVER(ORDER BY Month, Year
                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM   aggr
go
DROP TABLE dbo.Sales, dbo.Purchases

The first part creates the tables and populates it with the test data. Then comes the actual query.

Next time you have a question like this, we appreciate if you provide CREATE TABLE + INSERT to save us from that work.