Inserting Recurring Notes on Scheduled Dates

Question

Post reply

Inserting Recurring Notes on Scheduled Dates

Sheldon Penner-296948

SSC Enthusiast

Points: 128
More actions
October 23, 2022 at 8:56 am

#4103239
I am having a problem with a T-SQL stored procedure whose purpose is to insert recurring notes into the tblNotes table from the tblRecur table where the schedule specified in a record in tblRecur applies to a given date.
Here’s my code:
```
ALTER procedure [dbo].[uspAddRecurringNotesOnDate](
@OwnerID int,
@Dt datetime
)

AS

-- Get table of all @Owner's items in tblRecur scheduled to recur on @Dt
declare @recur table (
RecurID int 
)
insert @recur(RecurID)
execute uspRecurOnDate @OwnerID, @Dt

-- Get notes
insert tblNotes(OwnerID, NotesDate, NotesText, RecurID)
select R.OwnerID,
@Dt,
R.RecurNotes,
R.RecurID
from tblRecur R
inner join @recur r2
on r2.RecurID = R.RecurID
where not exists (
select NotesID from tblNotes N where N.RecurID = R.RecurID and dbo.udfDateOnly(N.NotesDate) = @Dt
)
```
The uspRecurOnDate procedure returns a list of RecurID’s from tblRecur, specifying the schedules that apply to the given date, @dt (e.g. “Last Tuesday of the month” or “Every April 15”). The list is saved in the single-column table @recur. The function udfDateOnly takes a datetime and returns the date with the time removed. I have tested both of these and I’m quite certain they return the correct values.
The error must reside in the second query, under “—Get notes”. The Where clause is supposed to prevent duplicate entries by filtering out RecurID’s that are already listed for the given @dt. This is not working, however. The new record is inserted whether or not there is an existing record in tblNotes with the same RecurID and NotesDate.
I have been going over this one ‘til I’m cross-eyed. Can anybody spot the error? Thank you in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Your approach is fundamentally flawed as you cannot select missing values from which the values are missing!

😎

The solution is simple, this example should get you over this hurdle:

USE TEEST;
GO
SET NOCOUNT ON;
GO

DECLARE @TDATE  DATE    = '20221025';
DECLARE @SCHEDULE TABLE
(
    SC_ID   INT     NOT NULL
   ,SC_DT   DATE    NOT NULL
);
INSERT INTO @SCHEDULE (SC_ID,SC_DT)
VALUES 
    (1,@TDATE)
   ,(2,@TDATE)
   ,(3,@TDATE)
   ,(5,@TDATE)
   ,(6,@TDATE)
   ,(10,@TDATE)
;
DECLARE @TID TABLE
(
    ID  INT NOT NULL
);
INSERT INTO @TID (ID)
VALUES
    (1)
   ,(2)
   ,(3)
   ,(4)
   ,(5)
   ,(6)
   ,(7)
   ,(8)
   ,(9)
   ,(10)
;
SELECT
    TID.ID
   ,@TDATE
FROM            @TID            TID
LEFT OUTER JOIN @SCHEDULE       SC
ON              TID.ID      =   SC.SC_ID
AND             SC.SC_DT    =   @TDATE
WHERE           SC.SC_ID    IS NULL;

It kind of goes without saying that there should be a unique constraint on the ID and DATE combination 😉

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

one of your possible issues is that @dt is a datetime - if this contains anything other than the date only your query will fail as you are comparing it to a date.

and to avoid using the function on the where clause (bad as it is not sargeable) you should consider creating 2 variables

@startdate date = convert(date, @dt)

@enddate date = dateadd(day, 1, convert(date, @dt))

and change query to be "where N.RecurID = R.RecurID and N.NotesDate >= @startdate and N.NotesDate < @enddate"