sql server - SQL query min(date) from one column and max(amount) from another -

i have table , looks

table have

i need create table consists of following columns

table need

table includes unique id, min date when number of sms max , max date when number of sms min, including numbers of sms both cases

i've tried several queries, feel problem not in finding min(sms) in column "sms" in general condition max(date) column "date"

so if have find maximum amount of sms sent on minimum data, should pick minimum data when number maximum. can find number of sms sent on min data.

will glad explanation

p.s.: i'm using ms sql 2014

---

that should work or @ least starting point tune:

select * table1 (values (1,'1/1/2015',10), (1,'2/1/2015',10), (1,'3/1/2015',20), (1,'4/1/2015',20), (2,'5/1/2015',30), (2,'6/1/2015',30), (2,'7/1/2015',40), (2,'8/1/2015',40) ) x ([user_id], [date], [sms]) go ;with mx (     select t1.[user_id], min(t1.[date]) [date], t1.[sms]     table1 t1     t1.[sms] = (select max(i.[sms]) [sms] table1 i.[user_id] = t1.[user_id] )      group t1.[user_id], t1.[sms] ), mi (     select t1.[user_id], max(t1.[date]) [date], t1.[sms]     table1 t1     t1.[sms] = (select min(i.[sms]) [sms] table1 i.[user_id] = t1.[user_id] )      group t1.[user_id], t1.[sms] ) select mx.[user_id], mx.[date] min_date, mx.[sms] max_sms, mi.[date] max_date, mi.[sms] min_sms mx inner join mi on mx.[user_id] = mi.[user_id]; go