CODEWITHSUNDEEP
sql-server
Kanjula Kumara
Kanjula Kumara

Reputation: 21

Datepart Function with If condition

I wrote this query to insert data to Att_process Table checkin_time field.

If the CHECKINOUT Table CHECKTIME Field Hours Value < 12, it should insert in to CHECKINOUT Table checkin_time Field. And if it is greater than 12 it should insert in to CHECKINOUT Table checkout_time Field

But it doesn't work, can you help me to figure this out and also i don't know how to put this on a trigger, if you can help me, I will really appreciate it. 

IF (DATEPART(HOUR,CHECKTIME) < 12) 
BEGIN
    INSERT INTO Att_process(USERID,checkin_time)
    SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKTIME
    from CHECKINOUT 
    where DATEPART(HOUR,CHECKTIME) < 12; 
END
ELSE
BEGIN
    INSERT INTO Att_process(USERID,checkout_time)
    SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKTIME
    from CHECKINOUT 
    where DATEPART(HOUR,CHECKTIME) >= 12;
END

Upvotes: 0

Views: 1554

Answers (2)

Sanu Antony
Sanu Antony

Reputation: 364

U can use this trigger just change the script accordingly

CREATE TRIGGER trgAfterInsert ON [dbo].[CHECKINOUT]
FOR INSERT
AS
     DECLARE @uid INT;
     DECLARE @checkin DATETIME;
     SELECT @uid = i.USERID
     FROM [CHECKINOUT] i;
     SELECT @checkin = i.CHECKTIME
     FROM [CHECKINOUT] i;
     IF(DATEPART(HOUR, @checkin) < 12)
         BEGIN
             INSERT INTO Att_process
                                    (USERID, checkin_time
                                    )
             VALUES
                    (@uid, @checkin
                    );
         END;
     ELSE
         BEGIN
             INSERT INTO Att_process
                                    (USERID, checkout_time
                                    )
             VALUES
                    (@uid, @checkin
                    );
         END;

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Plz try this 

INSERT INTO Att_process(USERID,checkin_time,checkout_time)
SELECT USERID, 
    (CASE WHEN DATEPART(HOUR,CHECKTIME) < 12 THEN CHECKTIME END),
    (CASE WHEN DATEPART(HOUR,CHECKTIME) >= 12 THEN CHECKTIME END)
FROM CHECKINOUT

Upvotes: 1

Related Questions