locked
employee reminders RRS feed

  • Question

  • i have a table that contains a list of work related reminders with an issue date and a due date (an employee has 10days to read a reminder).a second table that has employee information.is there a query to list:

    • the reminders an employee has not read 
    • list users that have read the reminders after the due date

    i know i'd have to create a third table but i don't know what info to put in it or what relationship to form between the existing tables.

    a detail explanation would be more than helpful or pseudo code is fine

    Friday, June 23, 2017 10:31 PM

Answers

  • I would do something like this:

    use tempdb
    go
    
    i have a table that contains a list of work related reminders with an issue date and a due date
     (an employee has 10days to read a reminder).
     a second table that has employee information.is there a query to list:
    the reminders an employee has not read  
    list users that have read the reminders after the due date
    i know i'd have to create a third table but i don't know what info to put in it or what relationship 
    to form between the existing tables.
    a detail explanation would be more than helpful or pseudo code is fine
    
    create table Employees(EmployeeID int constraint EmployeesPK primary key, FirstName varchar(20), LastName varchar(20))
    GO
    insert into Employees(EmployeeID,FirstName,  LastName) values(1,'test1','test1')
    insert into Employees(EmployeeID,FirstName,  LastName) values(2,'test2','test2')
    insert into Employees(EmployeeID,FirstName,  LastName) values(2,'test3','test3')
    GO
    
    create table Reminders(ReminderID int constraint RemindersPK primary key,
    EmployeeID int constraint FK_EmployeeID references Employees(EmployeeId), IssueDate date,
    DueDate date, ResponseDate datetime, ReminderIssued bit)
    GO
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(1, 1, '2017-06-20','2017-06-30')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(2, 1, '2017-06-20','2017-06-30', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(3, 1, '2017-06-20','2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(4, 1, '2017-06-20','2017-06-25', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate, ReminderIssued) values(5, 1, '2017-06-20','2017-06-25',1, 1)
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ReminderIssued) values(6, 1, '2017-06-20','2017-06-25', 1)
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(7, 1, '2017-06-10','2017-06-30')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(8, 1, '2017-06-10','2017-06-30', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(9, 1, '2017-06-10','2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(10, 1, '2017-06-10','2017-06-25', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate, ReminderIssued) values(11, 1, '2017-06-10','2017-06-25',1, 1)
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ReminderIssued) values(12, 1, '2017-06-10','2017-06-25', 1)
    
    GO
    --employees who have not responded within 10 days and no reminder issued
    select * from Reminders where DueDate>getdate()
    and datediff(dd, issuedate, getdate())>10 and ResponseDate is null
    and ReminderIssued is null
    GO
    --employees who have not responded within 10 days and reminder issued
    select * from Reminders where DueDate>getdate()
    and datediff(dd, issuedate, getdate())>10 
    and ResponseDate is null
    and ReminderIssued is not null
    
    
    

    • Marked as answer by audeamus Monday, June 26, 2017 11:20 AM
    Saturday, June 24, 2017 1:45 PM

All replies

  • We have something similar to what you're trying to put together, and it's really pretty simple.  To make it comparable, tables involved are:

    • Employee
    • Reminder
    • EmployeeReminder

    All tables have auto increment identity columns as PK's.  The EmployeeReminder table would also have FK's to Employee, and Reminder.

    Employee table is information relevant to employees, such as Active Directory GUID, email address, department, hire and term dates, First and Last names, etc.  

    The Reminder table would contain pertinent information for the reminder, plus the HTML that's used for the emails.

    EmployeeReminder contains data relevant to a specific employee and a specific reminder - information like when the first email was sent, and follow up emails, and when we receive a confirmation that the notice was read.  We populate this table with the correct employee/reminder matches, then send out daily emails based on any listing that doesn't have a timestamp for first notification and hasn't been timestamped with a response date.  We have secondary/tertiary processes that run every 1-3 weeks to send follow up emails if no response was received after the initial notice was sent.


    Saturday, June 24, 2017 2:17 AM
  • I would do something like this:

    use tempdb
    go
    
    i have a table that contains a list of work related reminders with an issue date and a due date
     (an employee has 10days to read a reminder).
     a second table that has employee information.is there a query to list:
    the reminders an employee has not read  
    list users that have read the reminders after the due date
    i know i'd have to create a third table but i don't know what info to put in it or what relationship 
    to form between the existing tables.
    a detail explanation would be more than helpful or pseudo code is fine
    
    create table Employees(EmployeeID int constraint EmployeesPK primary key, FirstName varchar(20), LastName varchar(20))
    GO
    insert into Employees(EmployeeID,FirstName,  LastName) values(1,'test1','test1')
    insert into Employees(EmployeeID,FirstName,  LastName) values(2,'test2','test2')
    insert into Employees(EmployeeID,FirstName,  LastName) values(2,'test3','test3')
    GO
    
    create table Reminders(ReminderID int constraint RemindersPK primary key,
    EmployeeID int constraint FK_EmployeeID references Employees(EmployeeId), IssueDate date,
    DueDate date, ResponseDate datetime, ReminderIssued bit)
    GO
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(1, 1, '2017-06-20','2017-06-30')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(2, 1, '2017-06-20','2017-06-30', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(3, 1, '2017-06-20','2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(4, 1, '2017-06-20','2017-06-25', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate, ReminderIssued) values(5, 1, '2017-06-20','2017-06-25',1, 1)
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ReminderIssued) values(6, 1, '2017-06-20','2017-06-25', 1)
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(7, 1, '2017-06-10','2017-06-30')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(8, 1, '2017-06-10','2017-06-30', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate) values(9, 1, '2017-06-10','2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate) values(10, 1, '2017-06-10','2017-06-25', '2017-06-25')
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ResponseDate, ReminderIssued) values(11, 1, '2017-06-10','2017-06-25',1, 1)
    insert into Reminders(ReminderID, EmployeeID, IssueDate, DueDate,ReminderIssued) values(12, 1, '2017-06-10','2017-06-25', 1)
    
    GO
    --employees who have not responded within 10 days and no reminder issued
    select * from Reminders where DueDate>getdate()
    and datediff(dd, issuedate, getdate())>10 and ResponseDate is null
    and ReminderIssued is null
    GO
    --employees who have not responded within 10 days and reminder issued
    select * from Reminders where DueDate>getdate()
    and datediff(dd, issuedate, getdate())>10 
    and ResponseDate is null
    and ReminderIssued is not null
    
    
    

    • Marked as answer by audeamus Monday, June 26, 2017 11:20 AM
    Saturday, June 24, 2017 1:45 PM