Q:

Select distinct record with multiple time related

I have a database that contains staff awarding history so in future we can calculate how many awards are awarded to a specific staff. I have another column that contains information about how many times a staff arrived to work without a smart dress, which has date associated. I would like to calculate the numbers of awards awarded to each staff, numbers to time a staff fail to wear a company's uniform. Currently my database looks:
  ID   fName   sName     awards    smart    date  
  1    jack    Mack        1        0       2014-05-06
  2    Jimmy   Jack        0        1       2014-04-06
  3    Jack    Mack        1        1       2014-05-04
  4    Jack    Mack        1        0       2014-02-03
  5    Jimmy   Jack        0        1       2014-05-01

Based on above table I have created another table, that should have only single entry for a record e.g. 
  ID   fName   sName     awards    smart    date  
  1    jack    Mack        3        1       2014-05-06
  2    Jimmy   Jack        0        2       2014-04-06

But in here the problem is the date, Jack is been given 3 awards in different days. In future if I need to select records between two dates then how would I go about and do this because in my table I have removed repeated record and what SQL does is, it takes the top date and use it. 
If I count number of 1's in my first table (where records are being repeated), I get wrong data my query is: 
select ID, fName, sName, awards, smart, date, 
    count(case awards when 1 then 1 else null end) 
from tablename 
group by ID, fName, sName, awards, smart, date

The reason for receiving wrong data is once again due to time? Is there any way to get around this problem?   

A:

When you group records (e.g. to get totals, etc.), it makes sense to return only those columns that you can use for grouping. This means only those columns that are the basis of your group for calculations. In your case, only fName and sName are used for grouping (as you're grouping for unique staff).
(Side note: you should have some sort of unique identifier for your staff in this query as names are not necessarily unique).
Therefore, you should only include those columns in group by. You can use sum function to get the number of awards and smart as they have values of 0 and 1.
select fName, sName, 
    sum(convert(int, awards)) as awards,
    sum(convert(int, smart)) as smart,
    max([date]) as maxdate
from tablename 
group by fName, sName

See this SQL Fiddle (I added only relevant columns).

