Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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
0
lulu50
Asked:
lulu50
  • 2
1 Solution
 
Tony303Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
lulu50Author Commented:
If there is better than "A" I would have gave you.

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

Thank you for your help!!!!!!!!
0
 
Chris LuttrellSenior Database ArchitectCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now