hims056 hims056 - 5 months ago 13
SQL Question

Show stock details by each sales

I am trying to select data from

ProdcutStock
table by grouping it with each client, sales.

My Tables are as below

SalesMaster


Id CustId TDate
1 2 2016-06-03
2 1 2016-06-03
3 2 2016-06-06
4 2 2016-06-06
5 1 2016-06-06
6 3 2016-06-06


SalesDetails


Id SalesId ProductId Qty
1 1 2 4.00
2 1 1 5.00
3 2 2 21.00
4 2 2 88.00
5 2 1 8.00
6 2 3 9.00
7 2 3 4.00
8 2 1 77.00
9 2 2 4.00
10 2 3 8.00
11 2 2 7.00
12 3 2 41.00
13 3 3 10.00
14 4 2 25.00
15 4 1 49.00
16 5 3 50.00
17 5 1 50.00
18 6 2 10.00
19 6 3 20.00


ProductStock


Id Date OpeningStock TotalProduction TotalSales ClosingStock
1 2016-06-05 100.00 0.00 0.00 100.00
2 2016-06-06 100.00 325.00 255.00 170.00
3 2016-06-07 200.00 0.00 0.00 200.00
5 2016-06-08 200.00 0.00 0.00 200.00
6 2016-06-09 200.00 0.00 0.00 200.00
7 2016-06-10 200.00 0.00 0.00 200.00


What I am trying to achieve is the below table

Date OpeningStock Production Sales Customer ClosingStock
2016-06-05 100.00 0.00 0.00 NULL 100.00
2016-06-06 100.00 325.00 125.00 XYZ 300.00
2016-06-06 300.00 0.00 30.00 ABC 270.00
2016-06-06 270.00 0.00 100.00 PQR 170.00
2016-06-07 170.00 0.00 0.00 NULL 200.00


What I am getting using code below: (See Opening and Closing Stock)

Date Opening Production Sales Name Closing
2016-06-05 100.00 0.00 0.00 NULL 100.00
2016-06-06 100.00 325.00 125.00 XYZ 300.00
2016-06-06 100.00 325.00 30.00 ABC 395.00
2016-06-06 100.00 325.00 100.00 PQR 325.00
2016-06-07 200.00 0.00 0.00 NULL 200.00


What I have done is as below:

SELECT PS.Date, PS.OpeningStock , PS.TotalProduction
, SUM(COALESCE(SD.Qty,0)) AS SALES, CM.Name
, (COALESCE(PS.OpeningStock,0)) + COALESCE(PS.TotalProduction,0) - SUM(COALESCE(SD.Qty,0)) AS ClosingStock
FROM ProductStock PS LEFT JOIN SalesMaster SM ON PS.Date = SM.Date
LEFT JOIN SalesDetails SD ON SM.Id = SD.SalesId
LEFT JOIN CustomersMaster CM ON SM.CustomerId = CM.Id
GROUP BY PS.Date, CM.Name, PS.OpeningStock, PS.TotalProduction
ORDER BY PS.Date


I also tried to work with
LAG
as I am using SQL Server 2012 but I didn't get how to apply.

Answer

This should get you close. You can use SUM() OVER() to get a running total of SALES and just calculate the Quantities using that.

;WITH ProductDetail AS (
    SELECT  ps.[Date],
            ps.OpeningStock,
            ps.TotalProduction,
            ps.ClosingStock,
            sm.CustId,
            COALESCE(SUM(Qty),0) Sales,
            ROW_NUMBER() OVER (PARTITION BY ps.[Date] ORDER BY CustID) Rn,
            SUM(SUM(Qty)) OVER (PARTITION BY ps.[Date] ORDER BY CustID) SalesRunningTotal
    FROM    ProductStock ps
            LEFT JOIN SalesMaster sm ON ps.[Date] = sm.[Date]
            LEFT JOIN SalesDetail sd ON sd.SalesID = sm.ID
    GROUP BY ps.[Date],
            ps.OpeningStock,
            ps.TotalProduction,
            ps.ClosingStock,
            sm.CustId
)
SELECT  [Date],
        CASE WHEN Rn = 1 THEN OpeningStock ELSE OpeningStock + (TotalProduction - SalesRunningTotal + Sales) END OpeningStock,
        CASE WHEN Rn = 1 THEN TotalProduction ELSE 0 END [Production],
        Sales,
        CustId,
        CASE WHEN Rn = 1 THEN OpeningStock + TotalProduction - Sales 
            ELSE OpeningStock + (TotalProduction - SalesRunningTotal) 
            END [ClosingStock]
FROM    ProductDetail
Comments