SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Inner Join Required?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kerryman
Starting Member

United Kingdom
17 Posts

Posted - 06/13/2013 :  05:35:39  Show Profile  Reply with Quote

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

India
52249 Posts

Posted - 06/13/2013 :  05:44:23  Show Profile  Reply with Quote
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

United Kingdom
17 Posts

Posted - 06/13/2013 :  05:55:30  Show Profile  Reply with Quote
Perfect! Thanks Visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/13/2013 :  05:56:08  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000