I have a stored procedure in which I don't want to use table which is inward_doc_tracking_trl and its related aliases.
This is the code:
ALTER PROCEDURE [dbo].[GET_INWARD_REMINDER_REPORT]
AS
BEGIN
Select distinct
U.first_name + ' ' + U.last_name UserName,
TH.User_ID, TH.doc_no, TH.U_datetime,
CASE
WHEN TL.U_datetime < DATEADD(d, -5, GETDATE())
THEN M.Reporting_To
ELSE NULL
END Reporting_1 ,
--U.Email AS UserEmail,
--senior.Email AS RA1_Email,
10000 AS UserEmail,
100001 AS RA1_Email,
DATEDIFF(DAY,TH.LastAction_DateTime,GETDATE()) - DATEDIFF(WK,TH.LastAction_DateTime,GETDATE())AS No_Of_Days_Opening
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
outer apply
(select
uss.Email
from
emp_mst MS
inner join
user_mst uss ON uss.employee_mkey = MS.mkey
where
MS.mkey = M.Reporting_To) senior
where
TH.Status_flag NOT IN (5,14)
and TH.To_user IS NOT NULL
END
Aucun commentaire:
Enregistrer un commentaire