Search code examples
sqlsql-serverselectinner-joininventory-management

Sql Server : Select query with calculation on rows one after another

Table PURCHASE(PDate, ItemName, PQty, Rate)

PDate       | ItemName     | PQty  | Rate
============================================
01-04-2015  | Mobile Phone | 06    | 05,000
01-04-2015  | Laptop       | 07    | 30,000
03-04-2015  | Mobile Phone | 03    | 06,000
04-04-2015  | Mobile Phone | 08    | 07,000
04-04-2015  | Laptop       | 03    | 40,000
05-04-2015  | Mobile Phone | 05    | 04,000

Table SALES(SDate, ItemName, SQty)

SDate       | ItemName     | SQty
=================================
02-04-2015  | Mobile Phone | 02
03-04-2015  | Laptop       | 03
03-04-2015  | Mobile Phone | 05
04-04-2015  | Mobile Phone | 07
04-04-2015  | Laptop       | 03
06-04-2015  | Laptop       | 02

SQL

  • Here we need to view the Remaining Items and their Stock Values (PQty*Rate) from PURCHASE Table after sales. (Closing Stock)
  • The select query should do the process here.
  • The PURCHASE table's PQty should be deducted One by One, Top to Bottom in Ascending order of PDate based on the SALES table's SQty One by One, Top to Bottom in Ascending order of Date.
  • The PQty of PURCHASE Table should not become 0 (Zero) Which is ofcourse.
  • Incase, if the PQty reaches to Zero, Deduct the remaining SQty of SALES from the next row of PURCHASE Table's PQty in the list.
  • The last row may have a negative value if no more rows are available.
  • Condition between the tables is done WHERE SALES.ItemName LIKE PURCHASE.ItemName

Table EXPECTED SELECT QUERY RESULT (Date, ItemName, Qty, Rate, ProductValue)

PDate       | ItemName     | PQty  | Rate   | ProductValue
===========================================================
01-04-2015  | Mobile Phone | 00    | 05,000 |      0.00
01-04-2015  | Laptop       | 00    | 30,000 |      0.00
03-04-2015  | Mobile Phone | 00    | 06,000 |      0.00
04-04-2015  | Mobile Phone | 03    | 07,000 | 21,000.00
04-04-2015  | Laptop       | 02    | 40,000 | 80,000.00
05-04-2015  | Mobile Phone | 05    | 04,000 | 20,000.00

How can I sort out this problem?

Solution 
  • SELECT PDate, ItemName, MPQty, PQty, ISNULL(SQty,0) AS SQty,
ISNULL((CASE WHEN SQTY>=PQty THEN 0 WHEN (SQty+MPQty)>PQty THEN PQty-SQty ELSE MPQty END),0) AS Qty, Rate,
(ISNULL((CASE WHEN SQTY>=PQty THEN 0 WHEN (SQty+MPQty)>PQty THEN PQty-SQty ELSE MPQty END),0)*Rate) AS PValue FROM
(SELECT P.PDate AS PDate, P.ItemName AS ItemName, P.PQty AS MPQty,
(SELECT SUM(P1.PQty) FROM PURCHASE P1 WHERE P1.ItemName=P.ItemName AND P1.PDate<=P.PDate) AS PQty,
(SELECT SUM(S1.SQty) FROM SALES S1 WHERE S1.ItemName=P.ItemName) AS SQty,    
P.Rate AS Rate FROM PURCHASE P)X ORDER BY Pdate