Solved

Problem with SQL query

Posted on 2015-02-03
30
74 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
ID: 40586160
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
ID: 40586174
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
ID: 40586175
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
ID: 40586177
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
ID: 40586183
Have you add my new line 18?
0
 
LVL 1

Author Comment

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

Author Comment

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

Expert Comment

by:Phillip Burton
ID: 40586195
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
ID: 40586199
Sorry i still get

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

Author Comment

by:RickCooper
ID: 40586203
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
ID: 40586205
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
ID: 40586213
No errors but no headings for each PayCode
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586254
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
ID: 40586289
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
ID: 40586300
Please paste the results, as I am in the dark.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:RickCooper
ID: 40586302
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
ID: 40586307
delete ", PayCode" from the end of line 17 before adding that line.
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40586317
Output as requested.
Book2.xlsx
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40586320
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
ID: 40586327
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
ID: 40586386
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
ID: 40586391
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
ID: 40586427
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
ID: 40586583
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
ID: 40586661
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
ID: 40586686
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
ID: 40586692
It errors sorry,

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

Author Comment

by:RickCooper
ID: 40586736
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
ID: 40586740
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
ID: 40588068
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 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

28 Experts available now in Live!

Get 1:1 Help Now