June 19, 2003 at 12:40 pm
David Burrows or someone else, can I get your help on this, please?
I am designing an email program that sends email automatically to various individuals when an accident/incident involving an employee occurs.
The way this is supposed to work is, if an accident occurs between an employee driving company vehicle and a non employee, the employee is required to call our accident hotmail and provide info about who sh/e is, location of accident, nature of accident etc.
The individual that answers the call then inputs this information into the system and hits the submit button to submit it to the database.
As soon as this is submitted to the database, then an automatically email is sent to various individuals responsible for handling accidents.
Those individuals have 24 hours to respond when the info gets to their desks.
The first person that must respond is the supervisor - he has 24 hours to responde.
After he responds, he passes the form to the safety officer who has 24 hours to respond.
The safety officer responds and passes the form to deputy for final approval and submittal to risk management dept.
Essentially, from the time accident info is logged to the system, supervisor has 24 hours to respond, safety officer has 48hours and deputy has 72 hours to respond.
Before each one of these individuals respond date is due, the email program is supposed to send them reminders pretty similar to outlook.
I can come up with a code that will select records inserted into the accident table and insert them into the email table and then schedule jobs in sql server to send them email messages every 7 hours until action is taken by all concerned.
My question then is how do I come up with the logic that selects the accident records from accident table showing that supervisor has 24 hours to respond, safety officer has 48 hours to respond and deputy has 72 hours to respond?
Once I can do this, I have the email program to do the rest.
Hopefully, please let me know if further info/explanation is needed.
Thanks in advance
June 20, 2003 at 7:26 am
Need more info - can you show us the DDL from the accidents table?
Exactly what is it that you are trying to work out? Are you trying to calulate the times, or work out who is the supervisor etc., or what?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 20, 2003 at 7:41 am
>>Essentially, from the time accident info is logged to the system, supervisor has
>> 24 hours to respond, safety officer has 48hours and deputy has
>>72 hours to respond.
That's not the way I read what you said before -- are you saying that if the supervisor responds within 1 hour of recieving his report, the safety officer has 47 hours to deal with it?
Do the weekends make any difference of are your safety team available 24/7?
June 20, 2003 at 10:20 am
first stax68, there are only 2 tables involved here.
One table has the accident info.
Info such as the name of caller, name of employee involved in the accident if different from the caller, date and time of accident, location of accident, employee's supervisor, dept, deputy for that dept, email address - that's it.
the other table is used to insert info for emailing to these folks.
This part is fine.
To planet115, what I meant by this statement:
>>Essentially, from the time accident info is logged to the system, supervisor has
>> 24 hours to respond, safety officer has 48hours and deputy has
>>72 hours to respond.
is that the supervisor of the employee involved in accident has 24 hours to fill a form as soon as the email that my program will send reaches him.
Once he completes the form and forwards to safety officer, from the time the safety officer receives it, he has 24 hours to complete and forward to the deputy who in turn has 24 hours from receipt of email to forward it to risk management.
So if the supervisor receives the email and it takes him/her 1 hour to complete his/her form, it doesn't matter when the safety officer receives it. He/she still has 24 hours from the time he/she receives.
maximum time allowed for the each to complete the form from date of receipt is 24 hours.
Each will continue to receive an email until he/she completes his/her form.
They have a maximum of 72 hours between them but they can complete the form much earlier.
The intent from what I gathered in the meeting is that these individuals sit on these forms and do nothing until several days, sometimes weeks before any action is taken.
As a result, there are lots of fingerpointing.
This email program is intended to place blame where it is deserved if the concerned individual fails to do what he/she is supposed to do.
*******************
Do the weekends make any difference of are your safety team available 24/7?
**********************
Good question! Weekends don't count because they don't work weekends.
What matters to me quite frankly is to come up with the logic of coding this.
I have asked this question and their response is that they don't mind receiving several emails as long as they have not completed their forms and turned them in.
For example, if the form is passed along to the supervisor, say on Friday by 5pm, he/she has till Tuesday to respond since Saturday and Sunday don't county.
But according to them, the supervisor doesn't mind getting reminders begining from the day and time accident info is passed to him/her.
My problem right now is getting past this initial stage of sending emai.
As I indicated, I have written an email program that says if your order is processed by a certain date, you will receive an email and the way I did it was to select from orders table where orderdates are less than duedate. Everything that is less than duedate is selected from the others table and inserted into the notification table.
All I had to do here was write an email program that just picks up this info and sends email to the customer informaing them that their orders have missed the submission date.
So if you guys can help me with a logic that says an accident happened 1pm and you have been notified. You have x date to respond.
Then there is a criterium set in the accident table that lets my email program know to go their and pick certain info where condition is blah, blah, blah.
From this point, I will do the rest.
In the end, I will just use sql server scheduling utility to schedule an email that keeps notifying them until they fully comply.
Sorry for long note.
June 20, 2003 at 11:49 am
/*
Basically I have made a table (accident_info) of foll columns
id - Some Primary Key
Person_Name
Date_Time_of_Reporting - The TimeStamp when the accident was reported
Action_required - 1 Pending for Supervisor 2 Pending for Safety Officer 3 Pending for Deputy and 0 for None
Last_Action_Taken - TimeStamp which gets updated when any user carries out a specific action
I have not included all of your columns.
Also I have not figured out the weekend logic yet.
*/
/* Table desciption */
CREATE TABLE [accident_info] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Person_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Time_of_Reporting] [datetime] NULL CONSTRAINT [DF_accident_info_date_time_of_reporting] DEFAULT (getdate()),
[Action_required] [smallint] NULL CONSTRAINT [DF_accident_info_Action_required] DEFAULT (1),
[Last_Action_Taken] [datetime] NULL ,
CONSTRAINT [PK_accident_info] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'Some Primary Key', N'user', N'dbo', N'table', N'accident_info', N'column', N'id'
GO
exec sp_addextendedproperty N'MS_Description', N'The TimeStamp when the accident was reported', N'user', N'dbo', N'table', N'accident_info', N'column', N'date_time_of_reporting'
GO
exec sp_addextendedproperty N'MS_Description', N'1 for Supervisor 2 for Safety Officer 3 for Deputy and 0 for None', N'user', N'dbo', N'table', N'accident_info', N'column', N'Action_required'
GO
exec sp_addextendedproperty N'MS_Description', N'Gets updated when any user carries out a specific action', N'user', N'dbo', N'table', N'accident_info', N'column', N'Last_Action_Taken'
GO
/*Pending for Supervisor action required=1 but time is less than 7 hours*/
select DATEDIFF(Hour, date_Time_of_Reporting, getdate()),*
from Accident_Info
Where Action_Required=1 and DATEDIFF(Hour, date_Time_of_Reporting, getdate()) < 7
/*Pending for supervisor action required=1 but time is more than 7 hours*/
select DATEDIFF(Hour, date_Time_of_Reporting, getdate()),*
from Accident_Info
Where Action_Required=1 and DATEDIFF(Hour, date_Time_of_Reporting, getdate()) > 7
/*
assuming that when the Supervisor does some action on the form, the Last_Action_Taken column is updated with the
timestamp. The time for Safety Officer starts after that.
*/
/* Pending for the Safety Officer but time is less than 7 hours */
select DATEDIFF(Hour, date_Time_of_Reporting, getdate()),*
from Accident_Info
Where Action_Required=2 and DATEDIFF(Hour, Last_Action_Taken, getdate()) < 7
/* Pending for the Safety Officer but time is More than 7 hours */
select DATEDIFF(Hour, date_Time_of_Reporting, getdate()),*
from Accident_Info
Where Action_Required=2 and DATEDIFF(Hour, Last_Action_Taken, getdate()) > 7
/*
assuming that when the Safety Officer does some action on the form, the Last_Action_Taken column is updated with the
timestamp. The time for Deputy starts after that.
*/
/* Pending for the Deputy but time is less than 7 hours */
select DATEDIFF(Hour, date_Time_of_Reporting, getdate()),*
from Accident_Info
Where Action_Required=3 and DATEDIFF(Hour, Last_Action_Taken, getdate()) < 7
/* Pending for the Deputy but time is More than 7 hours */
select DATEDIFF(Hour, date_Time_of_Reporting, getdate()),*
from Accident_Info
Where Action_Required=3 and DATEDIFF(Hour, Last_Action_Taken, getdate()) > 7
June 20, 2003 at 1:52 pm
hi Mandard,
thanks for your input on this.
I am still trying to sort out what you have done here.
I mean, I understand what the datediff function is doing but I still don't understand how they all tie together.
Remember, the supervisor needs to be advised that he/she has 24 hours to complete a form, safety officer has 24 from the time he/she receives it from supervisor and deputy has 24 from the time he/she receives it from the safety officer.
So are you saying that I should have multiple select statements one where last_action_taken, getdate() is <7 days and one where it is > 7 and where action_required is either 1, 2 or 3?
I am selecting this info from the accidentInfo table into the email table, is that correct?
Please help clarify these for me.
Thanks,
June 21, 2003 at 1:46 am
Hi Simflex,
I have the multiple select statements which I have posted, you can easily club them via union if you want. So you get one select statement to put into the email table.
The select statements have DATEDIFF function which is getting the hours elapsed since the accident info was posted/ updated.
Let me explain in detail.
When a accident is reported, the Date_Time_of_Reporting will have the current system date and time.
Also the Action_required column will be set to 1(action required by Supervisor). When the Supervisor takes some action on the form, the column called Last_Action_Taken will be updated with the current system time and Action_required = 2.
Now for the Safety Officer, we take into consideration the time from which the supervisor has taken the action. (Am I right?) Also for the Safety Officer, the Action_required will be 2.
When the Safety Officer takes action on the same, the action required will be updated to 3 and the Last_Action_Taken to the time when the safety officer took the action.
Now the Deputy will come into picture. After the Deputy takes the action on the form, we update Action_required to 0 (no action required) and optionally update the Last_Action_Taken column to the current system date/time.
June 21, 2003 at 5:51 am
hi mandard!
I understand what you are saying and it makes a great deal of sense to me.
The logic appears to be fine with me.
But I have a problem.
if last_action taken is set to current system date and action_required is set to 2, how do I instruct my email program to send an email to the next person?
Just look at this code:
select from accidentInfo where action_required = 1 and last_action_taken is null.
This will be selected and inserted into the email table.
My email program will then send mass email to all 3 letting them know that an accident occurred and they need to take action.
So the supervisor (action_required = 1) takes action and updates emailt table(?).
How does the email know to pick this up and send to the next person.
I know I can set up jobs to notify them but I think only those who are required to take action need to be notified.
Please help out here with some logic and syntax if possible.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply