How to Create Sql Query in Windows Form C#

Do not execute this query directly first understand all statements and thing about your requirement... here is basic queries, let me know next queries later after understanding this...

--get current stock of product
Select ProductID, ProductName, ClosingStockQTy, ClosingStockRs from ProductMaster where Product = 1
--on purchase add stock
insert into Purchases (BillNo, BillDate, ProductID, Rate, Discount, Qty, TotalAmount)
values
(
'CP1',
getdate(),
@ProductID,
@Rate,
@Discount,
@Qty,
@TotalAmount
)
declare @StockQTy as int=0;
set @StockQTy = (select top 1 ClosingStockQty from ProductMaster where ProductID = 1);
set @StockQTy = @StockQTy + @Qty;
Update ProductMaster
set
ClosingStockQTy = @StockQTy
Where
ProductID = 1
--on sale decrease stock
insert into Sales (BillNo, BillDate, ProductID, Rate, Discount, Qty, TotalAmount)
values
(
'CS1',
getdate(),
@ProductID,
@Rate,
@Discount,
@Qty,
@TotalAmount
)
declare @StockQTy as int=0;
set @StockQTy = (select top 1 ClosingStockQty from ProductMaster where ProductID = 1);
set @StockQTy = @StockQTy - @Qty;
Update ProductMaster
set
ClosingStockQTy = @StockQTy
Where
ProductID = 1

2 Replies

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

Dharmraj Thakur8y ago
Do not execute this query directly first understand all statements and thing about your requirement... here is basic queries, let me know next queries later after understanding this...
--get current stock of product

Select ProductID, ProductName, ClosingStockQTy, ClosingStockRs from ProductMaster where Product = 1



--on purchase add stock

insert into Purchases (BillNo, BillDate, ProductID, Rate, Discount, Qty, TotalAmount)

values

(

    'CP1',

    getdate(),

    @ProductID,

    @Rate,

    @Discount,

    @Qty,

    @TotalAmount

)



declare @StockQTy as int=0;

set @StockQTy = (select top 1 ClosingStockQty from ProductMaster where ProductID = 1);

set @StockQTy = @StockQTy + @Qty;



Update ProductMaster

set

    ClosingStockQTy = @StockQTy

Where

    ProductID = 1





--on sale decrease stock

insert into Sales (BillNo, BillDate, ProductID, Rate, Discount, Qty, TotalAmount)

values

(

    'CS1',

    getdate(),

    @ProductID,

    @Rate,

    @Discount,

    @Qty,

    @TotalAmount

)



declare @StockQTy as int=0;

set @StockQTy = (select top 1 ClosingStockQty from ProductMaster where ProductID = 1);

set @StockQTy = @StockQTy - @Qty;



Update ProductMaster

set

    ClosingStockQTy = @StockQTy

Where

    ProductID = 1
Ramesh Barik8y ago
You can use inner join and left join to get the result.
select PM.ProductID, PM.ProductName, PM.MRP,
Purchase.BillDate as PurchaseBillDate,Purchase.Rate as PurchaseRate,
Sales.BillDate as SalesBillDate,Sales.Rate as SalesRate
from Productmaster PM
inner join Purchase on PM.ProductID=Purchase.ProductID
left join Sales on PM.ProductID = Sales.ProductID
where PM.ProductID= 1000