Statement of Account - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Statement of Account

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2013-10-07 : 09:55:17

Hi all. I need to retrieve the statement of account for the past 12 months.

I can retrieve for those months that have outstanding balances. But I also need to include those months where there's zero balance. That's where I'm stuck.

My (simplified) table design.


Customer	Date		InvoiceTotal
Big John	2013-08-01	56.0000
Big John	2013-08-23	43.0000
Big John	2013-09-12	12.0000
Big John	2013-09-23	13.0000

Little Star	2013-07-23	82.0000
Little Star	2013-09-20	293.0000
Little Star	2013-10-01	43.0000

Assuming today is 7-Oct-13, I need to retrieve the last 12 months statement (inclusive of Oct-13).


Customer	Nov-13	Dec-13	Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13
Big John	0	0	0	0	0	0	0	0	0	99	25	0
Little Star	0	0	0	0	0	0	0	0	82	0	293	43

Thanks in advance.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-07 : 10:41:27

Look at PIVOT. It may involve some dynamic code to get the month headers.

djj

hayashiryo
Starting Member

46 Posts

Posted - 2013-10-07 : 11:22:18

Thanks for the tip.

However, I got as far as this:


SELECT 'Month' AS Balance_Sorted_By_Months, 
[-1], [0], [1], [2], [3], [4], [5], [6], [7], , [9], [10]
FROM

(SELECT		MONTH(Date) AS MyMonth,
		InvoiceTotal
FROM		dbo.Table
WHERE		(Customer = 'Big John')) AS SourceTable

PIVOT

(
SUM(InvoiceTotal) FOR MyMonth IN ([-1], [0], [1], [2], [3], [4], [5], [6], [7], , [9], [10])
) AS PivotTable

This is the result I got:


Balace_Sorted_By_Months	-1	0	1	2	3	4	5	6	7	8	9	10
Month			NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	99	25	NULL

How do I dynamically code in the last 12 months, which is represented by [-1] to [10]? (Note: [10] represent current month which is Oct)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 11:35:53

For this example, you can use a coalesce on the outer query like shown below. Ideally, one would use a numbers table in the inner query to get a row for every month

SELECT 'Month' AS Balance_Sorted_By_Months, 
COALESCE(COALESCE([-1],0),0), COALESCE([0],0), COALESCE([1],0), COALESCE([2],0), 
COALESCE([3],0), COALESCE([4],0), COALESCE([5],0), COALESCE([6],0), COALESCE([7],0), 
COALESCE([8],0), COALESCE([9],0), COALESCE([10]
FROM
...

hayashiryo
Starting Member

46 Posts

Posted - 2013-10-07 : 23:08:42

Hey James,

Thanks for the tip. With your suggestion, I managed to get the query to return 0 instead of Null. So that's good.

However, I'm not sure how I can dynamically specify the month.

For example, current month is Oct, which is a value of 10.
Therefore in my query, I hardcoded the following


[-1], [0], [1], [2], [3], [4], [5], [6], [7], , [9], [10]

If current month is November, which is a value of 11, then I will need to specify as follows:


[0], [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11]

So the question is how do I dynamically specify these numbers?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-08 : 07:55:00

See if this helps http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources