Help Converting query -- Do I need a cursor?
-
I have an SSRS report that I wrote and need to convert it to another reporting tool. Unfortunately, the new reporting tool isn't as advanced as SSRS, however, it has some other advantages that require my usage of it.
The problem:
I'm creating an Employee Pay Detail report for an employer to look at. It basically looks like a pay stub, but with multiple employees per page. To simplify the data structure, I have the following tables:
Employee
Earnings
Benefits
Deductions
Taxes
(There are table create statements as well as sample data included below in the code window).
For SSRS, the Solution was to create a temp table as such:
Temp
ID(identity just in case)
EmployeeID
Description
Name
Amount
Section (=1 EmployeeInfo; =2 BenefitInfo; =3 DeductionInfo; =4 TaxInfo)
Then I had a table and within each column, I had another table with a filter set to only show a particular section. Simple to do an execute. This new reporting solution has no such concept. So my thought was to build a new temporary table that would just print all the data out old COBOL style. Just display what shows on each line. The problem is that each table has a variable number of values(4 benefits, 8 deductions, 5 taxes for example).
My report needs to look as such(in case the above pay stub concept is difficult to imagine):
So, if I could somehow build this data output from the table structure listed above so that my output record looks as it does in the #DesiredOutput table in the code window.
Any way, I was considering a nested cursor or even a call out to a C# subroutine(to build using arrays or whatever) but was hoping for a cleaner way to do it. Even the nested cursor solution seems a bit daunting at first glance.
Here is a copy of my code to build the request:
--Build Employee Table.
CREATE TABLE #Employees(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EmployeeName] [nvarchar](50) NULL,
[EmployeeSSN] [nvarchar](50) NULL)
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0001', 'Graham, Jarvis', '***-**-4830')
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0002', 'Jones, Bradley', '***-**-9707')
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0003', 'Craig, David', '***-**-0918')
select * from #Employees
--Build Earnings Table.
CREATE TABLE #Earnings(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[PayCode] [nvarchar](30) NULL,
[Units] [Decimal](19,4) NULL,
[Rate] [Decimal](19,4) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0001', 'HoursBI', 80.00, 10.00, 800.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0001', 'OTHRBI', 6.00, 15.00, 90.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0002', 'HoursBI', 40.00, 10.00, 400.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0003', 'HoursBI', 75.00, 10.00, 750.00)
select * from #Earnings
--Build Taxes Table.
CREATE TABLE #Taxes(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[TaxCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'Carrol', 8.74)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'FICA SS', 37.16)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'FICA Med', 8.69)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'Federal', 16.43)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'MDC', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'FICA SS', 6.94)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'Federal', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'FICA Med', 1.63)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'MDC', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'FICA Med', 6.72)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'FICA SS', 28.75)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'Federal', 0.00)
select * from #Taxes
--Build Deductions Table.
CREATE TABLE #Deductions(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[DeductCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'AMHLFM', 75.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'DNTLFM', 15.63)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'EEFAM', 200.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'AMHLEE', 22.50)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'Child', 50.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'DNTLFM', 15.63)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'EEFAM', 250.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'AMHLES', 31.25)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'DNTLES', 5.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'EEFAM', 250.00)
select * from #Deductions
--Build Benefits Table.
CREATE TABLE #Benefits(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[BenefitCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'AMHLFM', 225.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'DNTLFM', 46.88)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'EEFAM', 125.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'AMHLEE', 67.50)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'DNTLFM', 46.88)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'EEFAM', 125.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'AMHLES', 93.75)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'DNTLES', 15.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'EEFAM', 125.00)
select * from #Benefits
--This is what I'm trying to build. This is how the output should look.
CREATE TABLE #DesiredOutput(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EarningPayCode] [nvarchar](30) NULL,
[EarningUnits] [Decimal](19,4) NULL,
[EarningRate] [Decimal](19,4) NULL,
[EarningAmount] [Decimal](19,4) NULL,
[TaxCode] [nvarchar](30) NULL,
[TaxAmount] [Decimal](19,4) NULL,
[DeductionCode] [nvarchar](30) NULL,
[DeductionAmount] [Decimal](19,4) NULL,
[BenefitCode] [nvarchar](30) NULL,
[BenefitAmount] [Decimal](19,4) NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', 'HoursBI', 80.00, 10.00, 800.00, 'Carrol', 8.74, 'AMHLFM', 75.00, 'AMHLFM', 225.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', 'OTHRBI', 6.00, 15.00, 90.00, 'FICA SS', 37.16, 'DNTLFM', 15.63, 'DNTLFM', 46.88)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', NULL, NULL, NULL, NULL, 'FICA Med', 8.69, 'EEFAM', 200.00, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', NULL, NULL, NULL, NULL, 'Federal', 16.43, NULL, NULL, NULL, NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', 'HoursBI', 40.00, 10.00, 400.00, 'MDC', 0.00, 'AMHLEE', 22.50, 'AMHLEE', 67.50)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'FICA SS', 6.94, 'Child', 50.00, 'DNTLFM', 46.88)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'Federal', 0.00, 'DNTLFM', 15.63, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'EEFAM', 1.63, 'EEFAM', 250.00, NULL, NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', 'HoursBI', 75.00, 10.00, 750.00, 'MDC', 0.00, 'AMHLES', 31.25, 'AMHLES', 93.75)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'FICA SS', 28.75, 'DNTLES', 5.00, 'DNTLES', 15.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'FICA Med', 6.72, 'EEFAM', 250.00, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'Federal', 0.00, NULL, NULL, NULL, NULL)
select * from #DesiredOutput
/*
drop table #Employees
drop table #Earnings
drop table #Benefits
drop table #Deductions
drop table #Taxes
drop table #DesiredOutput
*/
As you can see, any time there is "missing data" because of the differing number of records in each table, I need NULLs or empty strings to fill in the gaps.
Any ideas?
Thanks,
Brian
Edit: Added code window as requested and removed some extraneous information that wasn't particularly necessary for the understanding of the problem.
-
You've left a lot of work for us to do to help you out. Please read the link in my signature, and then help us help you by providing:
1. CREATE TABLE statements for all of the affected tables.
2. INSERT statements to put test data into those tables.
3. And what you want the final results to look like, based upon the sample data.
I doubt that you'll find anyone here that would advocate a cursor for this. But... help us help you. Get the above information.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Here it is:
--Build Employee Table.
CREATE TABLE #Employees(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EmployeeName] [nvarchar](50) NULL,
[EmployeeSSN] [nvarchar](50) NULL)
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0001', 'Graham, Jarvis', '***-**-4830')
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0002', 'Jones, Bradley', '***-**-9707')
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0003', 'Craig, David', '***-**-0918')
select * from #Employees
--Build Earnings Table.
CREATE TABLE #Earnings(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[PayCode] [nvarchar](30) NULL,
[Units] [Decimal](19,4) NULL,
[Rate] [Decimal](19,4) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0001', 'HoursBI', 80.00, 10.00, 800.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0001', 'OTHRBI', 6.00, 15.00, 90.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0002', 'HoursBI', 40.00, 10.00, 400.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0003', 'HoursBI', 75.00, 10.00, 750.00)
select * from #Earnings
--Build Taxes Table.
CREATE TABLE #Taxes(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[TaxCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'Carrol', 8.74)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'FICA SS', 37.16)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'FICA Med', 8.69)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'Federal', 16.43)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'MDC', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'FICA SS', 6.94)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'Federal', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'FICA Med', 1.63)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'MDC', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'FICA Med', 6.72)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'FICA SS', 28.75)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'Federal', 0.00)
select * from #Taxes
--Build Deductions Table.
CREATE TABLE #Deductions(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[DeductCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'AMHLFM', 75.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'DNTLFM', 15.63)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'EEFAM', 200.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'AMHLEE', 22.50)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'Child', 50.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'DNTLFM', 15.63)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'EEFAM', 250.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'AMHLES', 31.25)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'DNTLES', 5.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'EEFAM', 250.00)
select * from #Deductions
--Build Benefits Table.
CREATE TABLE #Benefits(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[BenefitCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'AMHLFM', 225.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'DNTLFM', 46.88)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'EEFAM', 125.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'AMHLEE', 67.50)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'DNTLFM', 46.88)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'EEFAM', 125.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'AMHLES', 93.75)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'DNTLES', 15.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'EEFAM', 125.00)
select * from #Benefits
--This is what I'm trying to build. This is how the output should look.
CREATE TABLE #DesiredOutput(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EarningPayCode] [nvarchar](30) NULL,
[EarningUnits] [Decimal](19,4) NULL,
[EarningRate] [Decimal](19,4) NULL,
[EarningAmount] [Decimal](19,4) NULL,
[TaxCode] [nvarchar](30) NULL,
[TaxAmount] [Decimal](19,4) NULL,
[DeductionCode] [nvarchar](30) NULL,
[DeductionAmount] [Decimal](19,4) NULL,
[BenefitCode] [nvarchar](30) NULL,
[BenefitAmount] [Decimal](19,4) NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', 'HoursBI', 80.00, 10.00, 800.00, 'Carrol', 8.74, 'AMHLFM', 75.00, 'AMHLFM', 225.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', 'OTHRBI', 6.00, 15.00, 90.00, 'FICA SS', 37.16, 'DNTLFM', 15.63, 'DNTLFM', 46.88)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', NULL, NULL, NULL, NULL, 'FICA Med', 8.69, 'EEFAM', 200.00, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', NULL, NULL, NULL, NULL, 'Federal', 16.43, NULL, NULL, NULL, NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', 'HoursBI', 40.00, 10.00, 400.00, 'MDC', 0.00, 'AMHLEE', 22.50, 'AMHLEE', 67.50)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'FICA SS', 6.94, 'Child', 50.00, 'DNTLFM', 46.88)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'Federal', 0.00, 'DNTLFM', 15.63, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'EEFAM', 1.63, 'EEFAM', 250.00, NULL, NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', 'HoursBI', 75.00, 10.00, 750.00, 'MDC', 0.00, 'AMHLES', 31.25, 'AMHLES', 93.75)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'FICA SS', 28.75, 'DNTLES', 5.00, 'DNTLES', 15.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'FICA Med', 6.72, 'EEFAM', 250.00, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'Federal', 0.00, NULL, NULL, NULL, NULL)
select * from #DesiredOutput
/*
drop table #Employees
drop table #Earnings
drop table #Benefits
drop table #Deductions
drop table #Taxes
drop table #DesiredOutput
*/
Thanks,
Brian
-
--Build Employee Table.
CREATE TABLE #Employees(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EmployeeName] [nvarchar](50) NULL,
[EmployeeSSN] [nvarchar](50) NULL)
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0001', 'Graham, Jarvis', '***-**-4830')
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0002', 'Jones, Bradley', '***-**-9707')
INSERT INTO #Employees(EmployeeID, EmployeeName, EmployeeSSN) values ('01-003-0003', 'Craig, David', '***-**-0918')
select * from #Employees
--Build Earnings Table.
CREATE TABLE #Earnings(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[PayCode] [nvarchar](30) NULL,
[Units] [Decimal](19,4) NULL,
[Rate] [Decimal](19,4) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0001', 'HoursBI', 80.00, 10.00, 800.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0001', 'OTHRBI', 6.00, 15.00, 90.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0002', 'HoursBI', 40.00, 10.00, 400.00)
INSERT INTO #Earnings (EmployeeID, PayCode, Units, Rate, Amount) Values ('01-003-0003', 'HoursBI', 75.00, 10.00, 750.00)
select * from #Earnings
--Build Taxes Table.
CREATE TABLE #Taxes(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[TaxCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'Carrol', 8.74)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'FICA SS', 37.16)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'FICA Med', 8.69)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0001', 'Federal', 16.43)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'MDC', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'FICA SS', 6.94)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'Federal', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0002', 'FICA Med', 1.63)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'MDC', 0.00)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'FICA Med', 6.72)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'FICA SS', 28.75)
INSERT INTO #Taxes (EmployeeID, TaxCode, Amount) Values ('01-003-0003', 'Federal', 0.00)
select * from #Taxes
--Build Deductions Table.
CREATE TABLE #Deductions(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[DeductCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'AMHLFM', 75.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'DNTLFM', 15.63)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0001', 'EEFAM', 200.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'AMHLEE', 22.50)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'Child', 50.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'DNTLFM', 15.63)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0002', 'EEFAM', 250.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'AMHLES', 31.25)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'DNTLES', 5.00)
INSERT INTO #Deductions (EmployeeID, DeductCode, Amount) Values ('01-003-0003', 'EEFAM', 250.00)
select * from #Deductions
--Build Benefits Table.
CREATE TABLE #Benefits(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[BenefitCode] [nvarchar](30) NULL,
[Amount] [Decimal](19,4) NULL)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'AMHLFM', 225.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'DNTLFM', 46.88)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0001', 'EEFAM', 125.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'AMHLEE', 67.50)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'DNTLFM', 46.88)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0002', 'EEFAM', 125.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'AMHLES', 93.75)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'DNTLES', 15.00)
INSERT INTO #Benefits (EmployeeID, BenefitCode, Amount) Values ('01-003-0003', 'EEFAM', 125.00)
select * from #Benefits
--This is what I'm trying to build. This is how the output should look.
CREATE TABLE #DesiredOutput(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EarningPayCode] [nvarchar](30) NULL,
[EarningUnits] [Decimal](19,4) NULL,
[EarningRate] [Decimal](19,4) NULL,
[EarningAmount] [Decimal](19,4) NULL,
[TaxCode] [nvarchar](30) NULL,
[TaxAmount] [Decimal](19,4) NULL,
[DeductionCode] [nvarchar](30) NULL,
[DeductionAmount] [Decimal](19,4) NULL,
[BenefitCode] [nvarchar](30) NULL,
[BenefitAmount] [Decimal](19,4) NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', 'HoursBI', 80.00, 10.00, 800.00, 'Carrol', 8.74, 'AMHLFM', 75.00, 'AMHLFM', 225.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', 'OTHRBI', 6.00, 15.00, 90.00, 'FICA SS', 37.16, 'DNTLFM', 15.63, 'DNTLFM', 46.88)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', NULL, NULL, NULL, NULL, 'FICA Med', 8.69, 'EEFAM', 200.00, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0001', NULL, NULL, NULL, NULL, 'Federal', 16.43, NULL, NULL, NULL, NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', 'HoursBI', 40.00, 10.00, 400.00, 'MDC', 0.00, 'AMHLEE', 22.50, 'AMHLEE', 67.50)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'FICA SS', 6.94, 'Child', 50.00, 'DNTLFM', 46.88)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'Federal', 0.00, 'DNTLFM', 15.63, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0002', NULL, NULL, NULL, NULL, 'EEFAM', 1.63, 'EEFAM', 250.00, NULL, NULL)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', 'HoursBI', 75.00, 10.00, 750.00, 'MDC', 0.00, 'AMHLES', 31.25, 'AMHLES', 93.75)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'FICA SS', 28.75, 'DNTLES', 5.00, 'DNTLES', 15.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'FICA Med', 6.72, 'EEFAM', 250.00, 'EEFAM', 125.00)
INSERT INTO #DesiredOutput (EmployeeID, EarningPayCode, EarningUnits, EarningRate, EarningAmount, TaxCode, TaxAmount, DeductionCode, DeductionAmount, BenefitCode, BenefitAmount)
Values ('01-003-0003', NULL, NULL, NULL, NULL, 'Federal', 0.00, NULL, NULL, NULL, NULL)
select * from #DesiredOutput
/*
drop table #Employees
drop table #Earnings
drop table #Benefits
drop table #Deductions
drop table #Taxes
drop table #DesiredOutput
*/
-
Maybe something along these lines?:
SELECT
COALESCE(ern.EmployeeID, tax.EmployeeID, ded.EmployeeID, ben.EmployeeID) AS EmployeeID,
MAX(ern.PayCode) AS EarningPayCode,
MAX(ern.Units) AS EarningUnits,
MAX(ern.Rate) AS EarningRate,
MAX(ern.Amount) AS EarningAmount,
MAX(tax.TaxCode) AS TaxCode,
MAX(tax.Amount) AS TaxAmount,
MAX(ded.DeductCode) AS DeductionCode,
MAX(ded.Amount) AS DeductionAmount,
MAX(ben.BenefitCode) AS BenefitCode,
MAX(ben.Amount) AS BenefitAmount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY PayCode) AS row_num
FROM #Earnings
) AS ern
FULL OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY TaxCode) AS row_num
FROM #Taxes
) AS tax ON
tax.EmployeeID = ern.EmployeeID AND
tax.row_num = ern.row_num
FULL OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY DeductCode) AS row_num
FROM #Deductions
) AS ded ON
ded.EmployeeID = ern.EmployeeID AND
ded.row_num = ern.row_num
FULL OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY BenefitCode) AS row_num
FROM #Benefits
) AS ben ON
ben.EmployeeID = ern.EmployeeID AND
ben.row_num = ern.row_num
GROUP BY
COALESCE(ern.EmployeeID, tax.EmployeeID, ded.EmployeeID, ben.EmployeeID),
COALESCE(ern.row_num, tax.row_num, ded.row_num, ben.row_num)
ORDER BY
EmployeeID,
COALESCE(ern.row_num, tax.row_num, ded.row_num, ben.row_num)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
That is exactly what I wanted. Thank you so much. Could I get a bit of an explanation as to why it works as it does. I've only used CTEs four or five times and it was only for counting stuff for a matrix-ish type report(like count of how many tasks were in what status per employee per day).
Thanks again so much.
Brian
-
Sure, I'll try to explain as best I can.
Each subquery (they're not technically CTEs but subqueries) reads one of the separate tables to be joined, and assigns a sequential row# to the rows, but resetting to 1 for each new EmployeeID. For example, if Emp 1 had 3 rows in the first table and Emp2 had 2 rows, the output would be:
Emp=1 Row#=1 ...other columns for that row...
Emp=1 Row#=2 ...other columns for that row...
Emp=1 Row#=3 ...other columns for that row...
Emp=2 Row#=1 ...other columns for that row...
Emp=2 Row#=2 ...other columns for that row...
Every separate table has its own similar subquery; the second table's subquery might result in:
Emp=1 Row#=1 ...other columns for that row...
Emp=1 Row#=2 ...other columns for that row...
Emp=2 Row#=1 ...other columns for that row...
Emp=2 Row#=2 ...other columns for that row...
Emp=2 Row#=3 ...other columns for that row...
Emp=2 Row#=4 ...other columns for that row...
And so on. All the subqueries together complete the data setup.
But the key to the logic is the FULL OUTER JOINs (FOJ), using EmployeeID and Row#, then done against each of those subqueries. A FOJ is effectively a LEFT OUTER JOIN (LOJ) and a RIGHT OUTER JOIN (ROJ) combined. A FOJ tells SQL to keep both table's rows even if there is not a match in other table. So, if a given Emp has 4 rows in one table and only 1 in the others, all 4 rows are still kept.
But, it's still a JOIN, where Row#1 from each table joins with only other Row#1s for the same Emp. This means a given Employee always ends up with exactly as many rows as the max rows he/she had in any individual table. If some tables don't have matching rows, that side of the join and "missing", and SQL automatically sets its columns to NULL, just as it does for any ROJ or LOJ.
By changing the ORDER BY in the subqueries, you could change the Row#s and change what values were shown with what other values on the same row, but you would not change the number of rows displayed. As a good example of this, change the ORDER BY to "ORDER BY ...Code DESC" and notice how it affects the final result set.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply