Share via

Convert Procedure Day wise to Month wise Sum

Analyst_SQL 3,551 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,601 questions
Accepted answer
  1. EchoLiu-MSFT 14,581 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
Most helpful Newest Oldest
  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
  2. Erland Sommarskog 107.2K 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
  3. EchoLiu-MSFT 14,581 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