Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mchath
Starting Member
3 Posts |
 Posted - 2008-12-09 : 00:26:07
|
| Hi, I have sales and purchase table.. How write a query to get opening stock for each day.. For eg : if between 1/12/2008 and 30/12/2008 it should display opening stock for each day.. Thanks in advanceRegards,Shijith |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-09 : 03:22:00
|
| which fields contain opening stock info? |
 |
|
|
mchath
Starting Member
3 Posts |
Posted - 2008-12-09 : 03:45:37
|
| SELECT ISNULL((SELECT sum(PU_Quantity) as PU_Quantity FROM SM_PURCHASE wHERE S_I_ID = PU_I_ID AND PU_DATE <=@i_from_Date ),0)- ISNULL((SELECT sum(S_QUANTITY) as S_QUANTITY FROM SM_SALES WHERE S_I_ID = PU_I_ID AND S_DATE <= @i_from_Date ),0) AS 'STOCK' FROM SM_PURCHASE INNER JOIN SM_SALES ON PU_I_ID = S_I_ID GROUP BY S_I_ID,PU_I_IDIn both field contains quantity field.. Purchase - Sales for each day is the requried outputThe above query retrieves opening stock for the particular period.. But i need retrieve it for each day.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-09 : 10:56:11
|
| [code]SELECT S_DATE, sum(PU_Quantity) - sum(S_QUANTITY) AS STOCKFROM SM_PURCHASEINNER JOIN SM_SALESON PU_I_ID = S_I_IDGROUP BY S_DATE[/code] |
|
|
|
mchath
Starting Member
3 Posts |
Posted - 2008-12-09 : 22:46:26
|
| Thanks , How to get the purchase - sales for a particular day,The above query will retrieve current opening stock. |
|
|
|
|
|
|
Opening Stock