lulu50
asked on
Please help me with my update
Hi,
I have two data result one for PunchInDate and the other is for PunchOutDate
what I want is to add the PunchOut data to the PunchIn table. (Merge them together)
PunchOut Table
EditedAtStoreID EmployeeID PunchOutDateTime
9 13000505 10/10/2013
9 13000505 10/11/2013
9 13000505 10/12/2013
9 13000505 10/13/2013
9 6 10/14/2013
9 6000077 10/14/2013
9 13000226 10/16/2013
9 10000000 10/17/2013
punchIn Table
EditedAtStoreID EmployeeID PunchInDateTime PunchOutDateTime
9 13000505 10/10/2013 Null
9 13000505 10/11/2013 Null
9 13000505 10/12/2013 Null
9 13000505 10/13/2013 Null
9 13000504 10/13/2013 Null
9 6 10/14/2013 Null
9 6000077 10/14/2013 Null
9 6000077 10/15/2013 Null
9 13000226 10/16/2013 Null
9 13000508 10/17/2013 Null
9 6000247 10/18/2013 Null
9 10000000 10/17/2013 Null
what I want is to populate the PunchIn table (PunchOutDateTime) from the PunchOut table.
I want the output to look something like this.
EditedAtStoreID EmployeeID PunchInDateTime PunchOutDateTime
9 13000505 10/10/2013 10/10/2013
9 13000505 10/11/2013 10/11/2013
9 13000505 10/12/2013 10/12/2013
9 13000505 10/13/2013 10/13/2013
9 13000504 10/13/2013 Null
9 6 10/14/2013 10/14/2013
9 6000077 10/14/2013 10/14/2013
9 6000077 10/15/2013 Null
9 13000226 10/16/2013 10/16/2013
9 13000508 10/17/2013 Null
9 6000247 10/18/2013 Null
9 10000000 10/17/2013 10/17/2013
I have two data result one for PunchInDate and the other is for PunchOutDate
what I want is to add the PunchOut data to the PunchIn table. (Merge them together)
PunchOut Table
EditedAtStoreID EmployeeID PunchOutDateTime
9 13000505 10/10/2013
9 13000505 10/11/2013
9 13000505 10/12/2013
9 13000505 10/13/2013
9 6 10/14/2013
9 6000077 10/14/2013
9 13000226 10/16/2013
9 10000000 10/17/2013
punchIn Table
EditedAtStoreID EmployeeID PunchInDateTime PunchOutDateTime
9 13000505 10/10/2013 Null
9 13000505 10/11/2013 Null
9 13000505 10/12/2013 Null
9 13000505 10/13/2013 Null
9 13000504 10/13/2013 Null
9 6 10/14/2013 Null
9 6000077 10/14/2013 Null
9 6000077 10/15/2013 Null
9 13000226 10/16/2013 Null
9 13000508 10/17/2013 Null
9 6000247 10/18/2013 Null
9 10000000 10/17/2013 Null
what I want is to populate the PunchIn table (PunchOutDateTime) from the PunchOut table.
I want the output to look something like this.
EditedAtStoreID EmployeeID PunchInDateTime PunchOutDateTime
9 13000505 10/10/2013 10/10/2013
9 13000505 10/11/2013 10/11/2013
9 13000505 10/12/2013 10/12/2013
9 13000505 10/13/2013 10/13/2013
9 13000504 10/13/2013 Null
9 6 10/14/2013 10/14/2013
9 6000077 10/14/2013 10/14/2013
9 6000077 10/15/2013 Null
9 13000226 10/16/2013 10/16/2013
9 13000508 10/17/2013 Null
9 6000247 10/18/2013 Null
9 10000000 10/17/2013 10/17/2013
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If there is better than "A" I would have gave you.
it's working great!!!!!!!!
Thank you for your help!!!!!!!!
it's working great!!!!!!!!
Thank you for your help!!!!!!!!
Glad to help and thank you so much for the feed back. Knowing I helped someone and they really appreciate it is what keeps me motivated.
UPDATE i
SET i.PunchOutDateTime = o.PunchOutDateTime
FROM PunchInTable i, PunchOutTable o
WHERE i.EditedAt = o.EditedAt AND i.StoreID = o.StoreID AND i.EmployeeID = o.EmployeeID
AND i.PunchOutDateTime IS NULL