What is the best way to divide a numeric column into two different columns based on a binary column?-CodePudding

I have a table in SQL Server that keeps track of hours and if they are billable or not.

SELECT 
    Billable,
    HoursBooked
FROM
    Time

Result:

Billable HoursBooked
--------------------
   0         8.0
   1         4.0
   1         6.0

I want to divide the hours into new columns based on whether it is billable or not. The new columns should be BillableHours and NonBillableHours.

Billable HoursBooked BillableHours NonBillableHours
----------------------------------------------------
   0         8.0          0.0            8.0
   1         4.0          4.0            0.0
   1         6.0          6.0            0.0

I figured out how to get billable hours by multiplying the two columns together, but this doesn't work where billable = 0.

SELECT 
    Billable,
    HoursBooked
    Billable * HoursBooked AS BillableHours
FROM
    Time

Output:

Billable HoursBooked BillableHours
----------------------------------
   0         8.0          0.0
   1         4.0          4.0 
   1         6.0          6.0

What would be the best way to create the non-billable hours column?

CodePudding user response：

You can do this:

SELECT Billable,
       HoursBooked
       Billable*HoursBooked AS BillableHours
       HoursBooked*(1-Billable) AS NonBillableHours
From Time

I would suggest you to use CASE statement here too, if it makes more sense:

SELECT Billable,
       Hoursbooked,
       CASE
         WHEN Billable = 1 THEN Hoursbooked
         ELSE 0
       END AS BillableHours,
       CASE
         WHEN Billable = 0 THEN Hoursbooked
         ELSE 0
       END AS NonBillableHours,
FROM Time;