SQL Server Create stored procedure for setting reminder for document created

o4hqfura 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(104)

I want to create a stored procedure for the documents whose NStatusFlag should not be equal to 5 and 14

And also it should send Reminder to the respective user if the Document is with them for more than 3 days and if it is for more than 5 days then it should send it to its senior.

The date should be calculated with the U_datetime of the table and with the SYSTEM date from the system.

My NStatusFlag suggest which all documents are opened.

I tried below:

ALTER PROCEDURE GET_INWARD_REMINDER_REPORT
AS
BEGIN
    Select * from inward_doc_tracking_trl 
        where NStatus_flag <> 5
        and NStatus_flag <> 14
END
GO

So I got all the documents which are opened, like below

But I am stuck how to get the all user by comparing with the date and send the reminders.

Also, I will get the Name of the USer with which the document is assigned from the inward_doc_tracking_hdr table.

NOTE

inward_doc_tracking_hdr table mkey is related to ref_mkey from the inward_doc_tracking_trl table.

UPDATE

I get all the User from this query

ALTER PROCEDURE GET_INWARD_REMINDER_REPORT
AS
BEGIN
    
    Select distinct a.mkey, b.mkey, a.first_name + ' ' + a.last_name from user_mst a
    inner join inward_doc_tracking_hdr b 
    on a.mkey = b.User_Id
END
GO

UPDATE to get Senior name

select * from inward_doc_tracking_hdr order by mkey desc  -- here I get To_User
  select * from user_mst where mkey = 187         -- here I get To_User
 select Reporting_To,* from emp_mst where mkey = 122 -- here I get Senior

sql-server

来源：https://stackoverflow.com/questions/38069482/create-stored-procedure-for-setting-reminder-for-document-created

1条答案

按热度按时间

soat7uwm1#

This query gets the tracking lines of people with documents for 3 days or more (you might need to be careful with the time component). Please test and see if this gets what you want.

Select TL.*
    from inward_doc_tracking_trl TL
    where TL.NStatus_flag NOT IN (5,14)
    and TL.U_datetime <= DATEADD(d, -3, GETDATE())

This query then works out if its 3 or 5 days overdue and populates a second column if it's 3 days overdue:

Select 
    TL.*, 
    U.UserName,
    CASE 
    WHEN TL.U_datetime <= DATEADD(d, -5, GETDATE()) 
    THEN M.Reporting_To
    ELSE NULL 
    END SeniorName
    from inward_doc_tracking_trl TL
    INNER JOIN inward_doc_tracking_hdr TH
    ON TH.mkey = TL.ref_mkey
    INNER JOIN user_mst U
    ON TH.User_Id = U.mkey
    INNER JOIN emp_mst M
    ON M.mkey = U.employee_mkey
    where TL.NStatus_flag NOT IN (5,14)
    and TL.U_datetime <= DATEADD(d, -3, GETDATE())

You need to run that every day, I suggest inside a scheduled SQL Agent Job

With regards to 'sending a reminder', then assuming this is email, I suggest you first set up SQL Mail: https://msdn.microsoft.com/en-au/library/hh245116.aspx

Then use sp_send_dbmailhttps://msdn.microsoft.com/en-au/library/ms190307.aspx to send the email message

You'll also need to use the above query in a cursor and feed that into your sp_send_dbmailHow to send multiple emails from sql server database using sp_send_dbmail procedure

Have a crack at that and ask more questions if you like

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Create stored procedure for setting reminder for document created

1条答案

相关问题

热门标签

最新问答