inventory management system in windows Form c#

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

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:

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
Accepted answer
Please change your database design...

+1
Jyoti Jodha8y ago
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.

0
Tapan Patel8y ago
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:
0
Laxmidhar Sahoo8y ago
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
0
Laxmidhar Sahoo8y ago
sir
If you have Id and transctioin_id pl Details display
0
Jyoti Jodha8y ago
how to Fetch Records From
View
0
Altaf Ansari8y ago
if you are using Sql Server.. Create a 'View' with name 'Stock_Table ' and Joint Table 'Sell_Table' and Table 'Purchase_Table'
0
Jyoti Jodha8y ago
How to Create SQL Query For fetch Data Like Stck_table...??
0
Jyoti Jodha8y ago
Yes I already have ID, Bill_No , Transction_Id

Columns In My Tables
0
Laxmidhar Sahoo8y ago
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
0