INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pivot dynamic sql and calculated columns

Pivot dynamic sql and calculated columns

(OP)
I have this static version of a pivot

CODE

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

CODE

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

RE: Pivot dynamic sql and calculated columns

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

RE: Pivot dynamic sql and calculated columns

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:

CODE

--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

CODE

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);

With pivoted 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 
)

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

Notice, the main part of the asnwer here is just the lower query Select *, ([2017-01]-[2016-08])/[2016-08]*100 as change from pivoted. 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:

CODE

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);

With pivoted 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 
),
changed as
( 
 Select *, ([2017-01]-[2016-08])/[2016-08]*100 as [2016-08 to 2017-01 change %] from pivoted
)

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

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.

RE: Pivot dynamic sql and calculated columns

(OP)
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:

CODE

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) 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close