update or insert based on data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

update or insert based on data?

Author

Topic

lloyddobler
Starting Member

4 Posts

Posted - 2008-08-05 : 09:54:08

Hello all, trying to get some help building a trigger (or sproc) that meets the following: If a row exists that is missing the TIMEIN value, then the TIMEIN value should be updated. If there are no missing TIMEIN values, or any values period, then a new record should be input with TIMEOUT value only. here's a sample of table:


Rec#     EMP#    timeOUT    timeIN
1         4         11:30      12:05
2         4         12:30       --
3         9         11:05      11:15
4         10        11:45      ---

Based on this table, if EMP# 4 scans, TimeIN should be update (record #2). If EMP 9 were to scan, a new record should be created with timeOUT only (and EMP# of course).

Any help with this is greatly appreciate. I've tried a trigger as follows:


CREATE TRIGGER timeclock ON [dbo].[tbl_timeclock] 
instead of  INSERT
AS
DECLARE @TimeIN DATETIME,
 	 @EE numeric(9,2),
	@ID numeric(9,2)

select @ID = TCid from inserted
select @EE = employeenumber from inserted
Select @TimeIN = TimeIn FROM tbl_timeclock WHERE timein is null and employeenumber = @EE
begin
 if exists (select employeenumber from tbl_timeclock where employeenumber = @EE) and  @TimeIN IS NULL 
UPDATE tbl_timeclock set timein = getdate()  WHERE employeenumber = @EE AND TCid = @ID
ELSE
     INSERT tbl_timeclock (employeenumber) values (@EE)
END

Hey my brother, can I borrow a copy of your "Hey Soul Classics"?
No, my brother, you have to go buy your own.

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-05 : 10:07:09

What do you mean by "to Scan"?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lloyddobler
Starting Member

4 Posts

Posted - 2008-08-05 : 10:38:58

"to scan", to punch out, clock out, sign out, etc.

Hey my brother, can I borrow a copy of your "Hey Soul Classics"?
No, my brother, you have to go buy your own.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:50:52

[code]CREATE PROCEDURE PuchTimeSave
@EmpID int,
@Time datetime
AS

IF EXISTS (SELECT 1 FROM tbl_timeclock WHERE timein is null and employeenumber = @EmpID)
BEGIN
UPDATE tbl_timeclock
SET timein=@Time
WHERE employeenumber = @EmpID
AND timein IS NULL
END
ELSE
BEGIN
INSERT INTO (EMP#,timeOUT)
SELECT @EmpID,@Time
END
GO[/code]

lloyddobler
Starting Member

4 Posts

Posted - 2008-08-05 : 14:08:12

Thanks Visakh16, actually, I came up with something similar to yours, found an example on some website. It's pretty much the same as your code, I just took away the @time variable and am having it = getdate(). Thanks for the valuable post.

Hey my brother, can I borrow a copy of your "Hey Soul Classics"?
No, my brother, you have to go buy your own.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 14:11:44

quote:
Originally posted by lloyddobler

Thanks Visakh16, actually, I came up with something similar to yours, found an example on some website. It's pretty much the same as your code, I just took away the @time variable and am having it = getdate(). Thanks for the valuable post.

Hey my brother, can I borrow a copy of your "Hey Soul Classics"?
No, my brother, you have to go buy your own.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources