Hi Sir,
Please see the below query
DDL :
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
Query giving right results :
DECLARE @YearStart DATE = cast(datepart(yy,getdate()) as varchar(4)) +'-1-'+'1'
DECLARE @Loop INT = 1
DECLARE @Cols NVARCHAR(max) = ''
,@SQL NVARCHAR(max)
,@curYear INT
,@curMonth INT
,@PrevMonth INT
,@curDate DATE;
SELECT
@PrevMonth = datepart(month, @YearStart)
,@curMonth = datepart(month, @YearStart)
,@curYear = datepart(year, @YearStart)
,@curDate = @YearStart;
WHILE @Loop < (datepart(mm,getdate())+1)
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 GROUPING SETS((Customer, Product), (Customer))
ORDER BY Customer, Product';
PRINT @SQL;
EXECUTE (@SQL);
I need to perform a select on the output returned from the above query and use it in another store proc for report generation am using .
Please Advice