sql - To calculate sum of qty based on date range -

in below query have 3 tables goodsreceivednote, goodsreceivednotedetail , transfernote. in goodsreceivednote , goodsreceivednotedetail receiving product on particular date on particular location. in transfernote transferring product 1 location location. objective sum transfer qty of product based on grn date of receiving date startdate , next receiving date of product in grn end date. here show example.

[goodsreceivednote]     goodsreceivednoteid | locationid --------------------+---------------      1              |      1      2              |      1  [goodsreceivednotedetail] goodsreceivednotedetailid|goodsreceivednoteid|acceptedquantity|productid|createdon -------------------------+-------------------+----------------+---------+-----------     1                    | 1                 | 50             |  1      | 10-2-2015     2                    | 2                 | 100            |  1      | 1-3-2015  [transfernote] fromlocation |tolocation|productid|transferquantity|createdon  | -------------+----------+---------+----------------+-----------+  1              2             1       10           | 10-2-2015  1              2             1       25           | 12-2-2015  1              2             1       50           | 5-3-2015 

my expected result:

goodsreceivednoteid|locationid|acceptedquantity|productid|createdon|fromlocation |tolocation|productid|transferquantity -------------------+----------+----------------+---------+---------+-------------+----------+---------+----------------- 1                  | 1        | 50             |   1     |10-2-2015|   1         |   2      |   1     |  35       2                  | 2        | 100            | 1       |  1-3-15 |   1         |   2      |   1     |  50     select  dbo.fn_materialtransferqty(grn.locationid,grnd.productid,grnd.createdon,grnd.createdon ) goodsreceivednotedetail grnd  left outer join goodsreceivednote grn on grn.goodsreceivednoteid =grnd.goodsreceivednoteid left outer join transfernote tn on tn.productid=grnd.productid  select grn.locationid,grnd.productid,grnd.receivedquantity, dbo.fn_materialtransferqty(grn.locationid,grnd.productid,grnd.createdon,grnd.createdon) goodsreceivednotedetail grnd  left outer join goodsreceivednote grn on grn.goodsreceivednoteid =grnd.goodsreceivednoteid left outer join transfernote tn on tn.productid=grnd.productid grnd.createdon >=@i_startdate ,   grnd.createdon<=@i_enddate 

this return per product grn start , end dates, , sum quantity transfers between these 2 dates:

select distinct grn.productid, grn.createdon startdate,      (select min(createdon) goodsreceivednotedetail          productid = grn.productid , createdon > grn.createdon     ) enddate,     (select sum(transferquantity) transfernote         productid = grn.productid         , createdon >= grn.createdon          , createdon <=              (select min(createdon) goodsreceivednotedetail                  productid = grn.productid , createdon > grn.createdon)     ) quantity goodsreceivednotedetail grn 

the enddate calculated next date product in grn. quantity sum of transferquantity in transfernote between (including) startdate , enddate in grn.