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:
And I am using a cross apply to make the totals appear vertically like so:
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