ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Calculate stock quantity from multiple table transactions in SQL Server<p>Hi <a class="username" rel="nagaraju60"> nagaraju60</a>,</p> <p>Refer below sample query.</p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">DECLARE @Table_Order AS TABLE(Order_Key INT,Order_Qty DECIMAL(18,2)) INSERT INTO @Table_Order VALUES(1,180.00) INSERT INTO @Table_Order VALUES(2,150.25) INSERT INTO @Table_Order VALUES(3,125.75) &nbsp; DECLARE @Table_Recived AS TABLE(Rcvd_Key INT,FK_Order_Key INT,Received_Qty DECIMAL(18,2)) INSERT INTO @Table_Recived VALUES(1,1,50.00) INSERT INTO @Table_Recived VALUES(2,3,27.25) INSERT INTO @Table_Recived VALUES(3,1,87.00) SELECT Order_Key,Order_Qty,ISNULL(SUM(Received_Qty),0) &#39;Recvd_Qty&#39;,Order_Qty - ISNULL(SUM(Received_Qty),0) &#39;Pending_Qty&#39; FROM ( &nbsp;&nbsp; &nbsp;SELECT Order_Key,Order_Qty,Received_Qty &nbsp;&nbsp; &nbsp;FROM @Table_Order tblo &nbsp;&nbsp; &nbsp;LEFT OUTER JOIN @Table_Recived tr &nbsp;&nbsp; &nbsp;ON tblo.Order_Key = tr.FK_Order_Key) t GROUP BY Order_Key,Order_Qty ORDER BY Order_Key</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <table border="1"> <tbody> <tr style="height: 15.0pt;"> <td style="height: 15.0pt; width: 48pt;" width="64" height="20"><strong>Order_Key</strong></td> <td style="width: 48pt;" width="64"><strong>Order_Qty</strong></td> <td style="width: 48pt;" width="64"><strong>Recvd_Qty</strong></td> <td style="width: 48pt;" width="64"><strong>Pending_Qty</strong></td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" align="right" height="20">1</td> <td align="right">180.00</td> <td align="right">137.00</td> <td align="right">43.00</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" align="right" height="20">2</td> <td align="right">150.25</td> <td align="right">0.00</td> <td align="right">150.25</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" align="right" height="20">3</td> <td align="right">125.75</td> <td align="right">27.25</td> <td align="right">98.5</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/196031/Calculate-stock-quantity-from-multiple-table-transactions-in-SQL-Server/https://www.aspforums.net:443/Threads/196031/Calculate-stock-quantity-from-multiple-table-transactions-in-SQL-Server/Wed, 29 May 2019 05:05:19 GMT