This was just covered the other day, in "Off day " thread
I thought this was essentially a different issue than in "Off day" thread.
Originally Posted by Wim
Care to share how you go from the Input to the Output? Or were you planning to leave that as an exercise to us?
Can you also give us the CREATE TABLE script and INSERT scripts to create and populate that table? Perhaps even other related tables (just the columns that are needed to solve this problem).
We don't mind helping, but you got to show you want to invest some work in it too.
I agree with the opinion.
Please give us the CREATE TABLE script and INSERT scripts to create and populate that table.
By the way,
I guessed a way to solve the issue might be ...
(1) number rows in the table(e.g. call it r_num) by using ROW_NUMBER() funcion in a common-table-expression.
(2) (Self) FULL OUTER JOIN the common-table-expression like ...
FROM cte t1
FULL OUTER JOIN
ON t2.r_num = t1.r_num + 1
AND t2.AltCategoryID = 3
AND t1.AltCategoryID = 1
numbered_table AS (
OVER( ORDER BY AttDateTime ) AS r_num
OVER( ORDER BY COALESCE(i.AttDateTime , o.AttDateTime) ) AS rowNo
, COALESCE(i.MachineID , o.MachineID ) AS MachineID
, CAST(COALESCE(i.AttDateTime , o.AttDateTime) AS DATE) AS tDate
, COALESCE(i.EmployeeID , o.EmployeeID ) AS EmployeeID
, i.AttCategoryID AS InType
, i.AttRecordID AS InRecordID
, i.AttDateTime AS InDateTime
, o.AttCategoryID AS OutType
, o.AttRecordID AS OutRecordID
, o.AttDateTime AS OutDateTime
FROM (SELECT *
WHERE AttCategoryID = 1
) AS i
FULL OUTER JOIN
WHERE AttCategoryID = 3
) AS o
ON o.MachineID = i.MachineID
AND o.EmployeeID = i.EmployeeID
AND o.r_num = i.r_num + 1
Last edited by tonkuma; 07-09-13 at 10:40.
Reason: Replace FULL OUTER JOINed tables with subqueries.