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

Add Balance (Total) column to PIVOT

Add Balance (Total) column to PIVOT

(OP)
Using SQL Server 2014, suppose I have a pivot query like so:

SELECT *
FROM (
SELECT
gl.reknr as [GL Account], oms25_0 as [Description], left(datename(month,docdate),13)as [Month],
bdr_hfl as Amount
FROM gbkmut gl left outer join grtbk acct on gl.reknr = acct.reknr
where docdate between '2016-01-01' and '2016-10-31'
)
as TB
PIVOT
(
SUM(Amount)
FOR [Month] IN (January, February, March, April,
May, June, July, August, September, October, November, December)
)
AS PVT
order by [GL Account] asc

How can I add 1 more column 'Balance' that is the total of every month's Amount? Basically a Trial Balance query with the YTD balance as a column unto itself?

Thank you.

RE: Add Balance (Total) column to PIVOT

I think you get no answers, because it's quite impossible to just expand your code theoretically without having some data to play with.
Without that let me start and state it's easier to do the YTD calculations as afftermath of having the pivoted month sales.

What you want with one pivot is like asking whether grouping of a granularity of month is possible at the same time of grouping by quarter and years. The simple answer is no. You can of course do anything anyway, but it may require a totally different solution strategy or tool in your toolbelt, than trying to make this work with pivoting, eg the now not so new windowing functions are helpful to build up running totals - see https://sqlperformance.com/2012/07/t-sql-queries/r...

Bye, Olaf.

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