Return min date and corresponding amount to that distinct ID

Question

Afternoon

I am trying to return the min value/ max values in SQL Server 2005 when I have multiple dates that are the same but the values in the Owed column are all different. I've already filtered the table down by my select statement into a temp table for a different query, when I've then tried to mirror I have all the duplicated dates that you can see below.

I now have a table that looks like:

ID| Date   |Owes
-----------------
1  20110901 89
1  20110901 179
1  20110901 101
1  20110901 197 
1  20110901 510
2  20111001 10
2  20111001 211
2  20111001 214 
2  20111001 669

My current query:

Drop Table #Temp

Select Distinct Convert(Varchar(8), DateAdd(dd, Datediff(DD,0,DateDue),0),112)as Date
,ID
,Paid
Into #Temp
From Table
Where Paid <> '0'

Select ,Id           
       ,Date
       ,Max(Owed)
       ,Min(Owed)          

From #Temp
Group by ID, Date, Paid
Order By ID, Date, Paid

This doesn't strip out any of my dates that are the same, I'm new to SQL but I'm presuming its because my owed column has different values. I basically want to be able to pull back the first record as this will always be my minimum paid and my last record will always be my maximum owed to work out my total owed by ID.

I'm new to SQL so would like to understand what I've done wrong for my future knowledge of structuring queries?

Many Thanks

Answer 1

In your "select into"statement, you don't have an Owed column?

GROUP BY is the normal way you "strip out values that are the same". If you group by ID and Date, you will get one row in your result for each distinct pair of values in those two columns. Each row in the results represents ALL the rows in the underlying table, and aggregate functions like MIN, MAX, etc. can pull out values.

SELECT id, date, MAX(owes) as MaxOwes, MIN(owes) as minOwes
FROM myFavoriteTable
GROUP BY id, date

In SQL Server 2005 there are "windowing functions" that allow you to use aggregate functions on groups of records, without grouping. An example below. You will get one row for each row in the table:

 SELECT id, date, owes, 
 MAX(Owes) over (PARTITION BY  select, id) AS MaxOwes,
 MIN(Owes) over (PARTITION BY select, id) AS MinOwes
 FROM myfavoriteTable

If you name a column "MinOwes" it might sound like you're just fishing tho.