none
Monthly Sales Query RRS feed

  • Question

  • Hi,

    I have two tables like below (Sales and Return)

    Sales

    Declare @Sales Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Sales values ('AAA','2018-12-01',1000)
    insert into @Sales values ('BBB','2019-01-09',2000)
    insert into @Sales values ('AAA','2019-02-08',5000)

    and Return table below

    Declare @Return Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Return values ('AAA','2018-12-15',200)
    insert into @Return values ('AAA','2019-01-09',100)

    i will pass two dates like fromDate and ToDate, i need to display like below table

    if i pass fromDate as '2018-12-01' and ToDate as '2019-02-28'

    Note : the date column amount would be sales table amount -  return table amount and the NetAmount should be sum of row amount.

    Thanks

    Friday, March 15, 2019 1:03 PM

Answers

  • Thanks Ousama,

    the order of the column changed and NetAmount column missing.

    Thanks 

    Kasim


    Declare @Sales Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Sales values ('AAA','2018-12-01',1000)
    insert into @Sales values ('BBB','2019-01-09',2000)
    insert into @Sales values ('AAA','2019-02-08',5000)
    
    Declare @Return Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Return values ('AAA','2018-12-15',200)
    insert into @Return values ('AAA','2019-01-09',100) 
    
    DROP TABLE IF EXISTS #tmp
    
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
    	@NetAmount  AS NVARCHAR(MAX)
    
    SELECT * INTO #tmp FROM (
    SELECT s._Name,CAST(YEAR(s._Date)AS VARCHAR(10))+RIGHT('0' + RTRIM(MONTH(s._Date)), 2) _OrderMonth, FORMAT(s._Date, 'MMMM')+CAST(YEAR(s._Date)AS VARCHAR(10)) _Month,ISNULL(s._Amount,0) AS _Amount 
    FROM @Sales s
    UNION
    SELECT r._Name,CAST(YEAR(r._Date)AS VARCHAR(10))+RIGHT('0' + RTRIM(MONTH(r._Date)), 2) _OrderMonth, FORMAT(r._Date, 'MMMM')+CAST(YEAR(r._Date)AS VARCHAR(10)) _Month,ISNULL(-r._Amount,0) AS _Amount 
    FROM @Return r
    ) A
    
    
    select @cols = STUFF((SELECT ',' + QUOTENAME(_Month) 
                        from #tmp
                        group by _OrderMonth,_Month
                        order by _OrderMonth
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    select @NetAmount = STUFF((SELECT '+ISNULL(' + _Month +',0)'
                        from #tmp
                        group by _OrderMonth,_Month
                        order by _OrderMonth
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT _Name,' + @cols + ','+@NetAmount+' AS NetAmount from 
                 (
                    select _Name, _Month, _Amount
                    from #tmp
                ) x
                pivot 
                (
                    sum(_Amount)
                    for _Month in (' + @cols + ')
                ) p '
    
    execute(@query);
    


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Friday, March 15, 2019 1:55 PM
  • Declare @Sales Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Sales values ('AAA','2018-12-01',1000)
    insert into @Sales values ('BBB','2019-01-09',2000)
    insert into @Sales values ('AAA','2019-02-08',5000)
    
    
     
    
    Declare @Return Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Return values ('AAA','2018-12-15',200)
    insert into @Return values ('AAA','2019-01-09',100)
    
    
    declare  @fromDate date= '2018-12-01' 
    
    declare  @toDate date='2019-02-28'
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeadersTotal NVARCHAR(4000) ;
    
    
    ;with mycte as (
    select _Name,_Date,_Amount from @Sales
    where _Date>=@fromDate and _Date<=@toDate
    union all
    select _Name,_Date,_Amount*-1 from @Return
    where _Date>=@fromDate and _Date<=@toDate
    )
    ,mycte2 as (
    select _Name,Format(_Date,'MMMyyyy') MMMyyyy,SUM(_Amount) amt 
    
    from mycte
    Group by  _Name,Format(_Date,'MMMyyyy')
    )
    
    select * into mytemp
    from mycte2
    ;with myMonthyear as (
    select distinct  MMMyyyy from mytemp
    )
    Select @ColumnHeaders = STUFF( (SELECT   ',' + 'ISNULL(SUM(CASE WHEN MMMyyyy= '''+ MMMyyyy+ ''' then  amt  else 0 end),0) as '+ quotename(MMMyyyy,'[')  + char(10)+char(13)
    FROM  myMonthyear 
    Order by Cast('1'+MMMyyyy as date)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    , @ColumnHeadersTotal = STUFF( (SELECT   '+' + 'ISNULL(SUM(CASE WHEN MMMyyyy= '''+ MMMyyyy+ ''' then  amt  else 0 end),0) '
    FROM  myMonthyear 
    Order by Cast('1'+MMMyyyy as date)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') +  '  as Total';
    
     --print @ColumnHeaders
     --print @ColumnHeadersTotal
    
    
      Declare @sql nvarchar(max);
    
    SELECT @sql = N'SELECT _Name,' + @ColumnHeaders +','+ @ColumnHeadersTotal +' FROM mytemp  
       group by _Name';
    
    -- print @sql
     EXECUTE sp_executesql @sql 
     
    
     drop table mytemp

    Friday, March 15, 2019 7:03 PM
    Moderator

All replies

  • Declare @Sales Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Sales values ('AAA','2018-12-01',1000)
    insert into @Sales values ('BBB','2019-01-09',2000)
    insert into @Sales values ('AAA','2019-02-08',5000)
    
    Declare @Return Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Return values ('AAA','2018-12-15',200)
    insert into @Return values ('AAA','2019-01-09',100) 
    
    DROP TABLE IF EXISTS #tmp
    
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    SELECT * INTO #tmp FROM (
    SELECT s._Name, FORMAT(s._Date, 'MMMM')+CAST(YEAR(s._Date)AS VARCHAR(10)) _Month,ISNULL(s._Amount,0) AS _Amount 
    FROM @Sales s
    UNION
    SELECT r._Name, FORMAT(r._Date, 'MMMM')+CAST(YEAR(r._Date)AS VARCHAR(10)) _Month,ISNULL(-r._Amount,0) AS _Amount 
    FROM @Return r
    ) A
    
    
    select @cols = STUFF((SELECT ',' + QUOTENAME(_Month) 
                        from #tmp
                        group by _Month
                        order by _Month
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT _Name,' + @cols + ' from 
                 (
                    select _Name, _Month, _Amount
                    from #tmp
                ) x
                pivot 
                (
                    sum(_Amount)
                    for _Month in (' + @cols + ')
                ) p '
    
    execute(@query);
    



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Friday, March 15, 2019 1:34 PM
  • Thanks Ousama,

    the order of the column changed and NetAmount column missing.

    Thanks 

    Kasim

    Friday, March 15, 2019 1:37 PM
  • Thanks Ousama,

    the order of the column changed and NetAmount column missing.

    Thanks 

    Kasim


    Declare @Sales Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Sales values ('AAA','2018-12-01',1000)
    insert into @Sales values ('BBB','2019-01-09',2000)
    insert into @Sales values ('AAA','2019-02-08',5000)
    
    Declare @Return Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Return values ('AAA','2018-12-15',200)
    insert into @Return values ('AAA','2019-01-09',100) 
    
    DROP TABLE IF EXISTS #tmp
    
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
    	@NetAmount  AS NVARCHAR(MAX)
    
    SELECT * INTO #tmp FROM (
    SELECT s._Name,CAST(YEAR(s._Date)AS VARCHAR(10))+RIGHT('0' + RTRIM(MONTH(s._Date)), 2) _OrderMonth, FORMAT(s._Date, 'MMMM')+CAST(YEAR(s._Date)AS VARCHAR(10)) _Month,ISNULL(s._Amount,0) AS _Amount 
    FROM @Sales s
    UNION
    SELECT r._Name,CAST(YEAR(r._Date)AS VARCHAR(10))+RIGHT('0' + RTRIM(MONTH(r._Date)), 2) _OrderMonth, FORMAT(r._Date, 'MMMM')+CAST(YEAR(r._Date)AS VARCHAR(10)) _Month,ISNULL(-r._Amount,0) AS _Amount 
    FROM @Return r
    ) A
    
    
    select @cols = STUFF((SELECT ',' + QUOTENAME(_Month) 
                        from #tmp
                        group by _OrderMonth,_Month
                        order by _OrderMonth
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    select @NetAmount = STUFF((SELECT '+ISNULL(' + _Month +',0)'
                        from #tmp
                        group by _OrderMonth,_Month
                        order by _OrderMonth
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT _Name,' + @cols + ','+@NetAmount+' AS NetAmount from 
                 (
                    select _Name, _Month, _Amount
                    from #tmp
                ) x
                pivot 
                (
                    sum(_Amount)
                    for _Month in (' + @cols + ')
                ) p '
    
    execute(@query);
    


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Friday, March 15, 2019 1:55 PM
  • ;WITH CTE1 AS (
    	SELECT ISNULL(s._Name, r._Name) AS _Name,
    	ISNULL(s._Date, r._Date) AS _Date,
    	ISNULL(s._Amount, 0) - ISNULL(r._Amount, 0) AS _Amount
    	FROM @Sales AS s
    	FULL JOIN @Return AS r ON r._Name = s._Name AND r._Date = s._Date
    ),
    CTE2 AS (
    	SELECT _Name, LEFT(DATENAME(MONTH, _Date), 3) + CAST(YEAR(_Date) AS char(4)) AS _Month, SUM(_Amount) AS _Amount
    	FROM CTE1
    	GROUP BY _Name, LEFT(DATENAME(MONTH, _Date), 3) + CAST(YEAR(_Date) AS char(4))
    )
    
    SELECT [_Name], 
    	   ISNULL([Dec2018], 0) AS [Dec2018], 
    	   ISNULL([Jan2019], 0) AS [Jan2019], 
    	   ISNULL([Feb2019], 0) AS [Feb2019], 
    	   ISNULL([Dec2018], 0) + ISNULL([Jan2019], 0) + ISNULL([Feb2019], 0) AS [_NetAmount]
    FROM
    (
    	SELECT _Name, _Month, _Amount
    	FROM CTE2
    ) AS tmp
    PIVOT
    (
    	SUM(_Amount)
    	FOR _Month IN ([Dec2018], [Jan2019], [Feb2019])
    ) AS pvt;


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Friday, March 15, 2019 2:06 PM
    Friday, March 15, 2019 2:05 PM
  • Declare @Sales Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Sales values ('AAA','2018-12-01',1000)
    insert into @Sales values ('BBB','2019-01-09',2000)
    insert into @Sales values ('AAA','2019-02-08',5000)
    
    
     
    
    Declare @Return Table (_Name varchar(30), _Date Date, _Amount decimal(18,3))
    insert into @Return values ('AAA','2018-12-15',200)
    insert into @Return values ('AAA','2019-01-09',100)
    
    
    declare  @fromDate date= '2018-12-01' 
    
    declare  @toDate date='2019-02-28'
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeadersTotal NVARCHAR(4000) ;
    
    
    ;with mycte as (
    select _Name,_Date,_Amount from @Sales
    where _Date>=@fromDate and _Date<=@toDate
    union all
    select _Name,_Date,_Amount*-1 from @Return
    where _Date>=@fromDate and _Date<=@toDate
    )
    ,mycte2 as (
    select _Name,Format(_Date,'MMMyyyy') MMMyyyy,SUM(_Amount) amt 
    
    from mycte
    Group by  _Name,Format(_Date,'MMMyyyy')
    )
    
    select * into mytemp
    from mycte2
    ;with myMonthyear as (
    select distinct  MMMyyyy from mytemp
    )
    Select @ColumnHeaders = STUFF( (SELECT   ',' + 'ISNULL(SUM(CASE WHEN MMMyyyy= '''+ MMMyyyy+ ''' then  amt  else 0 end),0) as '+ quotename(MMMyyyy,'[')  + char(10)+char(13)
    FROM  myMonthyear 
    Order by Cast('1'+MMMyyyy as date)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    , @ColumnHeadersTotal = STUFF( (SELECT   '+' + 'ISNULL(SUM(CASE WHEN MMMyyyy= '''+ MMMyyyy+ ''' then  amt  else 0 end),0) '
    FROM  myMonthyear 
    Order by Cast('1'+MMMyyyy as date)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') +  '  as Total';
    
     --print @ColumnHeaders
     --print @ColumnHeadersTotal
    
    
      Declare @sql nvarchar(max);
    
    SELECT @sql = N'SELECT _Name,' + @ColumnHeaders +','+ @ColumnHeadersTotal +' FROM mytemp  
       group by _Name';
    
    -- print @sql
     EXECUTE sp_executesql @sql 
     
    
     drop table mytemp

    Friday, March 15, 2019 7:03 PM
    Moderator
  • Thanks Ousama.
    Saturday, March 16, 2019 2:32 AM
  • Thanks for your reply.. my column in dynamic.. any way Ousama nailed it.
    Saturday, March 16, 2019 2:35 AM
  • Thanks Jingyang
    Saturday, March 16, 2019 2:42 AM
  • Hi, 

    how to bring column total at bottom of the row.

    Thanks

    Saturday, March 16, 2019 2:55 AM