mardi 12 juillet 2016

Remove one table and its alias from the stored procedure

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