Wednesday, January 24, 2018

Converting Rows To Columns with DateTime Column in MSSQL

Good evening!
An issue was brought up by a developer on how to convert rows of DateTime values into columns using MS Access DB with functionality similar to pivoting of MSSQL.The problem has an added complexity since specific portions of the DateTime will be extracted too. See original post here: Converting Columns to Rows in MS Access. The data presented is similar to a Timesheet entry wherein an employee has login/logout records. The solution presented in the thread involves usage of subquery and joins designed for MS Access Db. I modified the accepted answer to a T-SQL query using Group By, Max() function, Coalesce() and without subquery.
SELECT 
  LogInfo.fldMachineId, 
  Year(LogInfo.fldLogDate) as [Year], 
  Month(LogInfo.fldLogDate) as [Month], 
  Day(LogInfo.fldLogDate) as [Day],
  Max(Coalesce(Convert(varchar(5), T2.fldLogDate, 108),'')) as TimeIn1,
  Max(Coalesce(Convert(varchar(5), T3.fldLogDate, 108),'')) as TimeOut1, 
  Max(Coalesce(Convert(varchar(5), T4.fldLogDate, 108),'')) as TimeIn2, 
  Max(Coalesce(Convert(varchar(5), T5.fldLogDate, 108),'')) as TimeOut2
FROM LogInLogOut as LogInfo
  LEFT JOIN LogInLogOut as T2 ON (LogInfo.fldMachineId = T2.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T2.fldLogDate) AND T2.LogStatus = 1 AND DatePart(Hour, T2.fldLogDate) < 11)
  LEFT JOIN LogInLogOut as T3 ON (LogInfo.fldMachineId = T3.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T3.fldLogDate) AND T3.LogStatus = 2 AND DatePart(Hour, T3.fldLogDate) < 15)
  LEFT JOIN LogInLogOut as T4 ON (LogInfo.fldMachineId = T4.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T4.fldLogDate) AND T4.LogStatus = 1 AND DatePart(Hour, T4.fldLogDate) >= 11)
  LEFT JOIN LogInLogOut as T5 ON (LogInfo.fldMachineId = T5.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T5.fldLogDate) AND T5.LogStatus = 2 AND DatePart(Hour, T5.fldLogDate) >= 15)
Group By LogInfo.fldMachineId, 
         Year(LogInfo.fldLogDate), Month(LogInfo.fldLogDate), Day(LogInfo.fldLogDate) 
Another approach that I was thinking of will be using the partitioning logic of T-SQL. :-)

0 comments:

Post a Comment