Solved

Problem with SQL query

Posted on 2015-02-03
30
73 Views
Last Modified: 2015-02-04
I have the following code
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)

Open in new window

Which brings back records for the last weeks pay. The problem I have is if the employee has been on shifts or overtime then it brings back several records as the PayEmployeePayCodes table has a record for each shift and hours worked on that shift.

Ideally if possible I would like to return the PayEmployeePayCodes.PayCode as a column header and the PayEmployeePayCodes.InputValue as the value under each PayCode.

I have attached a file with the records returned for one employee with the records as they are returned and the layout I would like to get to.

Any ideas ?
Pay.xls
0
Comment
Question by:RickCooper
  • 17
  • 12
30 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Please try:

With myTable as (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
)

Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI, 
                         BasicSalary, PayCode, PeriodRateUsed, 
                         Title, Forename, Surname, CodeType

Pivot(Sum([InputValue]) for [PayCode] in ([BP], [AVSA], {OT15], [AF], [ANH}) as MyPivotTable;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Sorry,
 I get this error
Invalid text or symbol.
Error in SELECT clause: expression near 'PIVOT'.
Missing FROM clause.
Error in SELECT clause: expression near 'OT15'.
Error in SELECT clause: expression near '[ANH}) as MyPivotTable;
                                          '.
Unable to parse query text.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
I missed out a line.

With myTable as (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
)

Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI, 
                         BasicSalary, PayCode, PeriodRateUsed, 
                         Title, Forename, Surname, CodeType
From myTable
Pivot(Sum([InputValue]) for [PayCode] in ([BP], [AVSA], {OT15], [AF], [ANH}) as MyPivotTable;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
I changed the two { } symbols for [ ] and now get error common table expression defined but not used
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Have you add my new line 18?
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
On your second code I changed the same as above.
i now get
Incorrect syntax near '('
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
There were two typos in the last line { } i swapped for square brackets.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Try this:

With myTable as (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
)

Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI, 
                         BasicSalary, PayCode, PeriodRateUsed, 
                         Title, Forename, Surname, CodeType
From myTable
Pivot(Sum([InputValue]) for [PayCode] in ([BP], [AVSA], [OT15], [AF], [ANH])) as MyPivotTable;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Sorry i still get

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '('.
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Checked the DB and the compatibility was SQL2000 I have changed it to 2005

I now get this error
Msg 207, Level 16, State 1, Line 16
Invalid column name 'PayCode'.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Try:

With myTable as (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
)

Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI, 
                         BasicSalary, PeriodRateUsed, 
                         Title, Forename, Surname, CodeType
From myTable
Pivot(Sum([InputValue]) for [PayCode] in ([BP], [AVSA], [OT15], [AF], [ANH])) as MyPivotTable;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
No errors but no headings for each PayCode
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Can you please post the results for the following:

SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)

Open in new window


and

With myTable as (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
)

Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI, 
                         BasicSalary, PeriodRateUsed, 
                         Title, Forename, Surname, CodeType
From myTable
Pivot(Sum([InputValue]) for [PayCode] in ([BP], [AVSA], [OT15], [AF], [ANH])) as MyPivotTable;

Open in new window


as I am in the dark.
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Hi Philip

 With the first code I get all the rows from the select statement.

With the second code I only get the rows from second select statement.
Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI,
                         BasicSalary, PeriodRateUsed,
                         Title, Forename, Surname, CodeType

No errors. but when you add the PayCode to the second select statement I get the error as above.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Please paste the results, as I am in the dark.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:RickCooper
Comment Utility
I have just tried this,
With myTable as (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
)

Select PayrollPeriod, EmployeeNumber, PeriodGrossPay, PeriodEmployersNI, 
                         BasicSalary, PeriodRateUsed, 
                         Title, Forename, Surname, CodeType,PayCode
From myTable

Open in new window


which works but when you add the line
                                     
Pivot(Sum([InputValue]) for [PayCode] in ([AA], [O1], [O2])) as MyPivotTable;

Open in new window


It errors

Msg 207, Level 16, State 1, Line 18
Invalid column name 'PayCode'.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
delete ", PayCode" from the end of line 17 before adding that line.
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Output as requested.
Book2.xlsx
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
I deleted the PayCode and it retruns the same output as the second example above.
Its not pivoting the results.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
I NEED YOU TO PASTE THE RESULTS.

The reason why is suddenly you have changed the last line to

[AA], [O1], [O2]

which is different from your spreadsheet.

THEREFORE, PASTE THE RESULTS from the first and second queries that I have previously posted as I am in the dark.

If you do not, then I will unmonitor this question.
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
I'm sorry your upset I can see why but the reason I changed the line to [AA], [O1], [O2] is I created a record which specifically had these codes.

201543      3164         618.03      64.17      0.00      AA        39.00      10.4750      Mr          J                              B              5.07      A
201543      3164         618.03      64.17      0.00      O1        14.00      12.5700      Mr          J                                B                   5.07      A
201543      3164         618.03      64.17      0.00      O2        2.00              16.7600      Mr          J                             B                   5.07      A

Second Result
201543      3164         618.03      64.17      0.00      10.4750      Mr          J                              B                    A
201543      3164         618.03      64.17      0.00      12.5700      Mr          J                                B                    A
201543      3164         618.03      64.17      0.00      16.7600      Mr          J                               B                    A


i get the same results as result 2 if the last line has [BP], [AVSA], [OT15], [AF], [ANH] or [AA], [O1], [O2]
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
No, I'm not upset. If the answer wasn't working for you, then I needed real data to work on.

I'll get back to you.
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Ok Thanks.

I have changed the code to this:
Select  EmployeeNumber, [AA], [O1], [O2]
FROM (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
) sourcedata

Pivot(Sum([InputValue]) for [PayCode] in ([AA], [O1], [O2])) as MyPivotTable;

Open in new window


and I get this back

EmployeeNumber      AA      O1      O2
3164         39.00      NULL      NULL
3164         NULL      14.00      NULL
3164         NULL      NULL      2.00

I will keep trying , thanks for your help so far.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
Then let's expand what you have:

With myTable as (
Select  EmployeeNumber, [AA], [O1], [O2]
FROM (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
) sourcedata

Pivot(Sum([InputValue]) for [PayCode] in ([AA], [O1], [O2])) as MyPivotTable)
Select EmployeeNumber, ISNULL(sum(AA),0) as AA, ISNULL(sum(O1),0) as O1, ISNULL(sum(O2),0) as O2
From myTable
Group by EmployeeNumber

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Hi Phillip,

 I have been working on this.
The code you posted gives me the same output as this code:
SELECT PayrollPeriod,EmployeeNumber, [AA],[O1],[O2]
FROM
  (
  SELECT         PayrollPeriod,EmployeeNumber,  PayCode, InputValue FROM    PayEmployeePayCodes
  WHERE        (PayrollPeriod = 201543) AND (EmployeeNumber = '3164') AND (InputValue > 0) ) sourcedata
PIVOT
  (sum( InputValue) for PayCode in ( [AA],[O1],[O2])) myTable

Open in new window


I also need the rest of the information from this code adding to it.
 SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, TimGEEmployeeDetails.Title, TimGEEmployeeDetails.Forename, TimGEEmployeeDetails.Surname, 
                         PayEmployeePensionScheme.CurrEmployerAmount
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164')

Open in new window


I found that the pivot  would only work if the select had only one table in it.

If I could join the two results in a temp table that would work,, the PayrollPeriod and the EmployeeNumber are the same in both queries.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
What does this give you?

Select  EmployeeNumber, [AA], [O1], [O2]
FROM (
SELECT        PayEmployeeMaster.EmployeeNumber, sum(PayEmployeePayCodes.PayCode) as PayCode, Sum(PayEmployeePayCodes.InputValue) as InputValue, 
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
GROUP BY PayEmployeeMaster.EmployeeNumber
) sourcedata

Pivot(Sum([InputValue]) for [PayCode] in ([AA], [O1], [O2])) as MyPivotTable;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
It errors sorry,

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
0
 
LVL 1

Author Comment

by:RickCooper
Comment Utility
Hi Philip,

 The following code works
With myTable as (
Select  PayrollPeriod,EmployeeNumber,Title,FullName,PeriodGrossPay,PeriodEmployersNI,CurrEmployerAmount, [AA], [O1], [O2]
FROM (
SELECT        PayEmployeeMaster.PayrollPeriod, PayEmployeeMaster.EmployeeNumber, PayEmployeeMaster.PeriodGrossPay, PayEmployeeMaster.PeriodEmployersNI, 
                         PayEmployeeMaster.BasicSalary, PayEmployeePayCodes.PayCode, PayEmployeePayCodes.InputValue, PayEmployeePayCodes.PeriodRateUsed, 
                         TimGEEmployeeDetails.Title, TimGEEmployeeDetails.FullName, PayEmployeePensionScheme.CurrEmployerAmount, 
                         PayEmployeePayCodes.CodeType
FROM            PayEmployeeMaster INNER JOIN
                         TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod AND 
                         PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber LEFT OUTER JOIN
                         PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber AND 
                         PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE        (PayEmployeeMaster.PayrollPeriod = 201543) AND (PayEmployeeMaster.EmployeeNumber = '3164') AND (PayEmployeePayCodes.InputValue > 0)
) sourcedata

Pivot(Sum([InputValue]) for [PayCode] in ([AA], [O1], [O2])) as MyPivotTable)
Select PayrollPeriod,EmployeeNumber,FullName,PeriodGrossPay,PeriodEmployersNI,CurrEmployerAmount, ISNULL(sum(AA),0) as AA, ISNULL(sum(O1),0) as O1, ISNULL(sum(O2),0) as O2
From myTable
Group by PayrollPeriod,EmployeeNumber,Title,FullName,PeriodGrossPay,PeriodEmployersNI,CurrEmployerAmount

Open in new window


I will accept one of your posts as the answer as it was the basis of the code above.
i would like to thank you for your efforts in getting this resolved.
0
 
LVL 1

Author Closing Comment

by:RickCooper
Comment Utility
This was the basis of solving the problem.
The solution is in the last post. which is based on the input from Phillip.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I am not seeking any change in outcome, just wishing to make an observation.

At times old-fashioned ways are arguably easier. In the past when SQL variants had no "pivot" we just used case expressions to achieve pivoted data. Plus, using common table expressions when there is no technical need to do so is a cosmetic choice.

So, purely for comparison (no "with", no "pivot")

SELECT
      PayEmployeeMaster.PayrollPeriod
    , PayEmployeeMaster.EmployeeNumber
    , TimGEEmployeeDetails.Title
    , TimGEEmployeeDetails.FullName
    , PayEmployeeMaster.PeriodGrossPay
    , PayEmployeeMaster.PeriodEmployersNI
    , PayEmployeePensionScheme.CurrEmployerAmount
    , ISNULL(SUM(case when PayEmployeePayCodes.PayCode = 'AA' then PayEmployeePayCodes.InputValue else 0 end),0) AS [AA]
    , ISNULL(SUM(case when PayEmployeePayCodes.PayCode = 'O1' then PayEmployeePayCodes.InputValue else 0 end),0) AS [O1]
    , ISNULL(SUM(case when PayEmployeePayCodes.PayCode = 'O2' then PayEmployeePayCodes.InputValue else 0 end),0) AS [O2]
FROM PayEmployeeMaster
      INNER JOIN TimGEEmployeeDetails ON PayEmployeeMaster.EmployeeNumber = TimGEEmployeeDetails.EmployeeNumber
      LEFT OUTER JOIN PayEmployeePayCodes ON PayEmployeeMaster.PayrollPeriod = PayEmployeePayCodes.PayrollPeriod
                  AND PayEmployeeMaster.EmployeeNumber = PayEmployeePayCodes.EmployeeNumber
      LEFT OUTER JOIN PayEmployeePensionScheme ON PayEmployeeMaster.EmployeeNumber = PayEmployeePensionScheme.EmployeeNumber
                  AND PayEmployeeMaster.PayrollPeriod = PayEmployeePensionScheme.PayrollYearPeriod
WHERE (PayEmployeeMaster.PayrollPeriod = 201543)
      AND (PayEmployeeMaster.EmployeeNumber = '3164')
      AND (PayEmployeePayCodes.InputValue > 0)
GROUP BY
        PayEmployeeMaster.PayrollPeriod
      , PayEmployeeMaster.EmployeeNumber
      , TimGEEmployeeDetails.Title
      , TimGEEmployeeDetails.FullName
      , PayEmployeeMaster.PeriodGrossPay
      , PayEmployeeMaster.PeriodEmployersNI
      , PayEmployeePensionScheme.CurrEmployerAmount
;

Open in new window

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now