Question about rows and columns

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

General SQL Server Forums

New to SQL Server Programming

Question about rows and columns

Author

Topic

RalphWiggum
Starting Member

13 Posts

Posted - 2011-10-12 : 14:13:28

I have a query:
SELECT
E_TimeCards.TC_PDATE,-- AS [Date],
E_Employees.D_LNAME + ', ' + E_Employees.D_FNAME, --AS [Last, First],
E_Employees.D_EMPNO, --AS [Employee#],
E_Employees.COMPANY, --AS [Company Code],
E_Employees.D_LEVEL2 + ' - ' + COM_CodeTables.T_DESC, --AS [Location],
E_Employees.D_SUPERVIS,-- AS [Supervisor],
CONVERT(VARCHAR, E_Schedules.S_START) + ' - ' + CONVERT(VARCHAR, E_Schedules.S_STOP), --AS [Schedule],
E_Schedules.S_LUNMIN,-- AS [Lunch Minutes],
(SELECT CONVERT(VARCHAR, E_TimeCards.TC_RTIME, 108) WHERE SYS_PunchTypes.P_SHORT = 'IND') AS [IND],
(SELECT CONVERT(VARCHAR, E_TimeCards.TC_RTIME, 108) WHERE SYS_PunchTypes.P_SHORT = 'OUTD') AS [OUTD],
COM_ErrorClassDetail.ER_DES2-- AS [Error/Comments]

FROM E_Employees
INNER JOIN E_TimeCards ON E_Employees.D_EMPNO = E_TimeCards.TC_EMPNO AND E_Employees.COMPANY = E_TimeCards.COMPANY
LEFT OUTER JOIN COM_ErrorClassDetail ON E_TimeCards.TC_ERROR = COM_ErrorClassDetail.ER_DES1 AND COM_ErrorClassDetail.COMPANY = E_Employees.COMPANY AND COM_ErrorClassDetail.ER_CODE = '01'
INNER JOIN SYS_PunchTypes ON E_TimeCards.TC_TYPE = SYS_PunchTypes.P_TYPE
INNER JOIN E_Schedules ON E_Employees.D_EMPNO = E_Schedules.S_EMPNO AND E_Employees.COMPANY = E_Schedules.COMPANY AND E_TimeCards.TC_PDATE = E_Schedules.S_DATE
INNER JOIN COM_CodeTables ON E_Employees.D_LEVEL2 = COM_CodeTables.CODE AND E_Employees.COMPANY = COM_CodeTables.COMPANY

WHERE E_TimeCards.TC_PDATE = DATEADD(DAY, -1, CONVERT(NVARCHAR, GetDate(), 101))
AND E_Employees.D_ACTIVE = 'A'
AND S_SHIFT IN ('01', '02')
AND COM_CodeTables.TABLEID = 'L2'
AND E_TimeCards.TC_TYPE IN ('01', '11')

ORDER BY E_Employees.D_EMPNO

That gives a result:
DATE Name EMPNO COMPANY Location Manager Schedule LUNCH IND OUTD Error
---------------------------------------------------------------------------------------------------------------------------------2011-10-11 00:00:00.000 Rita 1060 GDT 01 - Morton Thomas P 800 - 1630 30 08:00:00 NULL NULL
2011-10-11 00:00:00.000 Rita 1060 GDT 01 - Morton Thomas P 800 - 1630 30 NULL 7:00:00 NULL

What I want:

DATE Name EMPNO COMPANY Location Manager Schedule LUNCH IND OUTD Error
---------------------------------------------------------------------------------------------------------------------------------
2011-10-11 00:00:00.000 Rita 1060 GDT 01 - Morton Thomas P 800 - 1630 30 08:00:00 17:00:00 NULL

Suggestions? I've not tried using most of what is suggested (COALESCE, PIVOT, UNPIVOT) --- yet. I was hoping that there may be a solution that doesn't require re-writing the entire query. Perhaps a table variable or temp table?

The table E_TimeCards has 1 row for each timeclock punch (IND, OUTD in this example). The data is correct, I just want it displayed on 1 line instead of 2.

Thanks in advance!

Edit: Wow, when I created this topic, it was formatted perfectly :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:30:06

add a GROUP BY including all fields except IND and OUTD
in the select list replace IND and OUTD by MAX(IND) and MAX(OUTD)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources