jeudi 9 juin 2016

Create Stored procedure with reference to old stored procedure

I have an OLD SP which is working fine as per the requirement. Here is what it looks like.

OLD SP

and the SP is below:-

ALTER PROCEDURE [dbo].[GET_RECORDS_FORDATE]      
                                @From_date Datetime,      
                                @To_date Datetime      

                                AS      
                                BEGIN      
                                CREATE TABLE #temp(      
                                date datetime, Total int,doc_From_To varchar(50),Inward int, First_Level_Transfer int,      
                                Data_Entry_Transfer int,       
                                Second_Level_Transfer int, Outward_Transfer int,      
                                Closed int, Communication_Transfer int, Returned int     
                            )      

                              INSERT INTO #temp      
                              (date, Total,doc_From_To, Inward, First_Level_Transfer,      
                               Data_Entry_Transfer,       
                               Second_Level_Transfer, Outward_Transfer,      
                               Closed, Communication_Transfer, Returned)      
                              SELECT      
                               doc_date, COUNT(*),      
                             (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj  
                             where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
                               + ' - '+  
                             (select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj  
                             where jj.doc_date =convert(datetime,aa.doc_date,103) )  )  
                               ,SUM(      
                               CASE      
                                WHEN status_flag in ('6','23') THEN 1 ELSE 0      
                               END)  
                               ,SUM(      
                               CASE      
                                  WHEN status_flag in ('4','26','24','19') THEN 1 ELSE 0      
                               END)  
                               ,SUM(      
                               CASE      
                                  WHEN status_flag in ('15','20') THEN 1 ELSE 0      
                               END),      
                               SUM(      
                               CASE      
                                  WHEN status_flag in ('17','21') THEN 1 ELSE 0      
                               END),      
                               SUM(      
                               CASE      
                                  WHEN status_flag in ('18','27') THEN 1 ELSE 0      
                               END),      
                               SUM(      
                               CASE      
                                  WHEN status_flag='5' THEN 1 ELSE 0      
                               END),      
                               SUM(      
                               CASE      
                                  WHEN status_flag='16' THEN 1 ELSE 0      
                               END),    
                               SUM(      
                               CASE      
                                  WHEN status_flag='14' THEN 1 ELSE 0      
                               END)    
                              FROM inward_doc_tracking_hdr  aa    
                              WHERE doc_date between @From_date and @To_date   
                            --AND status_flag <> '6'      
                              GROUP BY doc_date      

                            END      

            Select * from #temp

So, now what I want is, I want to create a another SP which will have following column as my output and only with one parameter

I am using SQL-server-2005 Please help

Aucun commentaire:

Enregistrer un commentaire