4

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
2
  • 2
    Did you try OUTER APPLY? Feb 20, 2017 at 22:33
  • @AaronBertrand Yes I did. It has no affect on the output
    – Jeff.Clark
    Feb 20, 2017 at 22:39

1 Answer 1

2

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

4
  • To expand on what Joe alluded to, if at any point you're looking to use COUNT, you might want to use NULL instead of zero for your default rows. If you then use COUNT(SIT), the NULL values will be ignored in the count. If you won't be using COUNT, then the zero values should be dandy.
    – RDFozz
    Feb 21, 2017 at 14:59
  • Thank you for this! It forces those records to appear quite well. Let me know what was unclear, and I'll edit the question to make it more clear for others down the road.
    – Jeff.Clark
    Feb 22, 2017 at 20:44
  • @Jeff.Clark What I wasn't sure about was which state column to use. If what you wanted was to see all of those metrics for each unique state in the STATE column that would have cleared it up.
    – Joe Obbish
    Feb 23, 2017 at 1:41
  • Ah, makes sense. Unfortunately with this data, there is no real way to tell what column would be the perfect unless one is an expert both in tax law/codes as well as this specific database software. The state column seems to have worked as desired for my data.
    – Jeff.Clark
    Feb 23, 2017 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.