October 12, 2011 at 11:32 am
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.000Rita 1060 GDT 01 - MortonThomas P 800 - 16303008:00:00 NULL
2011-10-11 00:00:00.000Rita 1060 GDT 01 - MortonThomas P 800 - 163030 NULL 17:00:00NULL
What I want:
DATE Name EMPNO COMPANY Location Manager Schedule LUNCH IND OUTD Error
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
2011-10-11 00:00:00.000Rita 1060 GDT 01 - MortonThomas P 800 - 16303008: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!
October 12, 2011 at 1:18 pm
Try using CTE with ROW_NUMBER() . Delete from CTE where rowNUM > 1
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 12, 2011 at 1:34 pm
SQL_By_Chance (10/12/2011)
Try using CTE with ROW_NUMBER() . Delete from CTE where rowNUM > 1
I don't think this will help here since both rows need to be merged (to combine the IND the first one and OUTD of the second one)
I would merge each two corresponding rows of the E_TimeCards table (either using MAX() or a self join based on ROW_NUMBER) and join this result to the rest of the tables.
The sample you provided most probably doesn't completely describe the possible scenarios: How many rows will be there if the person would leave in between? How do you handle a schedule that ends the following day? And a few more questions...
Please remember: Our answer can just be based on what you've posted. The more precise the scenario is, the better our suggestions will fit.
October 12, 2011 at 1:48 pm
I don't think this will help here since both rows need to be merged (to combine the IND the first one and OUTD of the second one)
I would merge each two corresponding rows of the E_TimeCards table (either using MAX() or a self join based on ROW_NUMBER) and join this result to the rest of the tables.
Thanks Lutz for correcting.
In direction of what Lutz suggests
I would Create a CTE with Row_NUM (to create ID )
WITH CTE
AS
( -- Your Code --)
-- ==== apply self join : -
Select a.IND,b.OUTD
FROM CTE A JOIN CTE B
ON A.ID = B.ID+1 --considering the ID would be +1 for Out Time entry
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 14, 2011 at 10:21 am
LutzM (10/12/2011)
SQL_By_Chance (10/12/2011)
Try using CTE with ROW_NUMBER() . Delete from CTE where rowNUM > 1I don't think this will help here since both rows need to be merged (to combine the IND the first one and OUTD of the second one)
I would merge each two corresponding rows of the E_TimeCards table (either using MAX() or a self join based on ROW_NUMBER) and join this result to the rest of the tables.
The sample you provided most probably doesn't completely describe the possible scenarios: How many rows will be there if the person would leave in between? How do you handle a schedule that ends the following day? And a few more questions...
Please remember: Our answer can just be based on what you've posted. The more precise the scenario is, the better our suggestions will fit.
Good suggestions, which have been accounted for within the application, really. Everytime a person leaves, he/she must punch out on the clock with certain codes. I'm using these codes to pull only IND (in for the day) and OUT (out for the day) punches which is all I'm needing at the moment.
The problem is when they accidentally punch IND or OUTD twice -- user error. I'm not sure I can find a way around that, but it should be pretty obvious within the data set what happened.
Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply