Access First() function replication


Imagine a simple table where users enter timesheet information

Field                  DataType
------                 ------------
Name                 char
Project              char
Skill                   char
TimesheetDate   datetime
TimesheetVal     int

This is simplified however, imagine a user enters the amount of time they have spent working in a month on a particular skill type, on a particular project:

Name        Project            Skill          TimesheetDate         TimesheetVal
Stephen    Project A         Developer  1/1/2005                  2
Stephen    Project A         Developer  2/1/2005                  3

The above states that Stephen spent 2 hours on the 1/1/2005 working on project A as a Developer, and 3 hours on Project A, on the 2/1/2005 also as a developer.

It is fairly straight forward to create an aggregate for the year which gives  a total number of hours worked against a project, for each user and skill.

However, the problem lies in the fact that occasionally a user may switch between two skill types to book their time against:

Name        Project            Skill             TimesheetDate         TimesheetVal
Stephen    Project A         Developer      1/1/2005                  2
Stephen    Project A         Developer      2/1/2005                  3
Stephen    Project A         Project Lead   2/1/2005                  3

In the aggregrate however, I dont want two rows of data for Stephen, I only want one.  If I create a Group By clause on the Skill, I will get two rows of data as their is now two skills.  What I used to do in Access is use the first function to retrieve the first skill type for an individual that it finds, and use that.

i.e. what I want is:

Name       Project            Skill             Year                       Total Hours Worked
Stephen   Project A         Developer    2005                       8

I am thinking down the lines of Top, or Coalesce but am completely new to SQl Server 2000.  Any ideas?



Stephen Mackenzie

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

please try

select Name, Project, (select top 1 Skill from <YourTable> where Name = A.Name and Project = A.Project order by TimeSheetDate) as Skill
[Year], tot as [Total Hours Worked]
from (
    select Name, Project, Year(TimesheetDate) as [Year],
    sum(TimesheetVal) as tot
    from <YourTable>
    group by     select Name, Project, Year(TimesheetDate)
) A
Maybe it's not an implementation of Access' First() function but I use Min or Max in similar cases.

   Year(TimesheetDate) as [Year],
   SUM(TimesheetVal) as [Total Hours Worked]
group by    

SELECT a.[Name], a.Project, ( select top 1 b.skill from tableName as b where a.Name = and a.project = b.project ) As Skill, Year(a.TimesheetDate) as [Year],  SUM(a.TimesheetVal) as [Total Hours Worked]
from tableName a
group by a.[Name], a.Project, Year(a.TimesheetDate)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p_loveAuthor Commented:
Thanks everyone!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.