Convert Procedure Day wise to Month wise Sum

Analyst_SQL 3,551

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

Ronen Ariely 15,186 Reputation points

2020-10-10T14:18:48.247+00:00

Please provide:

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).
EchoLiu-MSFT 14,581 Reputation points

2020-10-12T03:05:08.563+00:00

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

Echo
Analyst_SQL 3,551 Reputation points

2020-10-12T06:38:01.397+00:00

Hi @EchoLiu-MSFT
i have update above query with data.
EchoLiu-MSFT 14,581 Reputation points

2020-10-12T07:08:02.077+00:00

The time format in the green part is incorrect. Can you explain it?

Echo
Analyst_SQL 3,551 Reputation points

2020-10-12T07:44:33.403+00:00

Date format is like ,in my table below
(2020-07-21)

format in data first coming Day,then Month and last year
EchoLiu-MSFT 14,581 Reputation points

2020-10-12T08:06:31.667+00:00

Did you provide a temporary table? Many of the column names do not match the stored procedure. I tried to modify it, but no data was returned after the modification. Perhaps some column names did not correspond. So is it convenient for you to provide complete data, or you can give the code based on daily calculation, I may be able to modify it to calculate based on monthly.

Echo
Analyst_SQL 3,551 Reputation points

2020-10-12T08:40:24.283+00:00

again update query and data
EchoLiu-MSFT 14,581 Reputation points

2020-10-12T08:54:51.657+00:00

Data still has problems, please confirm:Whether the Codeitem column is item_ID？Where are DelID and Descriptionitem listed？

Echo
Analyst_SQL 3,551 Reputation points

2020-10-12T09:10:12.073+00:00

yes ,Codeitem column is item_ID,

Delid Column is in #Bigbalprd and Descriptionitem in #ItemMasterFile

Above data and query is working .and no error is coming
Analyst_SQL 3,551 Reputation points

2020-10-12T09:16:28.507+00:00

Sorry ,i mentioned wrong name in above query and data,

Now please check again ,i have again updated query and Data
EchoLiu-MSFT 14,581 Reputation points

2020-10-12T09:36:31.85+00:00

After a long time, we seem to have finally found the right direction. What output do you expect?

Echo
Analyst_SQL 3,551 Reputation points

2020-10-12T09:48:21.043+00:00

i have attached above my desire output.
EchoLiu-MSFT 14,581 Reputation points

2020-10-12T09:50:49.563+00:00

You may not upload successfully, please check again.

Echo

Accepted answer

EchoLiu-MSFT 14,581

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

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.

EchoLiu-MSFT 14,581 Reputation points

2020-10-13T05:50:51.78+00:00

The second method refers to Stefan's code, thanks.

Echo

6 additional answers

EchoLiu-MSFT 14,581

Hi @Analyst_SQL ,

We have struggled with complex dynamic SQL for a long time, but as Stefan said, not using dynamic SQL may make the problem much easier：

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,'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'  
;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 ([2020-01],[2020-02],[2020-03],[2020-04],[2020-05],[2020-06],[2020-07],[2020-08],[2020-09],[2020-10],[2020-11],[2020-12])) AS stat)     
  ,cte3 as  
    (select * ,isnull([2020-01],0)+isnull([2020-02],0)+isnull([2020-03],0)  
            +isnull([2020-04],0)+isnull([2020-05],0)+isnull([2020-06],0)  
			+isnull([2020-07],0)+isnull([2020-08],0)+isnull([2020-09],0)  
            +isnull([2020-10],0)+isnull([2020-11],0)+isnull([2020-12],0) Total   
    from cte2)  
select * from cte3  
union all      
select 'Total',sum([2020-01]) [2020-01],sum([2020-02]) [2020-02],  
         sum([2020-03]) [2020-03],sum([2020-04]) [2020-04],  
		 sum([2020-05]) [2020-05],sum([2020-06])[2020-06],  
		 sum([2020-07])[2020-07],sum([2020-08])[2020-08],  
		 sum([2020-09])[2020-09],sum([2020-10])[2020-10],  
		 sum([2020-11])[2020-11],sum([2020-12])[2020-12],sum(Total)  
from cte3  
  
   
drop table #Bigbalprd  
drop table #ItemMasterFile

Regards
Echo

EchoLiu-MSFT 14,581 Reputation points

2020-10-13T03:22:08.333+00:00

If you want to display the data for 2019, just replace 2020 in the code with 2019.Show the values of the remaining years in the same way.

Echo
Analyst_SQL 3,551 Reputation points

2020-10-13T03:36:50.007+00:00

i have added 2019,but date filter is not working
EchoLiu-MSFT 14,581 Reputation points

2020-10-13T05:29:19.163+00:00

If you want to display annual data, you need to write similar codes each year.

Echo

Analyst_SQL 3,551 Reputation points

2020-10-13T18:04:33.047+00:00

@Stefan Hoffmann thanks ,your provided query also working as per my desire ouput
Please sign in to rate this answer.
Stefan Hoffmann 621 Reputation points

2020-10-14T11:27:50.473+00:00

I have to admit that I don't understand why using UNION ALL with additional CTE's is the solution, if a single statement is possible, Especially as I posted a normal solution first.
Sign in to comment

Share via

Convert Procedure Day wise to Month wise Sum

6 additional answers