-- Populate a tempory table with the min and max dates for each code
SELECT Code, MIN(Date) AS MinDate, MAX(Date) AS MaxDate
INTO #tmp
FROM tblMain
GROUP BY Code

-- Select the amount sums for the min date of each code
SELECT m.Code, t.MinDate, SUM(m.Amount)
FROM tblMain m
INNER JOIN #tmp t
	ON m.Code = t.Code
	AND m.Date = t.MinDate
GROUP BY m.Code, t.MinDate

-- Select the amount sums for the max date of each code
SELECT m.Code, t.MaxDate, SUM(m.Amount)
FROM tblMain m
INNER JOIN #tmp t
	ON m.Code = t.Code
	AND m.Date = t.MaxDate
GROUP BY m.Code, t.MaxDate

-- Drop the temp table
DROP TABLE #tmp
GO 

select code,
sum(case when date=mindate then Amount else 0 end) as mindatetotal,
sum(case when date=maxdate then Amount else 0 end) as maxdatetotal
from
(
select code,Amount,date, min(date) over (partition by code) as mindate,max(date)  over (partition by code) as maxdate 
from tblMain 
)t
group by code

quote:

---

Originally posted by arkiboys

I wonder if row_number should be used here somehow?
Thanks

---

select code,
sum(case when fseq=1 then Amount else 0 end) as mindatetotal,
sum(case when bseq=1 then Amount else 0 end) as maxdatetotal
from
(
select code,Amount,date, row_number() over (partition by code order by date desc) as bseq, row_number() over (partition by code order by date asc) as fseq
from tblMain 
)t
where bseq=1
or fseq=1
group by code