• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

Problem with SQL query

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
RickCooper
Asked:
RickCooper
  • 17
  • 12
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
RickCooperAuthor Commented:
I changed the two { } symbols for [ ] and now get error common table expression defined but not used
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you add my new line 18?
0
 
RickCooperAuthor Commented:
On your second code I changed the same as above.
i now get
Incorrect syntax near '('
0
 
RickCooperAuthor Commented:
There were two typos in the last line { } i swapped for square brackets.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
Sorry i still get

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '('.
0
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
No errors but no headings for each PayCode
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please paste the results, as I am in the dark.
0
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
delete ", PayCode" from the end of line 17 before adding that line.
0
 
RickCooperAuthor Commented:
Output as requested.
Book2.xlsx
0
 
RickCooperAuthor Commented:
I deleted the PayCode and it retruns the same output as the second example above.
Its not pivoting the results.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
RickCooperAuthor Commented:
It errors sorry,

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
0
 
RickCooperAuthor Commented:
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
 
RickCooperAuthor Commented:
This was the basis of solving the problem.
The solution is in the last post. which is based on the input from Phillip.
0
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 17
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now