How to improve query to include 2 critical pieces of employee data

Question

I’m going to preface by saying that I’m fairly green with SQL.

My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but had no corresponding payroll deduction, and (2) those employees who experienced payroll deductions but were not invoiced. The code, as it is now, only performs the arithmetic on employees who have corresponding data in both the Employee_Deductions and Employee_Invoices tables. I've been reading up on CASE, but am not certain if that's the path I should go down. I greatly appreciate any constructive critiques and suggestions on how to arrive at my desired result. Thank you.

SELECT
    PayrollpersonID, 
    PayrollDeductionYear, 
    January, 
    February, 
    March, 
    April, 
    May, 
    June, 
    July, 
    August, 
    September, 
    October, 
    November, 
    December, 
    Total 
FROM
    (SELECT
        PayrollpersonID,
        PayrollDeductionAmount-i.invoice as Variance, 
        PayrollDeductionMonth, 
        PayrollDeductionYear, 
        Total = sum(PayrollDeductionAmount -i.invoice) over (partition by PayrollpersonID)
    FROM
        Employee_Deductions as d, 
        (SELECT
        InvoicepersonID, 
        sum(isnull(v.InvoiceAmount,0.00)) as invoice, 
        InvoiceProcessedMonth, 
        InvoiceProcessedYear 
        FROM 
            Employee_Invoices as v
        WHERE InvoiceProcessedYear = '2015' 
        GROUP BY InvoicepersonID, InvoiceProcessedMonth, InvoiceProcessedYear) as i
    WHERE
        d.PayrollpersonID = i.InvoicepersonID and 
        d.PayrollDeductionMonth = i.InvoiceProcessedMonth and 
        d.PayrollDeductionYear = i.InvoiceProcessedYear and 
        d.PayrollDeductionYear = '2015' and 
        d.PayrollDeductionAmount > 0)
SOURCE PIVOT
(
SUM(Variance) FOR PayrollDeductionMonth in (January, February, March, April, May, June, 
                                            July, August, September, October, November, December)
)
AS pivottable
ORDER BY PayrollpersonID;

Table Schema:

The [Employee_Deductions] table has the following columns:

PayrollDeductionYear nvarchar, 
PayrollDeductionMonth nvarchar, 
PayrollpersonID nvarchar, 
LastName nvarchar, 
FirstName nvarchar, 
PayrollDeductionAmount money

The [Employee_Invoices] table has the following columns:

InvoiceProcessedYear nvarchar, 
InvoiceProcessedMonth nvarchar, 
InvoicepersonID nvarchar, 
EmployeeLastName nvarchar, 
EmployeeFirstName nvarchar, 
EmployeeEmail nvarchar, 
OrganizationID nvarchar, 
OrgName nvarchar, 
OrgAddress1 nvarchar, 
OrgAddress2 nvarchar, 
OrgCity nvarchar, 
OrgState nvarchar, 
OrgZip nvarchar, 
OrgEIN nvarchar, 
InvoiceAmount money

Result Table:

Answer 1

Without the schema this is a little difficult to test but I think this will work. Even if I do make some syntax errors you will be able to see what I am getting at.

SELECT
    PayrollpersonID, 
    PayrollDeductionYear, 
    SUM(CASE WHEN PayrollDeductionMonth = 'January' THEN Variance ELSE 0 END) AS January, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'January' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'January No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'January' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'January No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'February' THEN Variance ELSE 0 END) AS February, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'February' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'February No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'February' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'February No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'March' THEN Variance ELSE 0 END) AS March,
    MAX(CASE WHEN (PayrollDeductionMonth = 'March' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'March No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'March' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'March No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'April' THEN Variance ELSE 0 END) AS April,
    MAX(CASE WHEN (PayrollDeductionMonth = 'April' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'April No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'April' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'April No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'May' THEN Variance ELSE 0 END) AS May, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'May' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'May No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'May' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'May No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'June' THEN Variance ELSE 0 END) AS June, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'June' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'June No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'June' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'June No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'July' THEN Variance ELSE 0 END) AS July, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'July' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'July No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'July' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'July No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'August' THEN Variance ELSE 0 END) AS August, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'August' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'August No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'August' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'August No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'September' THEN Variance ELSE 0 END) AS September, 
    MAX(CASE WHEN (PayrollDeductionMonth = 'September' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'September No Deduction', 
    MAX(CASE WHEN (PayrollDeductionMonth = 'September' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'September No Invoice', 
    SUM(CASE WHEN PayrollDeductionMonth = 'October' THEN Variance ELSE 0 END) AS October,
    MAX(CASE WHEN (PayrollDeductionMonth = 'October' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'October No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'October' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'October No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'November' THEN Variance ELSE 0 END) AS November,
    MAX(CASE WHEN (PayrollDeductionMonth = 'November' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'November No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'November' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'November No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'December' THEN Variance ELSE 0 END) AS December,
    MAX(CASE WHEN (PayrollDeductionMonth = 'December' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'December No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'December' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'December No Invoice', 
    Total 
FROM
    (SELECT
        PayrollpersonID,
        PayrollDeductionAmount,
        i.invoice,
        PayrollDeductionAmount-i.invoice as Variance, 
        PayrollDeductionMonth, 
        PayrollDeductionYear, 
        Total = sum(PayrollDeductionAmount -i.invoice) over (partition by PayrollpersonID)
    FROM
        Employee_Deductions as d, 
        (SELECT
        InvoicepersonID, 
        sum(isnull(v.InvoiceAmount,0.00)) as invoice, 
        InvoiceProcessedMonth, 
        InvoiceProcessedYear 
        FROM 
            Employee_Invoices as v
        WHERE DeductionYear = '2015' 
        GROUP BY InvoicepersonID, InvoiceProcessedMonth, InvoiceProcessedYear) as i
    WHERE
        d.PayrollpersonID = i.InvoicepersonID and 
        d.PayrollDeductionMonth = i.InvoiceProcessedMonth and 
        d.PayrollDeductionYear = i.InvoiceProcessedYear and 
        d.PayrollDeductionYear = '2015' and 
        d.PayrollDeductionAmount > 0) AS a
GROUP BY PayrollpersonID, 
    PayrollDeductionYear,
    Total
ORDER BY PayrollpersonID;

You could have done this by stacking pivot statements but this can be messy and inefficient. This is a different way of pivoting data called the cross tab method. This method is usually more efficient than the pivot statement and easier to read.