October 14, 2008 at 10:52 pm
I have a table named employee inout and having below data;
Employee codeIn dateIn timeOut time
011-Oct-08 7:005:00
012-Oct-086:305:00
013-Oct-087:104:39
021-Oct-08 6:003:00
022-Oct-087:005:00
023-Oct-088:006:00
I want to see the data in following format;
Employee codeDay 1Day 2Day 3
InOutInOutInOut
017:005:006:305:007:104:39
26:003:007:005:008:006:00
Any help will be highly appreciated
October 16, 2008 at 8:11 am
I'm thinking this will work better as a table valued function rather than a view, since you'll probably want to be reporting on different dates. Maybe something like this if you will always be reporting on the same number of days?
CREATE FUNCTION dbo.EmployeeInOutTimes (
@StartDate datetime,
@EndDate datetime
) RETURNS table
AS RETURN(
SELECT days.EmployeeCode,
MAX(CASE WHEN days.DayNum = 1 THEN days.InTime ELSE NULL END) AS Day1InTime,
MAX(CASE WHEN days.DayNum = 1 THEN days.OutTime ELSE NULL END) AS Day1OutTime,
MAX(CASE WHEN days.DayNum = 2 THEN days.InTime ELSE NULL END) AS Day2InTime,
MAX(CASE WHEN days.DayNum = 2 THEN days.OutTime ELSE NULL END) AS Day2OutTime,
MAX(CASE WHEN days.DayNum = 3 THEN days.InTime ELSE NULL END) AS Day3InTime,
MAX(CASE WHEN days.DayNum = 3 THEN days.OutTime ELSE NULL END) AS Day3OutTime
MAX(CASE WHEN days.DayNum = 4 THEN days.InTime ELSE NULL END) AS Day4InTime,
MAX(CASE WHEN days.DayNum = 4 THEN days.OutTime ELSE NULL END) AS Day4OutTime
MAX(CASE WHEN days.DayNum = 5 THEN days.InTime ELSE NULL END) AS Day5InTime,
MAX(CASE WHEN days.DayNum = 5 THEN days.OutTime ELSE NULL END) AS Day5OutTime
FROM
(SELECT EmployeeCode, InTime, OutTime,
ROW_NUMBER() OVER (PARTITION BY EmployeeCode ORDER BY InDate) AS DayNum
FROM #EmployeeTime
WHERE InDate BETWEEN @StartDate AND @EndDate) days
GROUP BY days.EmployeeCode)
GO
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply