update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-28 : 01:02:47

this update query works fine but I only need to update employee records for those who are non-exempt (meaning these emp has an entry in the clocking_details table, and i can see the emp_id when do a group by emp_id).

update clocking_status
set clocking_status=
(
SELECT min(CASE WHEN (cd.time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus
FROM dbo.CLOCKING_DETAILS AS cd
where (cd.emp_id=clocking_status.emp_id)
GROUP BY cd.emp_id
),
clocking_date =
(
SELECT max(cd.clocking_date)
FROM dbo.CLOCKING_DETAILS AS cd
where (cd.emp_id=clocking_status.emp_id) -- REFERING TO THIS LINE
GROUP BY cd.emp_id
)

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-05-28 : 05:27:33

quote:
Originally posted by doran_doran

this update query works fine but I only need to update employee records for those who are non-exempt (meaning these emp has an entry in the clocking_details table, and i can see the emp_id when do a group by emp_id).

update clocking_status
set clocking_status=
(
SELECT min(CASE WHEN (cd.time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus
FROM dbo.CLOCKING_DETAILS AS cd
where (cd.emp_id=clocking_status.emp_id)
GROUP BY cd.emp_id
),
clocking_date =
(
SELECT max(cd.clocking_date)
FROM dbo.CLOCKING_DETAILS AS cd
where (cd.emp_id=clocking_status.emp_id) -- REFERING TO THIS LINE
GROUP BY cd.emp_id
)

Can you explain more about the details of your problem and post some test data? I tried to run your update statement and it seems to update the correct records and informations.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 12:10:21

seems like this:-


update cs
set cs.clocking_status=cd.cStatus,
cs.clocking_date = cd.clocking_date 
FROM clocking_status cs
INNER JOIN
(
SELECT emp_id,min(CASE WHEN (time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus,max(clocking_date) AS clocking_date 
FROM dbo.CLOCKING_DETAILS AS 
GROUP BY emp_id
)cd
on cd.emp_id=cs.emp_id

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-28 : 22:19:30

Thanks visakh for your help. This worked (except just a very minor twick). Thanks a lot.

=== update sql here ===

update cs
set cs.clocking_status=cd.cStatus,
cs.clocking_date = cd.clocking_date
FROM clocking_status cs
INNER JOIN
(
SELECT emp_id,min(CASE WHEN (time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus,max(clocking_date) AS clocking_date
FROM CLOCKING_DETAILS
GROUP BY emp_id
) as cd
on cd.emp_id=cs.emp_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources