inventory management system in windows Form c#

Question

how to create Stock Table form Sell Table & Purchase Table

Sell_Table

Date	Bill No	Product Name	Unit	Qty	Rate	Total Amount
25/11/2017	13	Notebook	Pic	100	10	1000
25/11/2017	12	Keyboard	Pic	10	300	3000
25/10/2017	11	Mobile	Pic	5	5000	25000
10/10/20 17	10	Keyboard	Pic	20	300	6000
5/8/2017	9	Mobile	Pic	10	500	5000

Purchase_Table

Date	Product Name	Unit	Qty	Rate	Total Amount
5/11/2017	Notebook	Pic	500	10	5000
25/10/2017	Keyboard	Pic	100	300	30000
25/10/2017	Mobile	Pic	10	5000	50000
10/10/2017	Keyboard	Pic	20	300	6000
5/8/2017	Mobile	Pic	100	500	50000

Stock_Table

Month	Product Name	Unit	Opening_Balance	TotalSold	Closing_Balance
Nov. 2017	Notebook	Pic	500	100	400
Nov. 2017	Keyboard	Pic	100	10	90
Oct 2017	Keyboard	Pic	20+100=120	20	100
Oct 2017	Mobile	Pic	90+10 = 100	5	95
Aug 2017	Mobile	Pic	100	10	90

Dharmraj Thakur · Answer

Please change your database design...

Tapan Patel · Answer

Really tricky question in terms of getting final output. Like other suggestions, if you have a liberty to change the table design, do that and try again. If you don't have a liberty to do that, I have two solutions. Try optimizing it if there is a performance issues. Using the view: Create a view using below select query (note: I have created temp tables to test out the changes) Also, please don't focus on meaning of aliases. I was in rush. :) CREATE TABLE #Sell (sDate DATE , BillNo INT , ProductName VARCHAR (50), Unit CHAR (5), Qty INT , Rate INT ,TotalAmount INT ) CREATE TABLE #Purchase (sDate DATE , ProductName VARCHAR (50), Unit CHAR (5), Qty INT , Rate INT ,TotalAmount INT ) INSERT INTO #Sell ( sDate , BillNo , ProductName , Unit , Qty , Rate , TotalAmount ) select convert ( date , '25-11-2017' ,103) ,13 , 'Notebook' , 'Pic' , 100 ,10 ,1000 UNION select convert ( date , '25-11-2017' ,103) ,12 , 'Keyboard' , 'Pic' , 10 ,300 ,3000 UNION select convert ( date , '25-10-2017' ,103) ,11 , 'Mobile' , 'Pic' , 5 ,5000 ,25000 UNION select convert ( date , '10-10-2017' ,103) ,10 , 'Keyboard' , 'Pic' , 20 ,300 ,6000 UNION select convert ( date , '05-08-2017' ,103) ,9 , 'Mobile' , 'Pic' , 10 ,500 ,5000 INSERT INTO #Purchase SELECT convert ( DATE , '05-11-2017' ,103), 'Notebook' , 'Pic' , 500 ,10 ,5000 UNION SELECT convert ( DATE , '25-10-2017' ,103), 'Keyboard' , 'Pic' , 100 ,300 ,30000 UNION SELECT convert ( DATE , '25-10-2017' ,103), 'Mobile' , 'Pic' , 10 ,5000 ,50000 UNION SELECT convert ( DATE , '10-10-2017' ,103), 'Keyboard' , 'Pic' , 20 ,300 ,6000 UNION SELECT convert ( DATE , '05-08-2017' ,103), 'Mobile' , 'Pic' , 100 ,500 ,50000 SELECT * FROM #Sell SELECT * FROM #Purchase select FORMAT(TI.sDat, 'MMM, yyyy' ) [ Month ], TI.ProductName , TI.Unit , (TI.buys + ISNULL (TCE.ClosingBalance,0)) [OpeningBalance] , TI.TotalSold , TI.ClosingBalance FROM ( SELECT SCE1.sDat, SCE1.ProductName, SCE1.Unit, SCE1.buys, ABS (SCE1.sells) [TotalSold], SUM (SCE1.buys + SCE1.sells) OVER (PARTITION BY SCE1.ProductName ORDER BY SCE1.sDat)[ClosingBalance], ROW_NUMBER() OVER (PARTITION BY SCE1.ProductName ORDER BY SCE1.sDat) [Rnk], 0 [OpeningBalance] FROM ( SELECT DISTINCT DATEFROMPARTS( YEAR (CE.sDate), MONTH (CE.sDate),1) [sDat] , --CE.sDate, CE.ProductName , CE.Unit , SUM (CE.Buy) [buys], SUM (CE.Sell) [sells] FROM ( SELECT SL.sDate, SL.ProductName , SL.Unit , SL.Qty, -1 [Indicator] , 0 [Buy], SL.Qty*-1 [Sell] FROM #Sell SL UNION SELECT PCH.sDate, PCH.ProductName , PCH.Unit , PCH.Qty, 1 [Indicator] , PCH.Qty, 0 FROM #Purchase PCH ) AS CE GROUP BY DATEFROMPARTS( YEAR (CE.sDate), MONTH (CE.sDate), 1) , CE.ProductName , CE.Unit ) AS SCE1) AS TI LEFT JOIN ( SELECT SCE11.sDat, SCE11.ProductName, SCE11.Unit, SCE11.buys, ABS (SCE11.sells) [TotalSold], SUM (SCE11.buys + SCE11.sells) OVER (PARTITION BY SCE11.ProductName ORDER BY SCE11.sDat)[ClosingBalance], ROW_NUMBER() OVER (PARTITION BY SCE11.ProductName ORDER BY SCE11.sDat) [Rnk], 0 [OpeningBalance] FROM ( SELECT DISTINCT DATEFROMPARTS( YEAR (sCE.sDate), MONTH (sCE.sDate),1) [sDat] , --CE.sDate, sCE.ProductName , sCE.Unit , SUM (sCE.Buy) [buys], SUM (sCE.Sell) [sells] FROM ( SELECT SL.sDate, SL.ProductName , SL.Unit , SL.Qty, -1 [Indicator] , 0 [Buy], SL.Qty*-1 [Sell] FROM #Sell SL UNION SELECT PCH.sDate, PCH.ProductName , PCH.Unit , PCH.Qty, 1 [Indicator] , PCH.Qty, 0 FROM #Purchase PCH ) AS sCE GROUP BY DATEFROMPARTS( YEAR (sCE.sDate), MONTH (sCE.sDate), 1) , sCE.ProductName , sCE.Unit ) AS SCE11 ) AS TCE ON TCE.ProductName = TI.ProductName AND TCE.Rnk+1=TI.Rnk ORDER BY TI.[sDat] DESC ,TI.ProductName ASC If you are looking to create this table using sort of SP (that involves temp tables and all ) then here is the shorter version of the same. CREATE TABLE #Sell (sDate DATE , BillNo INT , ProductName VARCHAR (50), Unit CHAR (5), Qty INT , Rate INT ,TotalAmount INT ) CREATE TABLE #Purchase (sDate DATE , ProductName VARCHAR (50), Unit CHAR (5), Qty INT , Rate INT ,TotalAmount INT ) INSERT INTO #Sell ( sDate , BillNo , ProductName , Unit , Qty , Rate , TotalAmount ) select convert ( date , '25-11-2017' ,103) ,13 , 'Notebook' , 'Pic' , 100 ,10 ,1000 UNION select convert ( date , '25-11-2017' ,103) ,12 , 'Keyboard' , 'Pic' , 10 ,300 ,3000 UNION select convert ( date , '25-10-2017' ,103) ,11 , 'Mobile' , 'Pic' , 5 ,5000 ,25000 UNION select convert ( date , '10-10-2017' ,103) ,10 , 'Keyboard' , 'Pic' , 20 ,300 ,6000 UNION select convert ( date , '05-08-2017' ,103) ,9 , 'Mobile' , 'Pic' , 10 ,500 ,5000 INSERT INTO #Purchase SELECT convert ( DATE , '05-11-2017' ,103), 'Notebook' , 'Pic' , 500 ,10 ,5000 UNION SELECT convert ( DATE , '25-10-2017' ,103), 'Keyboard' , 'Pic' , 100 ,300 ,30000 UNION SELECT convert ( DATE , '25-10-2017' ,103), 'Mobile' , 'Pic' , 10 ,5000 ,50000 UNION SELECT convert ( DATE , '10-10-2017' ,103), 'Keyboard' , 'Pic' , 20 ,300 ,6000 UNION SELECT convert ( DATE , '05-08-2017' ,103), 'Mobile' , 'Pic' , 100 ,500 ,50000 SELECT * FROM #Sell SELECT * FROM #Purchase SELECT IDENTITY( INT ,1,1) [ID], CE1.sDat, CE1.ProductName, CE1.Unit, CE1.buys, ABS (CE1.sells) [TotalSold], SUM (CE1.buys + CE1.sells) OVER (PARTITION BY CE1.ProductName ORDER BY CE1.sDat)[ClosingBalance], ROW_NUMBER() OVER (PARTITION BY CE1.ProductName ORDER BY CE1.sDat) [Rnk], 0 [OpeningBalance] INTO #Summary FROM ( SELECT DISTINCT DATEFROMPARTS( YEAR (CE.sDate), MONTH (CE.sDate),1) [sDat] , --CE.sDate, CE.ProductName , CE.Unit , SUM (CE.Buy) [buys], SUM (CE.Sell) [sells] FROM ( SELECT SL.sDate, SL.ProductName , SL.Unit , SL.Qty, -1 [Indicator] , 0 [Buy], SL.Qty*-1 [Sell] FROM #Sell SL UNION SELECT PCH.sDate, PCH.ProductName , PCH.Unit , PCH.Qty, 1 [Indicator] , PCH.Qty, 0 FROM #Purchase PCH ) AS CE GROUP BY DATEFROMPARTS( YEAR (CE.sDate), MONTH (CE.sDate), 1) , CE.ProductName , CE.Unit ) AS CE1 ORDER BY CE1.[sDat] DESC ,CE1.ProductName ASC UPDATE #Summary SET #Summary.OpeningBalance = #Summary.buys + ISNULL (S2.ClosingBalance,0) FROM #Summary LEFT JOIN #Summary S2 ON S2.ProductName = #Summary.ProductName AND S2.Rnk+1 = #Summary.Rnk SELECT FORMAT(S1.sDat, 'MMM, yyyy' ) [ Month ] , S1.ProductName , S1.Unit , S1.OpeningBalance, S1.TotalSold , S1.ClosingBalance FROM #Summary S1 ORDER BY S1.sDat DESC --DROP TABLE #Purchase --DROP TABLE #Sell --DROP TABLE #Summary In both the cases, result would be:

Jyoti Jodha · Answer

Hello Tapan Patel Error Show Like this.. In Sql Server 2008R2 Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'order'. Msg 195, Level 15, State 10, Line 16 'DATEFROMPARTS' is not a recognized built-in function name. Msg 156, Level 15, State 1, Line 44 Incorrect syntax near the keyword 'AS'. Msg 195, Level 15, State 10, Line 59 'FORMAT' is not a recognized built-in function name.

Laxmidhar Sahoo · Answer

The stock table of atributes like bellow ID - int Auto increment PruductId - int FK Productmaster Transction_Type - varchar(20) this field value should be sale or purchase BillNo - (when purchase purchase billno otherwise sales billno) Qty - int/double stock - int/double(qty plus when purchase otherwise qty minus from stock) Date_of_Tranction

Laxmidhar Sahoo · Answer

sir If you have Id and transctioin_id pl Details display

Jyoti Jodha · Answer

how to Fetch Records From View

Altaf Ansari · Answer

if you are using Sql Server.. Create a ' View ' with name ' Stock_Table ' and Joint Table ' Sell_Table ' and Table ' Purchase_Table '

Jyoti Jodha · Answer

How to Create SQL Query For fetch Data Like Stck_table...??

Jyoti Jodha · Answer

Yes I already have ID, Bill_No , Transction_Id Columns In My Tables

Laxmidhar Sahoo · Answer

sir 1-Each table should contain ID field int data type with auto increment 2-Some columns add in purchase_table like BillNo or id 3-some colums add in Stock_Table like Transction_Id(link to sale and purchase table), Transction_Type (varcharchar20) and value like in for purchase and out for sale other fields are currect