I have two tables one is item master and other is SPDet.Item master contains item name,item code,rate etc and Spdet contains item code and quantity,voucher type (sale,purchase),saledate. now i want to show summary of stock between date ranges how to do that.
this is my table design
item Mast
PCode int
Pname nvarchar(100)
SrNo nvarchar(100)
Unit nvarchar(20)
SRate float
prate nvarchar(20)
OPqty int
TotIn int
TotOut int
Clqty int
IVcode int
OVCode int
ItDisc float
Img nvarchar(200)
ImgUrl nvarchar(200)
Edate nvarchar(50)
Ucode int
Spdet
SPTID int
Vtype nvarchar(2)
SPNo nvarchar(20)
SPDt date
PCode int
Qty int
Unit nvarchar(20)
Rate float
ItDisc float
Amt float
ItVcode int
ItVAmt float
ItVper float
EDate nvarchar(30)
UCode int
now i want to get summary of a product between two dates.for that i need to get opening balance of that product before the given date and then i need to get total sale,total purchase between given date and calculate closing quantity. can anyone help me how to do it with sql query/proedure etc
1 VAIO 1 1234566 Pcs 1000055 8000555 0 0 0 0 4 12 100 12/02/2014 08:47:25 PM 1
2 Lenevo Laptop 2 1234lv Pcs 30000 25000 0 8 6 2 0 10 10 20140212090101PM_Desert.jpg 12/02/2014 09:01:01 PM 1
3 LCD 32" 1 S1234 Pcs 0 0 0 10 0 10 0 0 0 13/02/2014 02:45:44 AM 1
4 LED 32" 2 AC1234 Pcs 0 0 0 10 0 10 0 0 0 13/02/2014 02:50:40 AM 1
5 SS 1 Pcs 10.5 10 0 0 0 0 0 0 5 13/02/2014 11:36:47 AM 1
6 DD 0 Pcs 0 0 0 0 0 0 0 0 0 13/02/2014 11:41:34 AM 1
7 Pendrive 0 Pcs 0 0 0 50 0 50 0 0 0 13/02/2014 11:47:25 AM 1
8 Raid & Tailor 4 Pcs 400 250 0 350 5 345 0 0 0.5 13/02/2014 12:05:13 PM 1
9 Laptop 5 LN123456 Pcs 0 0 0 25 5 20 4 3 10 17/02/2014 11:05:50 AM 1
10 Tab 5 Tab12345 Pcs 5000 4500 0 15 6 9 0 0 0 17/02/2014 11:30:45 AM 1
11 Lenevo Desktop 5 LN54321 Pcs 5000 4000 0 10 1 9 1 2 10 24/02/2014 11:10:47 PM 1