none
Required Output for Quarter RRS feed

  • Question

  • Hi  ,


    I have a table that has customer,product,month ,year and month ,information .Sql Scripts

    Create Table SampleRevenue
    (Customer varchar(30),
     Product  varchar(30),
     SMonth    int ,
     SYear     int,
     Total Decimal )
     go

     
     Insert into SampleRevenue
     Values('Sam','Switch',1,2014,300.50)
     Insert into SampleRevenue
     Values('Sam','Clutch',1,2014,100)
     Insert into SampleRevenue
     Values('Sam','Viper',1,2014,50)
     Insert into SampleRevenue
     Values('Tom','Viper',1,2014,50)
     Insert into SampleRevenue
     Values('Tom','Viper',2,2014,50)
     Insert into SampleRevenue
     Values('Tom','Belt',2,2014,50)
     Insert into SampleRevenue
     Values('Tom','Dash',3,2014,50)
     Insert into SampleRevenue
     Values('Ron','Dash',3,2014,100)
     Insert into SampleRevenue
     Values('Ron','Dash',4,2014,150)
     Insert into SampleRevenue
     Values('Ron','Dash',4,2014,150)
     Insert into SampleRevenue
     Values('Tom','Dash',4,2014,150)
     Insert into SampleRevenue
     Values('Tom','Belt',5,2014,150)
     Insert into SampleRevenue
     Values('Ron','Viper',5,2014,150)
     Insert into SampleRevenue
     Values('Ron','Belt',5,2014,150)
     Insert into SampleRevenue
     Values('Tom','Belt',5,2014,150)
     Insert into SampleRevenue
     Values('Tom','Belt',5,2014,150)
     Insert into SampleRevenue
     Values('Tom','Belt',6,2014,150)

     go

    So like if i want to check the result for the current quarter . The output should be like below :

    All the current quarter months, Sum of the Product in the customer column and the difference between the months in the current quarter . Kindly Help

    Customer Apr-14 May-14 Jun-14 April14 - May14 May 14 - JUNE 14
    Ron  300 150 0 250 150
    Dash  300 0 0 0 0
    Viper 0 150 0 0 0
    Tom 150 450 0 -300 450
    Dash  150 0 0 . .
    Belt  0 450 150 . .


    • Edited by Priya Bange Tuesday, April 29, 2014 6:46 PM ..
    Tuesday, April 29, 2014 6:45 PM

Answers

  • Try:

    DECLARE @QuarterStart DATE = dateadd(quarter, datediff(quarter, '19000101', CURRENT_TIMESTAMP), '19000101')
    DECLARE @Loop INT = 1
    DECLARE @Cols NVARCHAR(max) = ''
    	,@SQL NVARCHAR(max)
    	,@curYear INT
    	,@curMonth INT
    	,@PrevMonth INT
    	,@curDate DATE;
    
    SELECT @PrevMonth = datepart(month, @QuarterStart)
    	,@curMonth = datepart(month, @QuarterStart)
    	,@curYear = datepart(year, @QuarterStart)
    	,@curDate = @QuarterStart;
    
    WHILE @Loop < 4
    BEGIN
    	SET @Cols = @Cols + ',
    	 SUM(CASE WHEN SYear =' + CAST(@curYear AS NVARCHAR(max)) + ' AND SMonth = ' + CAST(@curMonth AS NVARCHAR(max)) + ' THEN Total ELSE 0 END) AS ' + QUOTENAME(LEFT(DATENAME(month, @curDate), 3) + '-' + RIGHT(CAST(@curYear AS VARCHAR(4)), 2)) + CASE 
    			WHEN @PrevMonth < @curMonth
    				THEN ',
    	SUM(CASE WHEN SYear =' + CAST(@curYear AS NVARCHAR(max)) + ' AND SMonth = ' + CAST(@prevMonth AS NVARCHAR(max)) + ' THEN Total ELSE 0 END) - SUM(CASE WHEN SYear =' + CAST(@curYear AS NVARCHAR(max)) + ' AND SMonth = ' + CAST(@curMonth AS NVARCHAR(max)) + ' THEN Total ELSE 0 END) AS ' + QUOTENAME(LEFT(DATENAME(month, DATEADD(month, - 1, @curDate)), 3) + RIGHT(CAST(@curYear AS VARCHAR(4)), 2) + '-' + LEFT(DATENAME(month, @curDate), 3) + RIGHT(CAST(@curYear AS VARCHAR(4)), 2))
    			ELSE ''
    			END;
    	SET @PrevMonth = datepart(month, @curDate);
    	SET @Loop = @Loop + 1;
    	SET @curDate = dateadd(month, 1, @curDate)
    
    	SELECT @curMonth = datepart(month, @curDate)
    		,@curYear = datepart(year, @curDate);
    END
    
    --PRINT @Cols;
    SET @SQL = 'SELECT Customer, Product ' + @Cols + '
     FROM dbo.SampleRevenue
     GROUP BY Customer, Product
     ORDER BY Customer, Product';
     PRINT @SQL;
    EXECUTE (@SQL);


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Priya Bange Tuesday, April 29, 2014 9:05 PM
    Tuesday, April 29, 2014 8:39 PM
    Moderator

All replies

  • Dash twice?

    Consider dynamic crosstab.

    Dynamic PIVOT example:  http://www.sqlusa.com/bestpractices2005/dynamicpivot/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Tuesday, April 29, 2014 7:24 PM
    Moderator
  • hi ,

    The Dash are the difference between the 2 months and . i  have used in the cells as  i was getting formatting issue will pasting it .

    But please let me know how can i calculate the difference of the months in a sequence .

    Thanks

    Priya

    Tuesday, April 29, 2014 7:47 PM
  • Hi ,

    Kindly provide a query so i can format it according to my reporting requirement .

    Thanks

    Tuesday, April 29, 2014 7:53 PM
  • Hi ,

    Kindly provide a query so i can format it according to my reporting requirement .

    Thanks

    ;WITH CTE AS 
    (Select CUSTOMER,SMONTH,SYEAR,sum(TOTAL) AS TOTAL from #SampleRevenue
    group by TOTAL,CUSTOMER,SMONTH,SYEAR)
    Select CUSTOMER
    ,pvt.[1] as JAN
    ,PVT.[2] AS FEB
    ,PVT.[3] AS MARCH
    ,PVT.[4] AS APRIL
    ,PVT.[5] AS MAY
    ,PVT.[6] AS JUNE
    ,PVT.[7] AS JULY
    ,PVT.[8] AS AUGUST
    ,PVT.[9] AS SEPTEMBER
    ,PVT.[10] AS OCTOBER
    ,PVT.[11] AS NOVEMBER
    ,PVT.[12] AS DECEMBER
     from CTE PIVOT
     (Max(total) for smonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) PVT
    



    - please mark correct answers

    Tuesday, April 29, 2014 8:09 PM
  • As you requested.

     ;WITH CTE AS 
    (Select CUSTOMER,SMONTH,SYEAR,sum(TOTAL) AS TOTAL from SampleRevenue
    group by CUSTOMER,SMONTH,SYEAR)
    Select CUSTOMER
    ,PVT.[4] AS [APRIL-14]
    ,PVT.[5] AS [MAY-14]
    ,PVT.[6] AS [JUN-14]
    ,PVT.[4]-PVT.[5] AS [APRIL14-MAY14]
    ,PVT.[5]-PVT.[6] AS [MAY14-JUNE14]
    
     from CTE PIVOT
     (Max(total) for smonth IN ([4],[5],[6])) PVT
    


    - please mark correct answers

    Tuesday, April 29, 2014 8:27 PM
  • Hi Murali ,

    Thanks for the revert but the month names column will be dynamic  . Like suppose i want to check the data for the Oct - Dec Quarter .

    So instead of April,May and June . It will be Oct,Novem and December . I hope your getting some idea what am trying to convey here . Please let me know if some more detail is required .

    Thanks

    Priya

    Tuesday, April 29, 2014 8:31 PM
  • Hi ,

    The product should come under the customer name . Please refer to the output i have pasted. Kindly let me know if you require more details .

    Thanks

    Tuesday, April 29, 2014 8:38 PM
  • Try:

    DECLARE @QuarterStart DATE = dateadd(quarter, datediff(quarter, '19000101', CURRENT_TIMESTAMP), '19000101')
    DECLARE @Loop INT = 1
    DECLARE @Cols NVARCHAR(max) = ''
    	,@SQL NVARCHAR(max)
    	,@curYear INT
    	,@curMonth INT
    	,@PrevMonth INT
    	,@curDate DATE;
    
    SELECT @PrevMonth = datepart(month, @QuarterStart)
    	,@curMonth = datepart(month, @QuarterStart)
    	,@curYear = datepart(year, @QuarterStart)
    	,@curDate = @QuarterStart;
    
    WHILE @Loop < 4
    BEGIN
    	SET @Cols = @Cols + ',
    	 SUM(CASE WHEN SYear =' + CAST(@curYear AS NVARCHAR(max)) + ' AND SMonth = ' + CAST(@curMonth AS NVARCHAR(max)) + ' THEN Total ELSE 0 END) AS ' + QUOTENAME(LEFT(DATENAME(month, @curDate), 3) + '-' + RIGHT(CAST(@curYear AS VARCHAR(4)), 2)) + CASE 
    			WHEN @PrevMonth < @curMonth
    				THEN ',
    	SUM(CASE WHEN SYear =' + CAST(@curYear AS NVARCHAR(max)) + ' AND SMonth = ' + CAST(@prevMonth AS NVARCHAR(max)) + ' THEN Total ELSE 0 END) - SUM(CASE WHEN SYear =' + CAST(@curYear AS NVARCHAR(max)) + ' AND SMonth = ' + CAST(@curMonth AS NVARCHAR(max)) + ' THEN Total ELSE 0 END) AS ' + QUOTENAME(LEFT(DATENAME(month, DATEADD(month, - 1, @curDate)), 3) + RIGHT(CAST(@curYear AS VARCHAR(4)), 2) + '-' + LEFT(DATENAME(month, @curDate), 3) + RIGHT(CAST(@curYear AS VARCHAR(4)), 2))
    			ELSE ''
    			END;
    	SET @PrevMonth = datepart(month, @curDate);
    	SET @Loop = @Loop + 1;
    	SET @curDate = dateadd(month, 1, @curDate)
    
    	SELECT @curMonth = datepart(month, @curDate)
    		,@curYear = datepart(year, @curDate);
    END
    
    --PRINT @Cols;
    SET @SQL = 'SELECT Customer, Product ' + @Cols + '
     FROM dbo.SampleRevenue
     GROUP BY Customer, Product
     ORDER BY Customer, Product';
     PRINT @SQL;
    EXECUTE (@SQL);


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Priya Bange Tuesday, April 29, 2014 9:05 PM
    Tuesday, April 29, 2014 8:39 PM
    Moderator
  • Hi ,

    Thank you so much it's very near to actual requirement . Please is it possible to have output formatted in the below format  . Like in the same customer column product info also .Sum of the product for each month and then the total of all the products in the same month as total in front of the customer name and difference between the months in quarter .

    Please suggest .

    Thanks

    Priya



    Customer Apr-14 May-14 Jun-14 April14 - May14 May 14 - JUNE 14
    Ron  300 150 0 250 150
    Dash  300 0 0 0 0
    Viper 0 150 0 0 0
    Tom 150 450 0 -300 450
    Dash  150 0 0 . .
    Belt  0 450 150 . .




    Tuesday, April 29, 2014 8:53 PM
  • Change the GROUP BY expression to be

     GROUP BY GROUPING SETS((Customer, Product), (Customer), ()) (last if you want the Total for all customers as well)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 29, 2014 8:59 PM
    Moderator
  • Hi Murali ,

    Thanks for the revert but the month names column will be dynamic  . Like suppose i want to check the data for the Oct - Dec Quarter .

    So instead of April,May and June . It will be Oct,Novem and December . I hope your getting some idea what am trying to convey here . Please let me know if some more detail is required .

    Thanks

    Priya

    Declare @cols as nvarchar(max),
    @query as nvarchar(max);
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Smonth) FROM SampleRevenue FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
    
    
    set @query = ';WITH CTE AS 
    (Select CUSTOMER,SMONTH,SYEAR,sum(TOTAL) AS TOTAL from SampleRevenue
    group by CUSTOMER,SMONTH,SYEAR)
    Select CUSTOMER,' + @cols + ' from CTE PIVOT
    (MAX(TOTAL) for smonth IN (' + @cols + ')) pvt'
    
    EXECUTE(@QUERY)



    - please mark correct answers

    Tuesday, April 29, 2014 9:00 PM
  • BTW, I've been thinking I already explored this topic before and yes, I did.

    Check this article

    T-SQL: Create Report for Last 10 Years of Data


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Murali dhar Tuesday, April 29, 2014 9:17 PM
    Tuesday, April 29, 2014 9:07 PM
    Moderator
  • Hi Mam,

    I have asked another question based on the same sql script . Please assist . Named it as V2

    thanks

    Priya

    Wednesday, April 30, 2014 7:04 PM