Update columns automatically in reports

[Chuck712]

I'm not sure this can be done, but I have a report that in manually updated in Excel for distribution. The client would like to have it automatically sent monthly using SSRS. The reports shows trending of membership from month to month based on data from a table that gets populated with a SQL job monthly. So the first item was to update the count column, which I did with this query embedded in the report.

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @col AS VARCHAR(MAX)

DECLARE @start_date datetime --first day of last month
DECLARE @finish_date datetime --last day of last month
DECLARE @this_month datetime --current day of last month
SET @this_month = DATEADD(mm,-1,GetDate())
SET @start_date = CONVERT(datetime,STR(MONTH(@this_month))+'/01/'+
STR(YEAR(@this_month)),101)
IF MONTH(@this_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@this_month)+1,2)
+'/01/'+STR(YEAR(@this_month))+ ' 23:59:00',101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@this_month)+1)+ ' 23:59:00',101))

select @col = STUFF((SELECT ',' + QUOTENAME(h10_month)
from client_period_counts
where h10_year = DATEPART(yyyy,@finish_date)
group by h10_month
order by ABS(h10_month) ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @sql = N'
DECLARE @finish_date datetime --last day of last month
DECLARE @this_month datetime --current day of last month
SET @this_month = DATEADD(mm,-1,GetDate())

IF MONTH(@this_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@this_month)+1,2)
+''/01/''+STR(YEAR(@this_month))+ '' 23:59:00'',101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,''01/01/''+STR(YEAR(@this_month)+1)+ '' 23:59:00'',101))

SELECT *
from(
SELECT DISTINCT h10_chp_code,
h10_chp_name,
h10_calculated_balance,
h10_month
from client_period_counts pc

where h10_year = DATEPART(yyyy,@finish_date)
AND h10_delete_flag = 0
Group by h10_chp_code,h10_chp_name,h10_month,h10_calculated_balance
)SRC
PIVOT
(Sum(h10_calculated_balance)
FOR h10_month in ('+@col+'))AS PVT

order by ABS(PVT.h10_chp_code)'

;

--PRINT @sql;

execute sp_executesql @sql;

This returns this result with the new automatic column: With the number in the column heading being the month, so 1 = January, 2 = Feb and so on.
See Text file attached


Now here is what the client wants:
See Excel file attachment
In other words the membership count in one column and the difference between the previous column, then the difference between the current month column and the YTD which is column 1 or 1/31/2016.
I am attaching a text file to show the query results

I'm not sure if this is even possible to automate, but if anyone has seen something similar, please advise.

 

[Chuck712]

Sorry other attachment for the client didn't attach