Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Inner Join Required?

Author  Topic 

Kerryman
Starting Member

17 Posts

Posted - 2013-06-13 : 05:35:39

Hi, I'm hoping that someone can advise me on the issue shown below. The following is the output from a single table, if you consider rows 2 and 3 it seems to me that the information could be expressed on one row without the nulls or zeros, likewise for rows 3 and 4 and rows 6 to 10 could be expressed in three rows. My SQL knowledge is limited but could the this outcome be achieved using a self join?


EmployeeID Name Year_ Week_ Contract_ BH OT1 OT2 OT3 PayType1_ PT1
BIR1 EMPLOYEE 1 2014 10 56235 1200 0 300 300 NULL 0
BIR1 EMPLOYEE 1 2014 10 56235 0 0 0 0 AFP 50
BIS2 EMPLOYEE 2 2014 10 56235 0 0 0 0 AFP 125
BIS2 EMPLOYEE 2 2014 10 56235 3000 0 360 0 NULL 0
BRO1 EMPLOYEE 3 2014 10 56235 600 0 60 0 NULL 0
BRO1 EMPLOYEE 3 2014 10 56236 600 0 60 0 NULL 0
BRO1 EMPLOYEE 3 2014 10 56237 600 0 60 0 NULL 0
BRO1 EMPLOYEE 3 2014 10 56235 0 0 0 0 APP 25
BRO1 EMPLOYEE 3 2014 10 56236 0 0 0 0 APP 25
BRO1 EMPLOYEE 3 2014 10 56237 0 0 0 0 PE 250

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:44:23
no need of self join use group by


SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],
SUM(BH) AS BH,
SUM(OT1) AS OT1,
SUM(OT2) AS OT2,
SUM(OT3) AS OT3,
MAX([PayType1_]) AS [PayType1_],
SUM(PT1) AS PT1
FROM Table
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-13 : 05:55:30
Perfect! Thanks Visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:56:08
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -