jeudi 26 mai 2016

Get Max and Min of document no in one column in Stored procedure

I have a Stored procedure which fetches data according to the date paramters. here is a preview

Preview

Now I want to add one more column which gets the MAX and MIN of doc_no from the inward_doc_tracking_hdr table.

Below is the SP

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

            AS  
            BEGIN  
            CREATE TABLE #temp(  
            date datetime, Total int, 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, Inward, First_Level_Transfer,  
           Data_Entry_Transfer,   
           Second_Level_Transfer, Outward_Transfer,  
           Closed, Communication_Transfer, Returned)  
          SELECT  
           doc_date, COUNT(*),  
           SUM(  
           CASE  
            WHEN status_flag in ('6','23') THEN 1 ELSE 0  
           END),  
           SUM(  
           CASE  
              WHEN status_flag in ('4','26','24') 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  
          WHERE doc_date between @From_date and @To_date AND status_flag <> '6'  
          GROUP BY doc_date  

   END  

 Select * from #temp

How to get that MAX and MIN document with respective to the TOTAL column.

Aucun commentaire:

Enregistrer un commentaire