Home Ask Login Register

Developers Planet

Your answer is one click away!

tennisno1 February 2016

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

There are two tables :

Tasks table :

    TaskName (PK)

TaskAllocation table :

     AllocationID(PK),
     TaskName(F.K to TaskName in 'Tasks' Table),
     UserID( F.K to ID in 'Users' Table),
     EngineerType( F.K to 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, the taskname can occur multiple times in the Task Allocation table. The same task can be mapped to multiple users (UserID)

I need to display the selected task (given as input from U.I), the UserIDs allocated to that task, first occurrence of start date, first occurrence of end date and Sum(Hours) for each user of the 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 which I implemented was :

                                                                                              Select TaskAllocation.UserId as UserId,Users.FirstName as Name,
                                                                    TaskAllocation.EngineerType as TypeId,EngineerTypes.Type as Ty        

Answers


TheGameiswar February 2016

You could try below ways

Method1:

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

Method2:Cross apply

 select 
 distinct taskname,userid,b.*
 from t1
 cross apply
 (select min(startdate) as Firstoccur,max(startdate) as secondocc,sum(hours) as hrs
 from t1 t2 where t1.taskname =t2.taskname and t1.userid=t2.userid
 group by t2.taskname,t2.userid) b

Method 3: Window functions

 with cte
 as
 (
 select taskname,userid,
 min(startdate) over (partition by taskname,userid) as 'first',
 max(enddate) over (partition by taskname,userid) as 'second',
 sum(hours) over (partition by taskname,userid) as 'hrs',
 ROW_NUMBER() over (partition by taskname,userid order by taskname,userid) as rn
   from t1
   )
   select *from cte where rn=1

Post Status

Asked in February 2016
Viewed 3,027 times
Voted 6
Answered 1 times

Search




Leave an answer


Quote of the day: live life