Show distinct totals based on Issue & Date

Question

I have the following results with my query:

Sample Data of the above:

declare @tbl table (DateOfService date, IssueID int, IssueName varchar(100), AssignedConsultant varchar(30), Activity varchar(100), TimeSpent float, TimeInvoiced float)

insert into @tbl (DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values ('2019-12-19',10295,'Bin Issue','Doug','Activity Detail 1…',10.25,8)
insert into @tbl (DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values ('2019-12-19',10295,'Bin Issue','Doug','Activity Detail 2…',10.25,8)
insert into @tbl (DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values ('2020-01-24',10295,'Bin Issue','Doug','Activity Detail 3…',5.5,4)
insert into @tbl (DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values ('2020-01-24',10295,'Bin Issue','Doug','Activity Detail 4…',5.5,4)

select
*
from    @tbl

For reasons of confidentiality, I'm unable to share my query, however,

How would one alter these results to look like this :

As can be seen from my expected results, I only need to the total time spent and invoiced per date.

The problem I have is, this result gets used in a pivot in Excel, causing double values to reflect.

Please assist?

Answer 1

I assume you have some way of identifying each row, so I added and ID column to the table variable. I also left the values for the secondary rows as NULL as you were not clear about what value you wanted there. Anyways, this should work for the represented data set:

declare @tbl table (ID int,DateOfService date, IssueID int, IssueName varchar(100), AssignedConsultant varchar(30), Activity varchar(100), TimeSpent float, TimeInvoiced float)

insert into @tbl (ID,DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values (1,'2019-12-19',10295,'Bin Issue','Doug','Activity Detail 1…',10.25,8)
insert into @tbl (ID,DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values (2,'2019-12-19',10295,'Bin Issue','Doug','Activity Detail 2…',10.25,8)
insert into @tbl (ID,DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values (3,'2020-01-24',10295,'Bin Issue','Doug','Activity Detail 3…',5.5,4)
insert into @tbl (ID,DateOfService,IssueID,IssueName,AssignedConsultant,Activity,TimeSpent,TimeInvoiced) values (4,'2020-01-24',10295,'Bin Issue','Doug','Activity Detail 4…',5.5,4)


;WITH CTE AS (
SELECT MIN(ID) ID, DateOfService
FROM @tbl
GROUP BY DateOfService
)
SELECT T1.DateOfService,
        IssueID,
        IssueName,
        AssignedConsultant,
        Activity, 
        CASE WHEN T2.ID IS NULL THEN NULL ELSE T1.TimeSpent END TimeSpent,
        CASE WHEN T2.ID IS NULL THEN NULL ELSE T1.TimeInvoiced END TimeInvoiced
FROM @tbl T1
LEFT JOIN CTE T2 on T1.ID = T2.ID