Consider this table:
pk uniqueidentifier
fk bigint
clock datetime
I need an SP that can do the following:
If there are zero records for that day, insert a record.
If there is one record for that day, insert a record.
If there are two or more records for that day:
- do nothing if the time falls between the min and max time for that day
- update the min time record if the passed clock is less than the min time for that day
- update the mmax time record if the passed clock is greater than the max time for that day
This is what I've got. but I am not sure how to find the correct record (if needed) to update
SELECT @count = count(*) FROM mytable
WHERE fk=@fk AND
YEAR(clock) = YEAR(@clock) AND
MONTH(clock) = MONTH(@clock) AND
DAY(clock) = DAY(@clock)
IF @count<2 BEGIN
// insert this is easy
END ELSE BEGIN
// find the correct @pk to update
END
Any help appreciated.
Semper ubi sub ubi.