Solved

Please help me with my update

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now