Solved

Please help me with my update

Posted on 2013-01-24
4
317 Views
Last Modified: 2013-01-25
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
0
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 38817065
Please try on test data only...

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
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38817714
Try this  code, it uses CTEs to add a row number to each set and then matches on those to assign punchouts to punchins.  It does have to assume that you will always have a punchin time for every punchout otherwise they can get off track.
;WITH PI AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY PI.EditedAtStoreID, PI.EmployeeID ORDER BY PI.PunchInDateTime) rn
FROM dbo.punchIn PI),
PO AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY PO.EditedAtStoreID, PO.EmployeeID ORDER BY PO.PunchOutDateTime) rn
FROM dbo.PunchOut PO)
UPDATE PI
SET PunchOutDateTime = PO.PunchOutDateTime
FROM PI 
LEFT OUTER JOIN PO ON PI.EditedAtStoreID = PO.EditedAtStoreID AND PI.EmployeeID = PO.EmployeeID AND PI.rn = PO.rn

SELECT * FROM dbo.punchIn PI

Open in new window

0
 

Author Closing Comment

by:lulu50
ID: 38818440
If there is better than "A" I would have gave you.

it's working great!!!!!!!!

Thank you for your help!!!!!!!!
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38819768
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question