I want to simple query that give me stock day by day details.

I wrote this code:

SELECT 
    TRNDATE, I_NAME, 
    SUM(AMOUNT) AS OPENING, SUM(PURCHASE) AS PURCHASE,
    SUM(SALE) AS SALE, SUM(AMOUNT)   SUM(PURCHASE) - SUM(SALE) AS STOCK
FROM 
    (SELECT 
         TRNDATE, I_NAME, AMOUNT AS OPENING, 0 AS PURCHASE,
         0 AS SALE, 0 AS STOCK 
     FROM DBO.ITEMMAST 
     UNION ALL
     SELECT 
         TRNDATE, I_NAME, 0 AS OPENING, AMOUNT AS PURCHASE,
         0 AS SALE, 0 AS STOCK 
     FROM DBO.PURCHASE 
     UNION ALL
     SELECT 
         TRNDATE, I_NAME, 0 AS OPENING, 0 AS PURCHASE,
         AMOUNT AS SALE,0 AS STOCK 
     FROM DBO.SALE) MAIN 
GROUP BY 
    TRNDATE, I_NAME

Output is

The result returns only same line summary

I want to be like this

I need to get line by line summary.

CodePudding user response：

I suspect this is what you are after, however, it is impossible to test. What you want is a cumulative SUM, which is the one at the end with the OVER clause. As a result you need to use another derived table.

I prefer to use CTEs over subqueries, as they are reusable through the query, so I change your query to use those and end up with the following:

WITH Items AS(
    SELECT TRNDATE,
             I_NAME,
             AMOUNT AS OPENING,
             0 AS PURCHASE,
             0 AS SALE,
             0 AS STOCK
      FROM dbo.ITEMMAST
      UNION ALL
      SELECT TRNDATE,
             I_NAME,
             0 AS OPENING,
             AMOUNT AS PURCHASE,
             0 AS SALE,
             0 AS STOCK
      FROM dbo.PURCHASE
      UNION ALL
      SELECT TRNDATE,
             I_NAME,
             0 AS OPENING,
             0 AS PURCHASE,
             AMOUNT AS SALE,
             0 AS STOCK
      FROM dbo.SALE),
Aggregrates AS(
    SELECT TRNDATE,
           I_NAME,
           SUM(OPENING) AS OPENING, --Note you had SUM(AMOUNT) in your original attempt
           SUM(PURCHASE) AS PURCHASE,
           SUM(SALE) AS SALE
    FROM Items
    GROUP BY TRNDATE,
             I_NAME)
SELECT TRNDATE,
       I_NAME,
       OPENING, 
       PURCHASE,
       SALE,
       SUM(OPENING   PURCHASE - SALE) OVER (PARTITION BY I_NAME ORDER BY TRNDATE
                                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS STOCK
FROM Aggregrates;