select in different columns based on condition

Question

this query is a bit more complicated, but for simplicity reasons I'll post it as simple as possible

I have 1 table that has following columns

table: "time_TimesheetRow" columns:

TimeSheet_ID,
d1,
d2,
d3,
d4,
d5,
d6,
d7,
rate

now, my select brings a lot of fields, among those I have the fields:

   time_TimesheetRow.d3 AS [Monday Normal Hours], 
   time_TimesheetRow.d4 AS [Tuesday Normal Hours], 
   time_TimesheetRow.d5 AS [Wednesday Normal Hours], 
   time_TimesheetRow.d6 AS [Thursday Normal Hours], 
   time_TimesheetRow.d7 AS [Friday Normal Hours], 
   time_TimesheetRow.d1 AS [Saturday Normal Hours], 
   time_TimesheetRow.d2 AS [Sunday Normal Hours], 
   time_TimesheetRow.d3 AS [Monday OT Hours], 
   time_TimesheetRow.d4 AS [Tuesday OT Hours], 
   time_TimesheetRow.d5 AS [Wednesday OT Hours], 
   time_TimesheetRow.d6 AS [Thursday OT Hours], 
   time_TimesheetRow.d7 AS [Friday OT Hours], 
   time_TimesheetRow.d1 AS [Saturday OT Hours], 
   time_TimesheetRow.d2 AS [Sunday OT Hours]

but, the thing is that all normal hours are the ones with rate = 0 and the OT columns are the ones with rate <> 0

how should I do to bring this?

I was trying adding the same table with different alias in the FROM clause and then using the ID field for matching them, but doesn't seems to work

this is the complete query, this will give a clear example.

SELECT ts1.TimeSheetRow_ID AS [Unique Line Id], 
       peperson.PERSONNEL_NO AS [Staff Id], 
       peperson.SURNAME + ', ' + peperson.FORENAME AS [Forename + Surname], 
       time_Timesheet.TSDate AS [Week ending date], 
       ts2.d1 + ts2.d2 + ts2.d3 + ts2.d4 + ts2.d5 + ts2.d6 + ts2.d7 AS [Normal Hours], 
       ts3.d1 + ts3.d2 + ts3.d3 + ts3.d4 + ts3.d5 + ts3.d6 + ts3.d7 AS [Overtime Hours], 
       ts2.d1 + ts2.d2 + ts2.d3 + ts2.d4 + ts2.d5 + ts2.d6 + ts2.d7 + ts3.d1 + ts3.d2 + ts3.d3 + ts3.d4 + ts3.d5 + ts3.d6 + ts3.d7 AS [Total Hours],
       CASE
           WHEN ts1.Rate = 11
           THEN 'Unpaid'
           ELSE 'Paid'
       END AS [Overtime Paid/Unpaid Indicator], 
       ts1.notes AS Narrative, 
       ts1.Job AS [Job Number], 
       ts2.d3 AS [Monday Normal Hours], 
       ts2.d4 AS [Tuesday Normal Hours], 
       ts2.d5 AS [Wednesday Normal Hours], 
       ts2.d6 AS [Thursday Normal Hours], 
       ts2.d7 AS [Friday Normal Hours], 
       ts2.d1 AS [Saturday Normal Hours], 
       ts2.d2 AS [Sunday Normal Hours], 
       ts3.d3 AS [Monday OT Hours], 
       ts3.d4 AS [Tuesday OT Hours], 
       ts3.d5 AS [Wednesday OT Hours], 
       ts3.d6 AS [Thursday OT Hours], 
       ts3.d7 AS [Friday OT Hours], 
       ts3.d1 AS [Saturday OT Hours], 
       ts3.d2 AS [Sunday OT Hours], 
       peperson.hourly_rate AS [Staff Overhead Rate], 
       peperson.hourly_rate AS [Cost Rate], 
       peperson.PU AS [Staff Cost Centre Code], 
       peperson.PU_DESCRIPTION AS [Staff Cost Centre Name]
FROM dbo.time_manager
     INNER JOIN dbo.peperson peperson_manager ON dbo.time_manager.manager_payroll = peperson_manager.Personnel_no
     RIGHT OUTER JOIN dbo.time_Timesheet
     INNER JOIN dbo.time_TimesheetRow ts1 ON dbo.time_Timesheet.TimeSheet_ID = ts1.TimeSheet_ID
     INNER JOIN dbo.time_TimesheetRow ts2 ON dbo.time_Timesheet.TimeSheet_ID = ts2.TimeSheet_ID
                                             AND ts2.rate IN(0)
     INNER JOIN dbo.time_TimesheetRow ts3 ON dbo.time_Timesheet.TimeSheet_ID = ts3.TimeSheet_ID
                                             AND ts3.rate NOT IN(0)
     INNER JOIN dbo.peperson ON dbo.time_Timesheet.Payroll = dbo.peperson.Personnel_no
     INNER JOIN dbo.time_Status ON dbo.time_Timesheet.StatusID = dbo.time_Status.statusID ON dbo.time_manager.payroll = dbo.time_Timesheet.Payroll
WHERE time_Timesheet.TSDate > '2019-11-01'
      AND ts1.Job LIKE 'B23639%';

right now it seems taht something is wrong with the query since is bringing too few rows, when it should be bringing more data, I take out the joins with the dbo.time_TimesheetRow ts2 and dbo.time_TimesheetRow ts3 tables and it brings a lot more, so I'm thinking here is where the error lays

Added with logic proposed by @LukStorms :

SELECT ts1.TimeSheetRow_ID AS [Unique Line Id], 
       peperson.PERSONNEL_NO AS [Staff Id], 
       peperson.SURNAME + ', ' + peperson.FORENAME AS [Forename + Surname], 
       time_Timesheet.TSDate AS [Week ending date], 
       ts1.d1 + ts1.d2 + ts1.d3 + ts1.d4 + ts1.d5 + ts1.d6 + ts1.d7 AS [Normal Hours], 
       ts2.d1 + ts2.d2 + ts2.d3 + ts2.d4 + ts2.d5 + ts2.d6 + ts2.d7 AS [Overtime Hours], 
       ts1.d1 + ts1.d2 + ts1.d3 + ts1.d4 + ts1.d5 + ts1.d6 + ts1.d7 + ts2.d1 + ts2.d2 + ts2.d3 + ts2.d4 + ts2.d5 + ts2.d6 + ts2.d7 AS [Total Hours],
       CASE
           WHEN ts1.Rate = 11
           THEN 'Unpaid'
           ELSE 'Paid'
       END AS [Overtime Paid/Unpaid Indicator], 
       ts1.notes AS Narrative, 
       ts1.Job AS [Job Number], 
       ts1.d3 AS [Monday Normal Hours], 
       ts1.d4 AS [Tuesday Normal Hours], 
       ts1.d5 AS [Wednesday Normal Hours], 
       ts1.d6 AS [Thursday Normal Hours], 
       ts1.d7 AS [Friday Normal Hours], 
       ts1.d1 AS [Saturday Normal Hours], 
       ts1.d2 AS [Sunday Normal Hours], 
       ts2.d3 AS [Monday OT Hours], 
       ts2.d4 AS [Tuesday OT Hours], 
       ts2.d5 AS [Wednesday OT Hours], 
       ts2.d6 AS [Thursday OT Hours], 
       ts2.d7 AS [Friday OT Hours], 
       ts2.d1 AS [Saturday OT Hours], 
       ts2.d2 AS [Sunday OT Hours], 
       peperson.hourly_rate AS [Staff Overhead Rate], 
       peperson.hourly_rate AS [Cost Rate], 
       peperson.PU AS [Staff Cost Centre Code], 
       peperson.PU_DESCRIPTION AS [Staff Cost Centre Name]
FROM dbo.time_manager
     INNER JOIN dbo.peperson peperson_manager ON dbo.time_manager.manager_payroll = peperson_manager.Personnel_no
     RIGHT OUTER JOIN dbo.time_Timesheet
     INNER JOIN dbo.time_TimesheetRow ts1 ON dbo.time_Timesheet.TimeSheet_ID = ts1.TimeSheet_ID
                                             AND ts1.Rate = 0
     LEFT JOIN dbo.time_TimesheetRow ts2 ON dbo.time_Timesheet.TimeSheet_ID = ts2.TimeSheet_ID
                                            AND ts2.rate > 0
     INNER JOIN dbo.peperson ON dbo.time_Timesheet.Payroll = dbo.peperson.Personnel_no
     INNER JOIN dbo.time_Status ON dbo.time_Timesheet.StatusID = dbo.time_Status.statusID ON dbo.time_manager.payroll = dbo.time_Timesheet.Payroll
WHERE time_Timesheet.TSDate > '2019-11-01'
      AND ts1.Job LIKE 'B23639%';

I still have the issue that the Overtime Hours, Total Hours, and all the OT Hours columns are showing NULL as value

Answer 1

So you want to self-join based on the id and the rate

SELECT 
t0.TimeSheet_ID,
t0.d3 AS [Monday Normal Hours], 
t0.d4 AS [Tuesday Normal Hours], 
t0.d5 AS [Wednesday Normal Hours], 
t0.d6 AS [Thursday Normal Hours], 
t0.d7 AS [Friday Normal Hours], 
t0.d1 AS [Saturday Normal Hours], 
t0.d2 AS [Sunday Normal Hours], 
SUM(tx.d3) AS [Monday OT Hours], 
SUM(tx.d4) AS [Tuesday OT Hours], 
SUM(tx.d5) AS [Wednesday OT Hours], 
SUM(tx.d6) AS [Thursday OT Hours], 
SUM(tx.d7) AS [Friday OT Hours], 
SUM(tx.d1) AS [Saturday OT Hours], 
SUM(tx.d2) AS [Sunday OT Hours]
FROM time_TimesheetRow t0
LEFT JOIN time_TimesheetRow tx
  ON tx.TimeSheet_ID = t0.TimeSheet_ID
 AND tx.rate > 0
WHERE t0.rate = 0
GROUP BY t0.TimeSheet_ID, t0.d1, t0.d2, t0.d3, t0.d4, t0.d5, t0.d6, t0.d7
ORDER BY t0.TimeSheet_ID

An untested notepad scribble that's based of a remodelled version of the unsimplified query:

SELECT 
   ts.TimeSheetRow_ID AS [Unique Line Id], 
   pep.PERSONNEL_NO AS [Staff Id], 
   CONCAT(pep.SURNAME, ', ' + pep.FORENAME) AS [Forename + Surname], 
   ts.TSDate AS [Week ending date], 
   t0tot.Total AS [Normal Hours], 
   ISNULL(txtot.Total,0) AS [Overtime Hours], 
   t0tot.Total + ISNULL(txtot.Total,0) AS [Total Hours],
   CASE WHEN ts.Rate = 11 THEN 'Unpaid' ELSE 'Paid' END AS [Overtime Paid/Unpaid Indicator],
   t0.notes AS [Narrative], 
   t0.Job AS [Job Number],
   t0.d3 AS [Monday Normal Hours], 
   t0.d4 AS [Tuesday Normal Hours], 
   t0.d5 AS [Wednesday Normal Hours], 
   t0.d6 AS [Thursday Normal Hours], 
   t0.d7 AS [Friday Normal Hours], 
   t0.d1 AS [Saturday Normal Hours], 
   t0.d2 AS [Sunday Normal Hours], 
   tx.d3 AS [Monday OT Hours], 
   tx.d4 AS [Tuesday OT Hours], 
   tx.d5 AS [Wednesday OT Hours], 
   tx.d6 AS [Thursday OT Hours], 
   tx.d7 AS [Friday OT Hours], 
   tx.d1 AS [Saturday OT Hours], 
   tx.d2 AS [Sunday OT Hours], 
   pep.hourly_rate AS [Staff Overhead Rate], 
   pep.hourly_rate AS [Cost Rate], 
   pep.PU AS [Staff Cost Centre Code], 
   pep.PU_DESCRIPTION AS [Staff Cost Centre Name]
FROM dbo.time_Timesheet AS ts
INNER JOIN dbo.time_manager AS tm_mngr
  ON tm_mngr.payroll = ts.Payroll
INNER JOIN dbo.peperson AS pep_mngr
  ON pep_mngr.Personnel_no = tm_mngr.manager_payroll
INNER JOIN dbo.peperson AS pep 
  ON pep.Personnel_no = ts.Payroll
INNER JOIN dbo.time_Status AS tm_stat
  ON tm_stat.statusID = ts.StatusID
INNER JOIN dbo.time_TimesheetRow AS t0
  ON t0.TimeSheet_ID = ts.TimeSheet_ID
 AND t0.Rate = 0
LEFT JOIN dbo.time_TimesheetRow tx
  ON tx.TimeSheet_ID = ts.TimeSheet_ID 
 AND (tx.rate > 0 OR tx.rate IS NULL)
OUTER APPLY (SELECT SUM(d) Total FROM (VALUES (t0.d1),(t0.d2),(t0.d3),(t0.d4),(t0.d5),(t0.d6),(t0.d7)) t(d)) AS t0tot
OUTER APPLY (SELECT SUM(d) Total FROM (VALUES (tx.d1),(tx.d2),(tx.d3),(tx.d4),(tx.d5),(tx.d6),(tx.d7)) t(d)) AS txtot
WHERE ts.TSDate > '2019-11-01'
  AND t0.Job LIKE 'B23639%';

Basically, more use of short alias names.
And not using a RIGHT JOIN, since that's often confusing.
And outer applies to sum the d's.

Answer 2

the lines in your select

INNER JOIN dbo.time_TimesheetRow ts2 ON dbo.time_Timesheet.TimeSheet_ID = ts2.TimeSheet_ID
                                         AND ts2.rate IN(0)
INNER JOIN dbo.time_TimesheetRow ts3 ON dbo.time_Timesheet.TimeSheet_ID = ts3.TimeSheet_ID
                                         AND ts3.rate NOT IN(0)

narrows your result set, as inner join requires all set items should be included on joined column from both sides and you limiting set with

rate (NOT) IN(0)

this means only the results having such values will be added to set.

I would suggest using left or right join as not all your rows would have rate "in (0)" or "in not (0)" for all data.

And I am not so sure you know what you doing because adding right outer join in the middle of the query seems unusual as later you adding left join.

For your problem with Overtime Hours, Total Hours, and all the OT Hours use ISNULL(value,0) before adding it, you can check it with

select 1+null+1

sum of NULL and digit is always NULL