Pivot dynamic sql and calculated columns 2

[wbodger]

I have this static version of a pivot

SELECT SBType,[2016-06],[2016-08],[2017-01] FROM
(
SELECT [MonthYear]
      ,Amount
      ,SBType
FROM [dbo].[vw_history]
WHERE SN=36521487
AND Amount>0
AND (Direction='Incoming' OR IsIncoming=1)
AND IsReturn=0
)src
PIVOT
(
SUM(Amount)
for MonthYear in ([2016-06],[2016-08],[2017-01])
) as pvt

which gives this data

SBType	2016-06	        2016-08	        2017-01
XX	91199725.1118	96441374.732	305296408.57
YY	147496746.94	186075209.717	362227931.77

and then when it is dynamic it is those month-year columns that change and are set dynamically. Now, my challenge is that I need to supply two percentage calculations with the first being the % change between the second to last column and the last column (217% and 95% respectively (b-a)/a) and then I need to know the breakdown of the percentage XX and YY of the total (in this case 46% and 54% respectively). What I cannot see how to do is how do I programmatically choose the last column and perform calculations on either that column and the one before it or between the rows in that column?

Thank you for any help you can give me!

Willie

 

[simian336]

If I understand you correctly, one method would be to create a "temp" table (but in this case I would create a real table and just delete all thee records each time) and and insert your above results into the table. Then you can add additional calculation queries to any additional columns that you may need.

It that way it is both static and dynamic.

Simi

 

[Olaf Doschke]

You can also make your query a subquery you then query the percentages in the outer query. Dynamic column names are no problem, are they. Whatever tool puts them together already has to know them to create this query, so the outer subquery can be generated with same known names.

Although it often scares beginners a CTE definition will give you the chance to formulate such a more complex query as two simpler with a simple name (the cte name) being a placeholder for the inner subquery, the query you already have. CTEs scare people off, because they have the half knowledge this is something about recursion. Only if the CTE query references itself. You can also have simple CTEs just giving better readability than a complex query.

First to illustrate that:

--query without cte
Select * from (select * from sys.sysusers where islogin=1) as logins where name like 'd%';

--query with cte
with logins as
(select * from sys.sysusers where islogin=1);

Select * from logins where name like 'd%';

The first query is not that convoluted, that it suggests a separate cte notation, but in the end it's just another way of giving a subquery a name. With the benefit of possible recursion and other properties you may set at the end of the cte parenthesis.

For your case, 1st degree (the change percentages

declare @income as table (SN Integer, SBType Char(2), Monthyear Char(7), Amount Money, IsIncoming Bit, IsReturn Bit );

insert into @income values 
(36521487, 'XX','2016-06', 91199725.1118, 1, 0),
(36521487, 'XX','2016-08', 96441374.732, 1, 0),
(36521487, 'XX','2017-01', 305296408.57, 1, 0),
(36521487, 'YY','2016-06', 147496746.94, 1, 0),
(36521487, 'YY','2016-08', 186075209.717, 1, 0),
(36521487, 'YY','2017-01', 362227931.77, 1, 0)[highlight #EF2929][COLOR=#FFFFFF];[/color][/highlight]

With [highlight #FCE94F]pivoted[/highlight] as
[highlight #FCE94F]([/highlight]
 SELECT SBType,[2016-06],[2016-08],[2017-01] FROM
  (
   SELECT [MonthYear]
      ,Amount
      ,SBType
   FROM @income
   WHERE SN=36521487
   AND Amount>0
   AND IsIncoming=1
   AND IsReturn=0
  )src
  PIVOT 
  (
  SUM(Amount)
  for MonthYear in ([2016-06],[2016-08],[2017-01])
  ) as pvt 
[highlight #FCE94F])[/highlight]

Select *, ([2017-01]-[2016-08])/[2016-08]*100 as change from [highlight #FCE94F]pivoted[/highlight]

Notice, the main part of the asnwer here is just the lower query [tt]Select *, ([2017-01]-[2016-08])/[2016-08]*100 as change from pivoted[/tt]. You could also have gotten there yourself without knowing CTE and just knowing the possibility of subqueries, if you put your whole query into brackets instead of the cte name 'pivoted'.

Know to get percenteges of the rows in relation to a total is a third query. For that you can simply define two CTEs and do the last one as final query:

declare @income as table (SN Integer, SBType Char(2), Monthyear Char(7), Amount Money, IsIncoming Bit, IsReturn Bit );

insert into @income values 
(36521487, 'XX','2016-06', 91199725.1118, 1, 0),
(36521487, 'XX','2016-08', 96441374.732, 1, 0),
(36521487, 'XX','2017-01', 305296408.57, 1, 0),
(36521487, 'YY','2016-06', 147496746.94, 1, 0),
(36521487, 'YY','2016-08', 186075209.717, 1, 0),
(36521487, 'YY','2017-01', 362227931.77, 1, 0)[highlight #EF2929][COLOR=#FFFFFF];[/color][/highlight]

With [highlight #FCE94F]pivoted[/highlight] as
(
 SELECT SBType,[2016-06],[2016-08],[2017-01] FROM
  (
   SELECT [MonthYear]
      ,Amount
      ,SBType
   FROM @income
   WHERE SN=36521487
   AND Amount>0
   AND IsIncoming=1
   AND IsReturn=0
  )src
  PIVOT 
  (
  SUM(Amount)
  for MonthYear in ([2016-06],[2016-08],[2017-01])
  ) as pvt 
),
[highlight #FCE94F]changed[/highlight] as
( 
 Select *, ([2017-01]-[2016-08])/[2016-08]*100 as [2016-08 to 2017-01 change %] from [highlight #FCE94F]pivoted[/highlight]
)

Select *, [2017-01]/(Select SUM([2017-01]) from [highlight #FCE94F]pivoted[/highlight])*100 as [2017-01 %] from [highlight #FCE94F]changed[/highlight]

Result:

Even without knowing CTE you could have done that with subqueries, simple brackets around inner queries you give an alias name. The outer queries are simply your percentage calculations, then.

Bye, Olaf.

 

[Olaf Doschke]

Not sure, but maybe you also want to take a look at more advanced aggregation functions T-SQL offers.

PERCENTILE_CONT -

https://blog.sqlauthority.com/2011/...ytic-functions-introduced-in-sql-server-2012/

PERCENTILE_DISC -

https://appliedsql.net/2014/02/16/percentile_disc-function-in-sql-server-2012/

Bye, Olaf.

 

[wbodger]

Just realized that I did not respond, sorry about that. I ended up using ides from both Simian and Olaf to get this to work using temp tables, dynamic sql, pivot and some pre-calculated variables. Chances are there is probably a better way to do this, but that seems to always be the case. Below is my code, perhaps it will help somebody find a better way:

CREATE PROCEDURE [VolumeTotalsByType]
	(
		@ToDate				DateTime,
		@BackTo				int,
		@ThisISN			varchar(12)
	)
AS
BEGIN
DECLARE @cols nvarchar(max),
		@sql nvarchar(max),
		@FromDate datetime,
		@LastMonthYear varchar(9),
		@NextToLastMonthYear varchar(9)

--****	Make sure that the Data is converted to the First of the Month
SET	@ToDate	=	DATEADD(month, DATEDIFF(month, 0, @ToDate), 0);

SELECT @FromDate = DATEADD(YEAR,-1*ABS(@BackTo), @ToDate)

SELECT
	@cols = STUFF((SELECT DISTINCT
			',' + QUOTENAME(MonthYear)
		FROM vw_History
		WHERE @FromDate <= FirstOfMonth
		AND @ToDate > FirstOfMonth
		AND ThisISN = @ThisISN
		ORDER BY ',' + QUOTENAME(MonthYear)
		FOR XML PATH (''), TYPE)
	.value('.', 'NVARCHAR(MAX)')
	, 1, 1, '')

SELECT @LastMonthYear = QUOTENAME(MAX(MonthYear))
FROM vw_History
WHERE FirstOfMonth <= DATEADD(MONTH,-1,@ToDate)

SELECT @NextToLastMonthYear = QUOTENAME(MAX(MonthYear))
FROM vw_History
WHERE FirstOfMonth < (SELECT DISTINCT FirstOfMonth FROM vw_History WHERE QUOTENAME(MonthYear) = @LastMonthYear)

--SELECT @LastMonthYear Last, @NextToLastMonthYear NextToLast

SET @sql = '
SELECT * INTO #MyTempTable FROM
(
	SELECT MSBType as Name,' + @cols + ' FROM
	(
	SELECT [MonthYear]
		  ,Amount
		  ,MSBType
	FROM [vw_History]
	WHERE BankISN=' + @BankISN + '
	AND Amount>0
	AND (Direction=''Incoming'' OR IsIncoming=1)
	AND IsReturn=0
	)src
	PIVOT
	(
	SUM(Amount)
	for MonthYear in (' + @cols + ')
	) as pvt
) as x
SELECT *,ROUND((' + @LastMonthYear + '-' + @NextToLastMonthYear + ')/' + @NextToLastMonthYear + ',2) as ''PerChg'', ROUND(' + @LastMonthYear + '/(sum(' + @LastMonthYear + ') over()),2) as ''PerTotal'' FROM #MyTempTable
DROP TABLE #MyTempTable'

EXECUTE (@sql)