sql - Get the first and last record of each item for the month -

product id     quantity     dateadded 1              100          4/1/14 2              200          4/2/14 3              300          4/2/14 1              80           4/3/14 3              40           4/5/14 2              5            4/6/14 1              10           4/7/14 

i using sql statement display first , last record of each item:

select      productid, min(quantity) starting, max(quantity) ending       records      dateadded between '2014-04-01' , '2014-04-30' group      productid, quantity 

but getting same values starting , ending columns. want achieve this:

product id     starting     ending 1              100          10 2              200          5 3              300          40 

you getting same quantities because aggregating quantity in group by product. version of query, written be:

select productid, min(quantity) starting, max(quantity) ending  records dateadded between '2014-04-01' , '2014-04-30' group productid; 

however, doesn't give first , last values. gives minimum , maximum ones. values, use row_number() , conditional aggregation:

select productid,        max(case when seqnum_asc = 1 quantity end) starting,        max(case when seqnum_desc = 1 quantity end) ending (select r.*,              row_number() on (partition product order dateadded asc) seqnum_asc,              row_number() on (partition product order dateadded desc) seqnum_desc       records r      ) r dateadded between '2014-04-01' , '2014-04-30' group productid; 

if using sql server 2012, can use first_value() , last_value() instead of row_number().