Solved

Please help me with my update

Posted on 2013-01-24
4
314 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.​
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

778 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