Convert Procedure Day wise to Month wise Sum

Analyst_SQL 3,531 Reputation points
2020-10-10T12:28:28.777+00:00

Data again update

Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50));  
Create table #Bigbalprd (BID int,Codeitem int,Bpqty int,B_Weight int,EntryDate date);  
  
  
INSERT INTO #ItemMasterFile VALUES  
  (1,'A')  
, (2,'B')  
, (3,'C')  
, (4,'D')  
, (5,'e')  
, (6,'f')  
, (7,'g')  
, (8,'h')  
, (9,'K')  
, (10,'L')  
, (11,'M');  
  
  
INSERT INTO #Bigbalprd VALUES  
(111,1,1,500,'2020-03-06')  
,(112,2,1,200,'2020-03-06')  
,(113,1,1,300,'2020-03-06')  
,(114,6,1,100,'2020-04-07')  
,(115,1,1,200,'2020-04-07')  
,(116,1,1,300,'2020-08-15')  
,(117,7,1,100,'2020-08-20')  
,(118,5,1,200,'2020-05-09')  
,(119,8,1,300,'2020-06-09');  
  
Declare @startDate date ='03-03-2020'  
Declare @Enddate  date ='09-10-2020'  
  
  
 DECLARE @cols AS NVARCHAR(MAX)  
 DECLARE @query AS NVARCHAR(MAX)  
 DECLARE @cols1 AS NVARCHAR(MAX)  
 DECLARE @cols2 AS NVARCHAR(MAX)  
 DECLARE @cols3 AS NVARCHAR(MAX)  
 declare @sum nvarchar(max);  
 SELECT  @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(Bpqty)+')') from #Bigbalprd;  
  -- SET NOCOUNT ON added to prevent extra result sets from  
  -- interfering with SELECT statements.  
    
      
      
 SET NOCOUNT ON;  
 SELECT Distinct EntryDate INTO #Dates26 FROM #Bigbalprd WHERE EntryDate BETWEEN @StartDate AND @Enddate   
 ORDER BY EntryDate  
 --select * from #Dates26  
 SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate,120) )  
 FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
 ORDER BY EntryDate  
 SELECT @cols1 = COALESCE(@cols1 + ',','') +'isnull('+ QUOTENAME( CONVERT(varchar(10),EntryDate,120) )+',0) '+QUOTENAME( CONVERT(varchar(10),EntryDate,120) )  
 FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
 ORDER BY EntryDate  
 SELECT @cols2 = COALESCE(@cols2 + ',','') +'sum('+ QUOTENAME( CONVERT(varchar(10),EntryDate,120) )+') '+QUOTENAME( CONVERT(varchar(10),EntryDate,120) )  
 FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
 ORDER BY EntryDate  
 SELECT @cols3 = COALESCE(@cols3 + '+','') + 'isnull('+ QUOTENAME( CONVERT(varchar(10),EntryDate,120) )+',0) '  
 FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
 ORDER BY EntryDate  
      
 --SET @cols =  STUFF(@cols, 1, 1, '')   
      
      
 SET @query =  
 N';with cte as (  
 SELECT Descriptionitem,'+@cols1+','+@cols3+' Total  
 FROM (SELECT Descriptionitem,#Bigbalprd.Bpqty,  
 #Bigbalprd.EntryDate   
 FROM #Bigbalprd left JOIN #ItemMasterFile on #ItemMasterFile.Codeitem=#Bigbalprd.Codeitem  
 where  #Bigbalprd.EntryDate BETWEEN '''+convert(varchar(10),@StartDate,120)+''' AND '''+convert(varchar(10),@Enddate,120)+''' )prd   
 PIVOT (Sum(bpqty) FOR EntryDate IN (' + @cols + ')) AS stat)  
 select * from cte   
 union all   
 select ''Total'','+@cols2+',sum(Total)   
 from cte '  
      
      
      
 exec  sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate  
      
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-10-13T05:33:02.493+00:00

    Hi @Analyst_SQL ,

    2019:

         Declare @startDate date ='03-03-2019'  
         Declare @Enddate  date ='09-10-2019'  
         ;with cte as   
         (select Descriptionitem,format(EntryDate,'yyyy-MM') EntryDate ,a.Bpqty  
                FROM #Bigbalprd a left JOIN #ItemMasterFile b  
                on a.Codeitem=b.Codeitem)  
                --where a.DelID is null and  a.EntryDate BETWEEN @startDate AND @Enddate )      
          ,cte2 as   
           (select * from cte t PIVOT (Sum(Bpqty) for EntryDate in ([2019-01],[2019-02],[2019-03],[2019-04],[2019-05],[2019-06],[2019-07],[2019-08],[2019-09],[2019-10],[2019-11],[2019-12])) AS stat)     
           ,cte3 as  
             (select * ,isnull([2019-01],0)+isnull([2019-02],0)+isnull([2019-03],0)  
                     +isnull([2019-04],0)+isnull([2019-05],0)+isnull([2019-06],0)  
                     +isnull([2019-07],0)+isnull([2019-08],0)+isnull([2019-09],0)  
                     +isnull([2019-10],0)+isnull([2019-11],0)+isnull([2019-12],0) Total   
             from cte2)  
         select * from cte3  
         union all      
         select 'Total',sum([2019-01]),sum([2019-02]),  
                  sum([2019-03]),sum([2019-04]),  
                  sum([2019-05]),sum([2019-06]),  
                  sum([2019-07]),sum([2019-08]),  
                  sum([2019-09]),sum([2019-10]),  
                  sum([2019-11]),sum([2019-12]),sum(Total)  
         from cte3  
    

    The above code works for me.

    If you want to display the annual data in a table, please refer to:

     Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50));  
     Create table #Bigbalprd (DelID int,Codeitem int,Bpqty int,B_Weight int,EntryDate date);  
     INSERT INTO #ItemMasterFile VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'e'),  
                                       (6,'f'),(7,'g'),(8,'h'),(9,'K'),(10,'L'),(11,'M');     
     INSERT INTO #Bigbalprd VALUES  
      (111,1,1,500,'2019-03-06')  
      ,(112,2,1,200,'2019-03-06')  
      ,(113,1,1,300,'2019-03-06')  
      ,(114,6,1,100,'2019-04-07')  
      ,(115,1,1,200,'2019-04-07')  
      ,(116,1,1,300,'2019-08-15')  
      ,(117,7,1,100,'2019-08-20')  
      ,(118,5,1,200,'2019-05-09')  
      ,(119,8,1,300,'2019-06-09');  
      INSERT INTO #Bigbalprd  
     VALUES ( 111, 1, 1, 500, '2020-03-01' ) ,  
            ( 112, 2, 1, 200, '2020-03-06' ) ,  
            ( 113, 1, 1, 300, '2020-03-06' ) ,  
            ( 114, 6, 1, 100, '2020-04-07' ) ,  
            ( 115, 1, 1, 200, '2020-04-07' ) ,  
            ( 116, 1, 1, 300, '2020-08-15' ) ,  
            ( 117, 7, 1, 100, '2020-08-20' ) ,  
            ( 118, 5, 1, 200, '2020-05-09' ) ,  
            ( 119, 8, 1, 300, '2020-06-09' );  
       
      
     DECLARE @startDate DATE = '2019-03-01';  
     DECLARE @Enddate DATE = '2020-10-09';    
     ;WITH cte  
     AS ( SELECT   I.Descriptionitem ,  
                   YEAR(B.EntryDate) AS YNumber ,  
                   MONTH(B.EntryDate) AS MNumber ,  
                   B.Bpqty  
          FROM     #Bigbalprd B  
                   JOIN #ItemMasterFile I ON I.Codeitem = B.Codeitem  
          WHERE    B.EntryDate BETWEEN @startDate AND @Enddate ),  
     cte2 AS   
     (SELECT * FROM  cte t PIVOT (SUM(Bpqty)  
                        FOR MNumber IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )) P )    
    					  
    select * from cte2 where YNumber=2019  
    union all  
    select 'Total','2019',sum([1]),sum([2]),sum([3]),sum([4]),sum([5]),sum([6]),sum([7]),sum([8]),sum([9]),sum([10]),  
    sum([11]),sum([12]) from cte2 where YNumber=2019  
    union all  
    select * from cte2 where YNumber=2020  
    union all  
    select 'Total','2020',sum([1]),sum([2]),sum([3]),sum([4]),sum([5]),sum([6]),sum([7]),sum([8]),sum([9]),sum([10]),  
    sum([11]),sum([12]) from cte2 where YNumber=2020  
      
      
    drop table #Bigbalprd  
    drop table #ItemMasterFile  
    

    31844-image.png

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

6 additional answers

Sort by: Most helpful
  1. Stefan Hoffmann 621 Reputation points
    2020-10-13T12:35:21.1+00:00

    btw, I would also use the classic PIVOT approach in the end as it has a slightly better query plan and requires lesser code:

    SELECT   ISNULL(IMF.Descriptionitem, 'TOTAL') AS DescriptionItem ,
             ED.YearNumber ,
             SUM(IIF(ED.MonthNumber = 1, B.Bpqty, NULL)) AS [01] ,
             SUM(IIF(ED.MonthNumber = 2, B.Bpqty, NULL)) AS [02] ,
             SUM(IIF(ED.MonthNumber = 3, B.Bpqty, NULL)) AS [03] ,
             SUM(IIF(ED.MonthNumber = 4, B.Bpqty, NULL)) AS [04] ,
             SUM(IIF(ED.MonthNumber = 5, B.Bpqty, NULL)) AS [05] ,
             SUM(IIF(ED.MonthNumber = 6, B.Bpqty, NULL)) AS [06] ,
             SUM(IIF(ED.MonthNumber = 7, B.Bpqty, NULL)) AS [07] ,
             SUM(IIF(ED.MonthNumber = 8, B.Bpqty, NULL)) AS [08] ,
             SUM(IIF(ED.MonthNumber = 9, B.Bpqty, NULL)) AS [09] ,
             SUM(IIF(ED.MonthNumber = 10, B.Bpqty, NULL)) AS [10] ,
             SUM(IIF(ED.MonthNumber = 11, B.Bpqty, NULL)) AS [11] ,
             SUM(IIF(ED.MonthNumber = 12, B.Bpqty, NULL)) AS [12]
    FROM     #Bigbalprd B
             INNER JOIN #ItemMasterFile IMF ON IMF.Codeitem = B.Codeitem
             CROSS APPLY (   SELECT YEAR(B.EntryDate) AS YearNumber ,
                                    MONTH(B.EntryDate) AS MonthNumber ) ED
    WHERE    B.EntryDate BETWEEN @startDate AND @Enddate
    GROUP BY GROUPING SETS((IMF.Descriptionitem, ED.YearNumber), (ED.YearNumber));
    
    2 people found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 98,731 Reputation points
    2020-10-10T19:02:41.36+00:00

    I am not going to try to read that mess of dynamic SQL, but here is a general pattern for a sum by month from a table that has dates:

    SELECT convert(char(6), OrderDate, 112) AS month, SUM(Amount)
    FROM   Order
    GROUP  BY convert(char(6), OrderDate, 112)
    

    Hopefully, that can get you started.

    0 comments No comments

  3. EchoLiu-MSFT 14,571 Reputation points
    2020-10-12T10:02:04.173+00:00

    Hi @Analyst_SQL ,

    I have made some changes to your stored procedure, you can view the cte statement in the output result, and you can modify the cte statement according to your needs:

          Create Procedure [dbo].[Pivot_Daywiseproduction_BB]  
         @StartDate Date,  
         @Enddate   Date,  
         @query NVARCHAR(MAX) output   
    
      
     AS  
     BEGIN  
     DECLARE @cols AS NVARCHAR(MAX)  
     --DECLARE @query AS NVARCHAR(MAX)  
     DECLARE @cols1 AS NVARCHAR(MAX)  
     DECLARE @cols2 AS NVARCHAR(MAX)  
     DECLARE @cols3 AS NVARCHAR(MAX)  
     declare @sum nvarchar(max);  
     SELECT  @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(Bpqty)+')') from Bigbalprd;  
      -- SET NOCOUNT ON added to prevent extra result sets from  
      -- interfering with SELECT statements.  
          
      SET NOCOUNT ON;  
          
          
          
          
     SET NOCOUNT ON;  
     SELECT Distinct Entrydate INTO #Dates26 FROM Bigbalprd WHERE EntryDate BETWEEN @StartDate AND @Enddate   
     ORDER BY EntryDate  
     --select * from #Dates26  
     SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate,120) )  
     FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
     ORDER BY EntryDate  
     SELECT @cols1 = COALESCE(@cols1 + ',','') +'isnull('+ QUOTENAME( CONVERT(varchar(10),EntryDate,120) )+',0) '+QUOTENAME( CONVERT(varchar(10),EntryDate,120) )  
     FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
     ORDER BY EntryDate  
     SELECT @cols2 = COALESCE(@cols2 + ',','') +'sum('+ QUOTENAME( CONVERT(varchar(10),EntryDate,120) )+') '+QUOTENAME( CONVERT(varchar(10),EntryDate,120) )  
     FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
     ORDER BY EntryDate  
     SELECT @cols3 = COALESCE(@cols3 + '+','') + 'isnull('+ QUOTENAME( CONVERT(varchar(10),EntryDate,120) )+',0) '  
     FROM (SELECT DISTINCT EntryDate FROM #Dates26 ) T  
     ORDER BY EntryDate  
          
     --SET @cols =  STUFF(@cols, 1, 1, '')   
          
          
     SET @query =  
     N';with cte as (  
     SELECT Descriptionitem,'+@cols1+','+@cols3+' Total  
     FROM (SELECT Descriptionitem,Bigbalprd.Bpqty,  
     Bigbalprd.EntryDate  
     FROM Bigbalprd left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Bigbalprd.Codeitem  
     where Bigbalprd.DelID is null and  Bigbalprd.EntryDate BETWEEN '''+convert(varchar(10),@StartDate,120)+''' AND '''+convert(varchar(10),@Enddate,120)+''' )prd   
     PIVOT (Sum(bpqty) FOR EntryDate IN (' + @cols + ')) AS stat)  
     select * from cte   
     union all   
     select ''Total'','+@cols2+',sum(Total)   
     from cte '  
     select @query    
     exec  sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate  
              
     END  
      
     DECLARE @query AS NVARCHAR(MAX)  
     exec [dbo].[Pivot_Daywiseproduction_BB] '2020-01-06','2020-10-06',@query output   
     exec (@query)  
    

    31650-image.png
    cte statement:

    ;with cte as   
    (SELECT Descriptionitem,isnull([2020-06-03],0) [2020-06-03],isnull([2020-07-04],0) [2020-07-04],  
    isnull([2020-08-15],0) [2020-08-15],isnull([2020-08-20],0) [2020-08-20],  
    isnull([2020-09-05],0) [2020-09-05],isnull([2020-09-06],0) [2020-09-06],  
    isnull([2020-06-03],0) +isnull([2020-07-04],0) +isnull([2020-08-15],0) +isnull([2020-08-20],0) +isnull([2020-09-05],0) +isnull([2020-09-06],0)  Total     
    FROM (SELECT Descriptionitem,Bigbalprd.Bpqty,Bigbalprd.EntryDate   
          FROM Bigbalprd left JOIN ItemMasterFile   
    	  on ItemMasterFile.Codeitem=Bigbalprd.Codeitem     
    	  where Bigbalprd.DelID is null and  Bigbalprd.EntryDate BETWEEN '2020-01-06' AND '2020-10-06' )prd      
    	  PIVOT (Sum(bpqty) FOR EntryDate IN ([2020-06-03],[2020-07-04],[2020-08-15],[2020-08-20],[2020-09-05],[2020-09-06])) AS stat)     
    	    
    select * from cte      
    union all      
    select 'Total',sum([2020-06-03]) [2020-06-03],sum([2020-07-04]) [2020-07-04],  
    	    sum([2020-08-15]) [2020-08-15],sum([2020-08-20]) [2020-08-20],sum([2020-09-05]) [2020-09-05],  
    		sum([2020-09-06]) [2020-09-06],sum(Total)      
    from cte   
    

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  4. Stefan Hoffmann 621 Reputation points
    2020-10-12T11:51:13.453+00:00

    Well, I don't see the reason for dynamic SQL.. e.g.

    USE tempdb;  
    GO  
      
    DROP TABLE IF EXISTS #ItemMasterFile;  
    CREATE TABLE #ItemMasterFile (  
        Codeitem INT ,  
        Descriptionitem VARCHAR(50)  
    );  
      
    DROP TABLE IF EXISTS #Bigbalprd;  
    CREATE TABLE #Bigbalprd (  
        BID INT ,  
        Codeitem INT ,  
        Bpqty INT ,  
        B_Weight INT ,  
        EntryDate DATE  
    );  
      
    INSERT INTO #ItemMasterFile  
    VALUES ( 1, 'A' ) ,  
           ( 2, 'B' ) ,  
           ( 3, 'C' ) ,  
           ( 4, 'D' ) ,  
           ( 5, 'e' ) ,  
           ( 6, 'f' ) ,  
           ( 7, 'g' ) ,  
           ( 8, 'h' ) ,  
           ( 9, 'K' ) ,  
           ( 10, 'L' ) ,  
           ( 11, 'M' );  
      
      
    INSERT INTO #Bigbalprd  
    VALUES ( 111, 1, 1, 500, '2020-03-01' ) ,  
           ( 112, 2, 1, 200, '2020-03-06' ) ,  
           ( 113, 1, 1, 300, '2020-03-06' ) ,  
           ( 114, 6, 1, 100, '2020-04-07' ) ,  
           ( 115, 1, 1, 200, '2020-04-07' ) ,  
           ( 116, 1, 1, 300, '2020-08-15' ) ,  
           ( 117, 7, 1, 100, '2020-08-20' ) ,  
           ( 118, 5, 1, 200, '2020-05-09' ) ,  
           ( 119, 8, 1, 300, '2020-06-09' );  
      
    DECLARE @startDate DATE = '2020-03-01';  
    DECLARE @Enddate DATE = '2020-10-09';  
      
    WITH Filtered  
    AS ( SELECT   IMF.Descriptionitem ,  
                  YEAR(B.EntryDate) AS EntryYearNumber ,  
                  MONTH(B.EntryDate) AS EntryMonthNumber ,  
                  B.Bpqty  
         FROM     #Bigbalprd B  
                  INNER JOIN #ItemMasterFile IMF ON IMF.Codeitem = B.Codeitem  
         WHERE    B.EntryDate BETWEEN @startDate AND @Enddate ) ,  
         Pivoted  
    AS ( SELECT *  
         FROM   Filtered F  
             PIVOT (   SUM(Bpqty)  
                       FOR EntryMonthNumber IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )) P )  
    SELECT   ISNULL(P.Descriptionitem, 'TOTAL') AS DescriptionItem ,  
             P.EntryYearNumber ,  
             SUM(P.[1]) AS [01] ,  
             SUM(P.[2]) AS [02] ,  
             SUM(P.[3]) AS [03] ,  
             SUM(P.[4]) AS [04] ,  
             SUM(P.[5]) AS [05] ,  
             SUM(P.[6]) AS [06] ,  
             SUM(P.[7]) AS [07] ,  
             SUM(P.[8]) AS [08] ,  
             SUM(P.[9]) AS [09] ,  
             SUM(P.[10]) AS [10] ,  
             SUM(P.[11]) AS [11] ,  
             SUM(P.[12]) AS [12]  
    FROM     Pivoted P  
    GROUP BY GROUPING SETS((P.Descriptionitem, P.EntryYearNumber), (P.EntryYearNumber));  
    

    31559-image.png