Sql-server – While using Cross Apply, can I force “empty” records to appear

cross-applypivotsql serverssrst-sql

I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS 2016. If the desired behavior can be done in SSRS, or even changing to a PIVOT or some such, I am open to that option.

I will put some DDL down below to mimic the example images shown.

The data I am pulling from looks like the below:

enter image description here

And I am using a cross apply to make the totals appear vertically like so:

enter image description here

The problem is, as seen above, the records that have a state and SUI_State of 'IN', have a SUI_State of "OH". This leads to the output to not contain SUI records for "IN" because no records have an SUI_State1 of "IN"

Is there any way to force "IN Employee SUI", "IN Employer SUI", etc… to appear with values of 0?

There are many states where the State/SDI State and SUI State are different, so I cannot hard-code the logic in there.

And here is some dumbed down similar data with my current query. The values aren't important in this question, only forcing the SUI records to appear for the state, "IN'

    CREATE TABLE #EmployeeTaxes
(    Payroll int
    ,SDI_State char(2)
    ,SUI_State char(2)
    ,State char(2)
    ,SIT DECIMAl(19,2)
    ,Employee_SDI DECIMAL(19,2)
    ,Employer_SDI DECIMAL(19,2)
    ,Employee_SUI DECIMAL(19,2)
    ,Employer_SUI DECIMAL(19,2)
)

--Data in source table
INSERT INTO #EmployeeTaxes
VALUES   (4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)


--My Current Query
SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax  
      ,v.OrderByNumber              AS [OrderByNumber]
FROM #EmployeeTaxes
CROSS APPLY (
    VALUES ([STATE]   + ' SIT', SIT, [STATE] + '110')
          ,(SDI_STATE + ' Employee SDI', EMPLOYEE_SDI,SDI_STATE + '111')
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI,SDI_STATE + '112')
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI,SUI_STATE + '113')
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI,SUI_STATE + '114A')
          ,(SUI_STATE + ' Total', EMPLOYEE_SUI + EMPLOYER_SUI, SUI_State + '114B')
          ,(SDI_STATE + ' Total', EMPLOYEE_SDI + EMPLOYER_SDI , SDI_State + '114B')
          ,('---------------------------------',NULL,[STATE] + '121')

) v (Item, TaxValue, OrderByNumber)
GROUP BY Payroll, OrderByNumber, v.Item
ORDER BY PAYROLL, OrderByNumber 
DROP TABLE #EmployeeTaxes

Best Answer

It's a little unclear what you're after, but one way to add in missing values into a GROUP BY query is to add all rows to your starting table with 0 or NULL for the aggregated columns. As @RDFozz pointed out, you want to use NULL if you are doing COUNT aggregates because otherwise the results will be inflated. However, 0 is a good choice if you only do SUM aggregates and don't want to bother with a COALESCE.

You can accomplish this with UNION ALL. One implementation is as follows:

SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax  
      ,v.OrderByNumber              AS [OrderByNumber]
FROM 
(
    SELECT * 
    FROM #EmployeeTaxes

    UNION ALL

    SELECT DISTINCT Payroll, [STATE], [STATE], [STATE], 0, 0, 0, 0, 0 
    FROM #EmployeeTaxes
) AS #EmployeeTaxes
CROSS APPLY (
    VALUES ([STATE]   + ' SIT', SIT, [STATE] + '110')
          ,(SDI_STATE + ' Employee SDI', EMPLOYEE_SDI,SDI_STATE + '111')
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI,SDI_STATE + '112')
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI,SUI_STATE + '113')
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI,SUI_STATE + '114A')
          ,(SUI_STATE + ' Total', EMPLOYEE_SUI + EMPLOYER_SUI, SUI_State + '114B')
          ,(SDI_STATE + ' Total', EMPLOYEE_SDI + EMPLOYER_SDI , SDI_State + '114B')
          ,('---------------------------------',NULL,[STATE] + '121')

) v (Item, TaxValue, OrderByNumber)
GROUP BY Payroll, OrderByNumber, v.Item
ORDER BY PAYROLL, OrderByNumber;

I think that this is closer to what you want? Top result set is from your query in the question and the bottom result set is mine:

result sets

Related Question