auto trigger insert null values with random min wit

Question

User-807418713 posted

Hello

This is my data store in sql

NAME	IN TIME	OUT TIME	STATUS
AAA	09:09	20:25	Present
BBB	09:15	18:55	Present
VVV			Absent
DDD	09:56	18:33	Present
EEE	09:44	20:25	Present
SSS	10:04		Present
YYY			Present

now i want insert trigger or insert query that automtically fill Present In Time as 09: with random min same if OutTime is empty with Present it should take hours as 19: min will take random within 20 minutes

NAME	IN TIME	OUT TIME	STATUS
AAA	09:09	20:25	Present
BBB	09:15	18:55	Present
VVV			Absent
DDD	09:56	18:33	Present
EEE	09:44	20:25	Present
SSS	10:04	19:05	Present
YYY	9:17	19:10	Present

How to do so random number add

Answer 1

User475983607 posted

Your requirement for using a random number is difficult to understand.  What is the actual requirement?  Check if there is an IN TIME?

IF OBJECT_ID('tempdb..#Timesheet') IS NOT NULL
	DROP TABLE #Timesheet

CREATE TABLE #Timesheet (
	[NAME]	VARCHAR(10),
	InTime	TIME(2)		NULL,
	OutTime	TIME(2)		NULL,
)

INSERT INTO #Timesheet ([NAME], InTime, OutTime)
VALUES('AAA', '09:09', '20:25'),
	('BBB', '09:15', '18:55'),
	('YYY', NULL, NULL)

SELECT [NAME], 
	InTime AS [IN TIME], 
	OutTime AS [OUT TIME],
	CASE WHEN InTime IS NULL THEN 'Absent' ELSE 'Present' END AS [STATUS]
FROM #Timesheet

In you r future post, please provide sample code like above so we have a better understanding of your design. 

Answer 2

User-1716253493 posted

Not sure about your requirement, seem like you want do like this

Insert into yourtable (name,intime,outtime) values (@name, Isnull(@intime, '09:00'), isnull(@outtime, '19:00'))

Manipulate by yourself to get 09:min using datepart() and getdate() function