Sql query with sum and aggregate function

See more:

SQL-Server

SQL-Server-2008

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