NULL Calculation

Question

Sorry, not sure what is the proper title for this issue. Need help & advice on this issue:

I have 2 tables:

--table_sum   
SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction as Amount 
FROM table_sum WHERE 
year = 2016 AND month = 4 AND  
(Basic + Allowance + Overtime)- Deduction > 0 
GROUP BY Outlet, Department

Emp no  Outlet  Department   Basic     Allowance    Overtime    Deduction
 101     OLET1    DET1     $2,000.00    $250.00      $30.00      $10.00 
 102     OLET2    DET2     $1,800.00    $100.00      $50.00      $10.00 
 103     OLET1    DET1     $2,500.00    $250.00      $20.00      $-   
 104     OLET2    DET1     $3,500.00    $100.00      $-           $-   


--table_details
SELECT SUM (Amount) 
FROM Table_details 
WHERE year = 2016 AND month = 4 
AND Code  = 'OTA'

Emp No  Outlet  Department  Code    Code Description      Amount
 101    OLET1   DET1        BSC     Basic                $2,000.00 
 101    OLET1   DET1        CRA     Car Allowance        $100.00 
 101    OLET1   DET1        OTA     Other Allowance      $150.00 
 101    OLET1   DET1        OTP     Normal Day Overtime  $30.00 
 101    OLET1   DET1        UFD     Uniform Deduction    $10.00 
 102    OLET2   DET2        BSC     Basic                $1,800.00 
 102    OLET2   DET2        CRA     Car Allowance        $100.00 
 102    OLET2   DET2        OTP     Normal Day Overtime  $50.00 
 102    OLET2   DET2        UFD     Uniform Deduction    $10.00 
 103    OLET1   DET1        BSC     Basic                $2,500.00 
 103    OLET1   DET1        CRA     Car Allowance        $100.00 
 103    OLET1   DET1        OTA     Other Allowance      $150.00 
 103    OLET1   DET1        OTP     Normal Day Overtime  $20.00 
 104    OLET2   DET1        BSC     Basic                $3,500.00 
 104    OLET2   DET1        CRA     Car Allowance        $100.00 

As you can see that in table_sum, all the allowance is already sum up together in "Allowance" column. My plan is not include some of the allowance in the calculation by deducting the allowance - This is where table_details comes in:

SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction - 
   (SELECT SUM (z.Amount) FROM Table_details z 
    WHERE z.Outlet=Table_sum.Outlet AND z.Outlet=Table_sum 
    AND z.Department=Table_sum.Department 
    AND z.year = 2016 AND z.month = 4 
    AND z.Code  = 'OTA') as  Amount --deduct OTA Allowance Amount
FROM table_sum 
WHERE year = 2016 AND month = 4 
AND  (Basic + Allowance + Overtime)- Deduction > 0 
GROUP BY Outlet, Department

Result:

  Outlet               Headcount         Amount
OLET1-DET1                 2             $4,740
OLET2-DET1                 1              NULL
OLET2-DET2                 1              NULL

I realized that emp no in "OLET2-DET1" & "OLET2-DET2" does not have the 'OTA' Allowance. So, result become NULL. Any advice on this?

Thanks!

Solved:

SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction - 
   ISNULL((SELECT SUM (z.Amount) FROM Table_details z 
    WHERE z.Outlet=Table_sum.Outlet AND z.Outlet=Table_sum 
    AND z.Department=Table_sum.Department 
    AND z.year = 2016 AND z.month = 4 
    AND z.Code  = 'OTA'),0) as  Amount --deduct OTA Allowance Amount
FROM table_sum 
WHERE year = 2016 AND month = 4 
AND  (Basic + Allowance + Overtime)- Deduction > 0 
GROUP BY Outlet, Department

Answer 1

You must always use join query when your common fields between tables, which is faster.

Here is 2 approach, also read the instruction in comment.

--Approach 1 : simple join
SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction - 
ISNULL ( SUM (z.Amount)   as  Amount --deduct OTA Allowance Amount

FROM table_sum ts  --give alias name to identiy easily
--add left outer, so if null value found, then main table data is not mismatch
Left outer JOIN Table_details z on z.Outlet=Table_sum.Outlet and z.year = ts.year and z.month = ts.month and z.code = 'OTA' 
WHERE ts.year = 2016 AND month = 4 
AND  (ts.Basic + ts.Allowance + ts.Overtime)- ts.Deduction > 0 
GROUP BY ts.Outlet, ts.Department


--Approach 2 : Use CTE and join
;with cte
as
(
    SELECT z.Outlet, SUM (z.Amount) 
    FROM Table_details z 
    WHERE z.year = 2016 AND z.month = 4 
        AND z.Code  = 'OTA'
    Group By z.Outlet
)

SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction - 
ISNULL ( SUM (z.Amount)   as  Amount --deduct OTA Allowance Amount

FROM table_sum ts  --give alias name to identiy easily
Left outer JOIN cte z on z.Outlet=Table_sum.Outlet --add left outer, so if null value found, then main table data is not mismatch
WHERE ts.year = 2016 AND month = 4 
AND  (ts.Basic + ts.Allowance + ts.Overtime)- ts.Deduction > 0 
GROUP BY ts.Outlet, ts.Department

Answer 2

I respect the answer given by Kyle Hale but ISNULL should be at field level and not query level, as follows

SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction - 
   (SELECT SUM (ISNULL (z.Amount, 0)) FROM Table_details z 
    WHERE z.Outlet=Table_sum.Outlet AND z.Outlet=Table_sum 
    AND z.Department=Table_sum.Department 
    AND z.year = 2016 AND z.month = 4 
    AND z.Code  = 'OTA') as  Amount --deduct OTA Allowance Amount
FROM table_sum 
WHERE year = 2016 AND month = 4 
AND  (Basic + Allowance + Overtime)- Deduction > 0 
GROUP BY Outlet, Department

Answer 3

Just wrap your subquery in an ISNULL statement:

SELECT Outlet + '-' + Department as Outlet, 
COUNT (*) as Headcount, 
SUM (Basic + Allowance + Overtime) - Deduction - 
   ISNULL ( (SELECT SUM (z.Amount) FROM Table_details z 
    WHERE z.Outlet=Table_sum.Outlet AND z.Outlet=Table_sum 
    AND z.Department=Table_sum.Department 
    AND z.year = 2016 AND z.month = 4 
    AND z.Code  = 'OTA'), 0 ) as  Amount --deduct OTA Allowance Amount
FROM table_sum 
WHERE year = 2016 AND month = 4 
AND  (Basic + Allowance + Overtime)- Deduction > 0 
GROUP BY Outlet, Department