Author |
Topic |
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-18 : 10:15:54
|
Hi, I’ve had some help before with this issue but additional columns placed in the table have altered the solution provided by visakh16. The following is the output from a single table, if you consider rows 1 and 2 it seems to me that the information could be expressed on one row without the nulls or zeros, likewise for rows 3 and 4 etc;EmployeeID ¦Name¦Year_¦Week_¦Contract_¦Site_ ¦BH¦OT1¦OT2¦OT3¦PayType1_¦PT1code_¦PT1¦PayType2_¦PT2code_¦PT2 BIR1¦EMPLOYEE1¦2014¦9¦56235¦RESOURCE ROOM**¦10¦0¦1¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00 BIR1¦EMPLOYEE1¦2014¦9¦56235¦RESOURCE ROOM**¦0¦0¦0¦0¦Travel Expenses¦ET¦25.00¦Price Work Hours¦AW¦20.00 BIR1¦EMPLOYEE1¦2014¦9¦56236!LAGO INTERNALL***¦10¦ 0¦1¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00 BIR1¦EMPLOYEE1¦2014¦9¦56236¦LAGO INTERNAL*** ¦0¦ 0¦0¦0¦Price Work Hours¦AW¦25.00¦NULL¦NULL¦0.00 BIR1¦EMPLOYEE1¦2014¦9¦56237¦HOOTON STREET** ¦10¦ 0¦2¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00 BIR1¦EMPLOYEE1¦2014¦9¦56237¦HOOTON STREET** ¦0¦ 0¦0¦0¦Telephone VAT¦TV¦25.00¦NC Travel Ex¦XET¦15.00 BIS2¦EMPLOYEE2¦2014¦9¦56241¦SVR REFURB OWN* ¦10¦ 0¦1¦0¦NULL¦NULL¦0.00¦NUL¦L NULL¦0.00 BIS2¦EMPLOYEE2¦2014¦9¦56241¦SVR REFURB OWN* ¦0¦ 0¦0¦0¦Price Work Hours¦AW¦25.00¦Digs Paid¦DP¦ 20.00 BIS2¦EMPLOYEE2¦2014¦9¦56242¦265 ILKESTON ROAD ¦10¦ 0¦1¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00 BIS2¦EMPLOYEE2¦2014¦9¦56242¦265 ILKESTON ROAD ¦0¦ 0¦0¦0¦Telephone VAT¦TV¦25.00¦Price Work Hours¦AW¦20.00The following select statement originally excluded the PT1code_ and PT2code_ to achieve the one row outcome;SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int))AS 'BH',SUM(CAST(OT1 / 60 AS int))AS 'OT1',SUM(CAST(OT2 / 60 AS int))AS 'OT2',SUM(CAST(OT3 / 60 AS int))AS 'OT3',MAX([PayType1_]) AS [PayType1_],[PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],[PT2code_],SUM(PT2) AS PT2,FROM #TimesheetXLGROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] The advent of the PT1code_, PT2code_ columns have changed things however as I can no longer use the MAX function else the return is incorrect. Any idea how I might return to the preferred output? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 10:21:32
|
follow the same patternSELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int))AS 'BH',SUM(CAST(OT1 / 60 AS int))AS 'OT1',SUM(CAST(OT2 / 60 AS int))AS 'OT2',SUM(CAST(OT3 / 60 AS int))AS 'OT3',MAX([PayType1_]) AS [PayType1_],MAX([PT1code_]) AS [PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],MAX([PT2code_]) AS [PT2code_],SUM(PT2) AS PT2FROM #TimesheetXLGROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-18 : 10:43:03
|
Hi visakh16, thank you again for your response, I did try the same pattern but if an employee has multiple paytypes on one contract then MAX(PT1code_) only gives me the maximum value for the codes inseted into the table... If that is not clear let me know if you would like to see a sample of the output |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 14:16:28
|
ok...in that case how are you planning to show the multiple values when you make them into a single row per EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] combination? please show your expected output------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-18 : 15:43:15
|
Hi visakh16, in a nutshell there are 3 relevant records in the table, the output with MAX on the PTcode colums is this;EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT3BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 30 0 3 0 Underpayment UP 75 Digs Paid DP 60 Travel Expenses DP 45Ideally, if its possible I'd like to see this;EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT3BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20 Travel Expenses AF 15BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20 Mileage Expenses AI 15BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20 Telephone Charges DP 15 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 01:04:35
|
then wont your initial query give you the same output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-19 : 04:36:01
|
Hi visakh16, it gives me the following, the difference being that BH and OT integer values arn't summed;EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT2BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 10 0 1 0 Price Work Hours AW 25 Additional Formans Pay AF 20 Travel Expenses AF 15BOO2 EMPLOYEE2 2014 9 56235 RESOURCE ROOM 10 0 1 0 Purchase Expense EP 25 APP - Incent Bonus AI 20 Mileage Expenses AI 15BOO2 EMPLOYEE3 2014 9 56235 RESOURCE ROOM 10 0 1 0 Underpayment UP 25 Digs Paid DP 20 Telephone Charges DP 15 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 04:38:54
|
do you mean this then?SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',MAX([PayType1_]) AS [PayType1_],[PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],[PT2code_],SUM(PT2) AS PT2,FROM #TimesheetXLGROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-19 : 05:17:51
|
Not sure why but the following error is being generated?Column '#TimesheetXL.BH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 05:22:27
|
Sorry it should be thisSELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],BH,OT1,OT2,OT3,MAX([PayType1_]) AS [PayType1_],[PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],[PT2code_],SUM(PT2) AS PT2FROM(SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',[PayType1_],[PT1code_],PT1,[PayType2_],[PT2code_],PT2FROM #TimesheetXL)tGROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-19 : 05:40:00
|
Hi visakh16, apologies, on my previous post the employees should all have been shown as 'EMPLOYEE1' not 1,2 and 3. The output from your last solution provides this:EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 NULL NULL 0 NULL NULL 0BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Purchase Expense EP 25 APP - Incent Bonus AI 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Underpayment UP 25 Digs Paid DP 20The total charge for wkeek 9 to contract 56235 should be BH 30 and OT2 3. The other paytypes are correct. Ideally I'd like to express on 3 rows like this;EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT2BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20 Travel Expenses AF 15BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20 Mileage Expenses AI 15BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20 Telephone Charges DP 15If thats possible? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 05:53:11
|
[code]SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],CASE WHEN Seq=1 THEN BH ELSE 0 END AS BH,CASE WHEN Seq=1 THEN OT1 ELSE 0 END AS OT1,CASE WHEN Seq=1 THEN OT2 ELSE 0 END AS OT2,CASE WHEN Seq=1 THEN OT3 ELSE 0 END AS OT3,[PayType1_],[PT1code_],PT1,[PayType2_],[PT2code_],PT2FROM(SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],ROW_NUMBER() OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ORDER BY EmployeeID ) AS Seq,BH,OT1,OT2,OT3,MAX([PayType1_]) AS [PayType1_],[PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],[PT2code_],SUM(PT2) AS PT2FROM(SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',[PayType1_],[PT1code_],PT1,[PayType2_],[PT2code_],PT2FROM #TimesheetXLWHERE [PT1code_] > ''OR [PT2code_] > '')tGROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3)r[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-19 : 06:03:32
|
Hi visakh16, the output from your last solution provides this:EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Price Work Hours AW 25 Additional Formans Pay AF 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20On three rows but the BH and OT sums are expressed as zero |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 06:13:21
|
what does this give you?SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],ROW_NUMBER() OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ORDER BY EmployeeID ) AS Seq,BH,OT1,OT2,OT3,MAX([PayType1_]) AS [PayType1_],[PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],[PT2code_],SUM(PT2) AS PT2FROM(SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',[PayType1_],[PT1code_],PT1,[PayType2_],[PT2code_],PT2FROM #TimesheetXLWHERE [PT1code_] > ''OR [PT2code_] > '')tGROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-19 : 06:25:15
|
The following;EmployeeID Name Year_ Week_ Contract_ Site_ Seq BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 1 0 0 0 0 Price Work Hours AW 25 Additional Formans Pay AF 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 2 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 3 0 0 0 0 Underpayment UP 25 Digs Paid DP 20 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 06:34:23
|
what about this?SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],CASE WHEN Seq=1 THEN BH ELSE 0 END AS BH,CASE WHEN Seq=1 THEN OT1 ELSE 0 END AS OT1,CASE WHEN Seq=1 THEN OT2 ELSE 0 END AS OT2,CASE WHEN Seq=1 THEN OT3 ELSE 0 END AS OT3,[PayType1_],[PT1code_],PT1,[PayType2_],[PT2code_],PT2FROM(SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],ROW_NUMBER() OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ORDER BY EmployeeID ) AS Seq,BH,OT1,OT2,OT3,MAX([PayType1_]) AS [PayType1_],[PT1code_],SUM(PT1) AS PT1,MAX([PayType2_]) AS [PayType2_],[PT2code_],SUM(PT2) AS PT2FROM(SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',[PayType1_],[PT1code_],PT1,[PayType2_],[PT2code_],PT2FROM #TimesheetXL)tWHERE [PT1code_] > ''OR [PT2code_] > ''GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3)r ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Kerryman
Starting Member
17 Posts |
Posted - 2013-06-19 : 06:38:51
|
Perfect, thanks for all your help!EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 06:48:16
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|