September 10, 2003 at 9:48 am
Hello all,
Please take a look at the code below.
This is the actual code I am using for an email program.
The way this code works currently is it goes into the tables listed in this query,
retrieves records where completed is 0, stores this(these) record(s) into another table and then
an email program picks these records and sends email notifications to those involved.
Those involved are:
emp, his supervisor, and the supervisor's director (3).
When the email is retrieved from the tables mentioned above, it shows this graphic:
assume an event happens today (9/10/2003),
the employee has 24 hours from today to take action, his/her supervisor has 48 hours from the date of the event to take action and the director has 72 hours from the date of event to take action.
This code works fine as it is right now.
However, there is a little problem.
The way the program should work is that the below code should only retrieve event where no action has been taken by the first person(employee) (completed=0) and then a reminder is sent to the employee.
Right now, it picks everything where completed = 0.
I need to find a way to instruct the code not to pick anything that is beyond 24 hours.
I have tried where completed = 0 and CAST(DateAdd(d,1,DateOfAccident) as VARCHAR) <=24
This not working.
Below is code.
SELECT theEmp.fname+' '+theEmp.lname as fullName, tblEmployeeType.Type,
tblEmployAccident.TrackingNumber, theEmp.Email,tblEmployAccident.Completed,
tblAccidentInfo.staffMem,TimeOfAccident,DateOfAccident
FROM tblEmployeeType, theEmp, tblEmployAccident,tblAccidentInfo
WHERE tblEmployeeType.TypeID = theEmp.TypeID AND
theEmp.empID = tblEmployAccident.empID
AND tblAccidentInfo.TrackingNumber = tblEmployAccident.TrackingNumber
AND tblEmployAccident.Completed = 0
ORDER BY tblEmployeeType.TypeID DESC
September 10, 2003 at 9:57 am
Don't you want
getdate() < dateadd(d,1,DateofAction)
?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 10, 2003 at 10:35 am
This won't work because the email program is supposed to pick up records that have missed deadline.
Example, if an action is not taken with regards to a particular event within 24 hours of that event occuring, and the flag called completed is still set to 0 this means that the individual concerned is going to get an email.
I am just concerned that if the is sent to everyone, when it should have been sent to the first person, they may start to get irritated.
The only time they are allowed to get irritated is if their 24 hours comes up and they start to get email reminders that their action is late, and they realize that they are getting these reminders because of someone else not doing what they are supposed to do, they will get mad at the person.
That was one of their specs.
Until that happens, I will like the first reminder be sent only to the first persone concerned.
September 10, 2003 at 11:52 pm
Have a look at the CASE statement and maybe move it to the where clause.
SELECT theEmp.fname+' '+theEmp.lname as fullName, tblEmployeeType.Type,
tblEmployAccident.TrackingNumber, theEmp.Email,tblEmployAccident.Completed,
tblAccidentInfo.staffMem,TimeOfAccident,DateOfAccident,
Case DateDiff(hh,DateOfAccident,GetDate()) Between 0 and 24 Then 'Employee' Else '' End,
Case DateDiff(hh,DateOfAccident,GetDate()) Between 24 and 48 Then 'Supervisor' Else '' End,
Case DateDiff(hh,DateOfAccident,GetDate()) Between 48 and 72 Then 'Director' Else '' End
FROM tblEmployeeType, theEmp, tblEmployAccident,tblAccidentInfo
WHERE tblEmployeeType.TypeID = theEmp.TypeID AND
theEmp.empID = tblEmployAccident.empID
AND tblAccidentInfo.TrackingNumber = tblEmployAccident.TrackingNumber
AND tblEmployAccident.Completed = 0
ORDER BY tblEmployeeType.TypeID DESC
How does the query knows who the supervisor, directory, through the type?
September 11, 2003 at 7:05 am
Yes, the query knows supervisor, employee and director through type.
when records are pulled into the notification table for emailing, it breaks type type up into employee, supervisor and director and displays.
Just for idiots like me, will case statement retrieve records to be sent to individuals based not just on complete = 0 but also the first person misses the 24 hour deadline, not all of them at the same time?
September 11, 2003 at 3:59 pm
SELECT theEmp.fname+' '+theEmp.lname as fullName, tblEmployeeType.Type,
tblEmployAccident.TrackingNumber, theEmp.Email,tblEmployAccident.Completed,
tblAccidentInfo.staffMem,TimeOfAccident,DateOfAccident,
Case When DateDiff(hh,DateOfAccident,GetDate()) Between 0 and 24 Then 'Employee' Else '' End,
Case When DateDiff(hh,DateOfAccident,GetDate()) Between 24 and 48 Then 'Supervisor' Else '' End,
Case When DateDiff(hh,DateOfAccident,GetDate()) Between 48 and 72 Then 'Director' Else '' End
FROM tblEmployeeType, theEmp, tblEmployAccident,tblAccidentInfo
WHERE tblEmployeeType.TypeID = theEmp.TypeID AND
theEmp.empID = tblEmployAccident.empID
AND tblAccidentInfo.TrackingNumber = tblEmployAccident.TrackingNumber
AND tblEmployAccident.Completed = 0 And
(Case When DateDiff(hh,DateOfAccident,GetDate()) Between 0 and 24 Then 1
When DateDiff(hh,DateOfAccident,GetDate()) Between 24 and 48 Then 1
When DateDiff(hh,DateOfAccident,GetDate()) Between 48 and 72 Then 1
Else 0 End)=1
ORDER BY tblEmployeeType.TypeID DESC
-- Warning BETWEEN is inclusive
-- If Getdate() is exactly 24 hours diff from DateOfAccident employee
-- and supervisor will be selected. Same with director
-- Not the most optimized SELECT but workable for a start
September 12, 2003 at 2:32 pm
thank you very much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply