Solved

Please help me with my update

Posted on 2013-01-24
4
316 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 26

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 26

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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