Display distinct rows of a table with the sum of a column of all duplicate rows in SQL Server 2008 -

there 2 tables :

tasks table :

    taskname (pk) 

taskallocation table :

     allocationid(pk),      taskname(f.k taskname in 'tasks' table),      userid( f.k id in 'users' table),      engineertype( f.k id in 'engineertype' table),               start date,      end date,       hours,      location 

'users' table :

     id,      firstname,      lastname 

'engineertypes' table :

     id,      type 

each task can have multiple allocations.hence, taskname can occur multiple times in task allocation table. same task can mapped multiple users (userid)

i need display selected task (given input u.i), userids allocated task, first occurrence of start date, first occurrence of end date , sum(hours) each user of selected tasks.

example: taskallocation data :

     taskname  userid   typeid startdate enddate hours location       task1    1          11    feb 5     feb 7   1     null       task1    1          11    feb 6     feb 7   2     null       task1    1          11    feb 7     feb 7   3     onsite       task1    2          12    feb 8     feb 10  4     offshore       task1    2          12    feb 9     feb 10  5     null       task1    2          12    feb 10    feb 10  6     null       'engineertypes' data :       id   type       11   type1       12   type2       'users' data :       id     firstname        1      name1       2      name2 

the query implemented :

                                                                                              select taskallocation.userid userid,users.firstname name,                                                                     taskallocation.engineertype typeid,engineertypes.type type,                                                        min(taskallocation.startdate) allocationstartdate,                                                                        max(taskallocation.enddate) allocationenddate,                                                          sum(taskallocation.hours) hours, taskallocation.location                                                                             taskallocation join users on taskallocation.userid=users.id                                                                            join engineertypes on engineertypes.id = taskallocation.engineertype                                                                          taskallocation.taskname = task1' group                                     firstname,userid,engineertype,type,allocationstartdate,allocationenddate,hours,                                                                                                                                                                           location,taskname order taskname, userid  

output: userid name typeid type allocationstartdate allocationenddate hours location

    1. 126   name1  11   type1  2015-11-23      2015-11-25         0.1  null     2. 126   name1  11   type1  2015-11-24      2015-11-25         0.2  null     3. 126   name1  11   type1  2015-11-25      2015-11-25         0.3  null     4. 127   name2  12   type2  2015-11-23      2015-11-25         0.2  null     5. 127   name2  12   type2  2015-11-24      2015-11-25         0.3  null     6. 127   name2  12   type2  2015-11-25      2015-11-25         0.4  null 

you try below ways

method1:

select taskname, userid, min(startdate) as'first occurence', max(enddate) as'last occurence' ,sum(hours)  t1  group taskname,userid 

method2:cross apply

 select   distinct taskname,userid,b.*  t1  cross apply  (select min(startdate) firstoccur,max(startdate) secondocc,sum(hours) hrs  t1 t2 t1.taskname =t2.taskname , t1.userid=t2.userid  group t2.taskname,t2.userid) b 

method 3: window functions

 cte   (  select taskname,userid,  min(startdate) on (partition taskname,userid) 'first',  max(enddate) on (partition taskname,userid) 'second',  sum(hours) on (partition taskname,userid) 'hrs',  row_number() on (partition taskname,userid order taskname,userid) rn    t1    )    select *from cte rn=1