Dynamic Columns - Cash Flow problem

Question

Dynamic Columns - Cash Flow problem

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Bezan Old Hand Points: 371 More actions · Answer 1

gah (9/1/2010)
just a thought....maybe I am missing the point but:
this is a cashflow forecast right?
why are you summing all entries?
surely either a debit or credit should be a negative, dependent upon how you work?

Not really a forecast. Data is in system. You can just see will it look like.

Yes one of them is negative but it is only sample data.... not from working system.

These 4 rows + Sum is OK.

WayneS SSC Guru Points: 95460 More actions · Answer 2

irobertson (9/1/2010)
Jeff Moden (9/1/2010)
Ooooooo... crud. I'm on my way to work and always miss out on the fun stuff. I'll be watching whoever gets to this one. 😛
Lol, I'm at work, completing some very important documentation... 😀

Ah yes, very important documentation indeed. Hence why you're here chiming in! :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 3

Bezan,

Given that @startdate = '2009-10-28', is Day1 = @startdate, or the following date?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 4

How's this? Still needs to be made dynamic; I just want to make sure that it's working correctly first.

;WITH CTE AS

(

SELECT Partner, Account, Debit, Credit,

Period = CASE WHEN CEILING(DATEDIFF(day, @startdate, Date)/(@periodlength*1.0)) =

DATEDIFF(day, @startdate, Date)/(@periodlength*1.0)

THEN convert(int, DATEDIFF(day, @startdate, Date)/(@periodlength*1.0) + 1)

ELSE CEILING(DATEDIFF(day, @startdate, Date)/(@periodlength*1.0))

END

FROM SomeTableX

WHERE Date >= @startdate

AND Date < DATEADD(day, (@periodlength * @periodsnumber), @startdate)

), CTE2 AS

(

SELECT Partner, Account, Period, Debit = SUM(Debit), Credit = SUM(Credit)

FROM CTE

GROUP BY Partner, Account, Period

), CTE3 AS

(

SELECT [Classification] = 'Partner Debit',

[Period 1] = sum(CASE WHEN Period = 1 THEN Debit ELSE 0.0 END),

[Period 2] = sum(CASE WHEN Period = 2 THEN Debit ELSE 0.0 END),

[Period 3] = sum(CASE WHEN Period = 3 THEN Debit ELSE 0.0 END),

[Period 4] = sum(CASE WHEN Period = 4 THEN Debit ELSE 0.0 END),

[Period 5] = sum(CASE WHEN Period = 5 THEN Debit ELSE 0.0 END)

FROM CTE2

WHERE Partner = @ph

UNION ALL

SELECT [Classification] = 'Partner Credit',

[Period 1] = sum(CASE WHEN Period = 1 THEN Credit ELSE 0.0 END),

[Period 2] = sum(CASE WHEN Period = 2 THEN Credit ELSE 0.0 END),

[Period 3] = sum(CASE WHEN Period = 3 THEN Credit ELSE 0.0 END),

[Period 4] = sum(CASE WHEN Period = 4 THEN Credit ELSE 0.0 END),

[Period 5] = sum(CASE WHEN Period = 5 THEN Credit ELSE 0.0 END)

FROM CTE2

WHERE Partner = @ph

UNION ALL

SELECT [Classification] = 'Account Debit',

[Period 1] = sum(CASE WHEN Period = 1 THEN Debit ELSE 0.0 END),

[Period 2] = sum(CASE WHEN Period = 2 THEN Debit ELSE 0.0 END),

[Period 3] = sum(CASE WHEN Period = 3 THEN Debit ELSE 0.0 END),

[Period 4] = sum(CASE WHEN Period = 4 THEN Debit ELSE 0.0 END),

[Period 5] = sum(CASE WHEN Period = 5 THEN Debit ELSE 0.0 END)

FROM CTE2

WHERE Account = @acc

UNION ALL

SELECT [Classification] = 'Account Credit',

[Period 1] = sum(CASE WHEN Period = 1 THEN Credit ELSE 0.0 END),

[Period 2] = sum(CASE WHEN Period = 2 THEN Credit ELSE 0.0 END),

[Period 3] = sum(CASE WHEN Period = 3 THEN Credit ELSE 0.0 END),

[Period 4] = sum(CASE WHEN Period = 4 THEN Credit ELSE 0.0 END),

[Period 5] = sum(CASE WHEN Period = 5 THEN Credit ELSE 0.0 END)

FROM CTE2

WHERE Account = @acc

)

SELECT * FROM CTE3

UNION ALL

SELECT [Classification] = 'Total',

[Period 1] = sum([Period 1]),

[Period 2] = sum([Period 2]),

[Period 3] = sum([Period 3]),

[Period 4] = sum([Period 4]),

[Period 5] = sum([Period 5])

FROM CTE3

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bezan Old Hand Points: 371 More actions · Answer 5

WayneS, Result seems to be OK but there are fixed 5 periods and they have to be dynamic. I should be able to exec this with 3 or 20 periods.

irobertson First part with no errors but second one with following:

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 39

Must declare the scalar variable "@i".

Msg 137, Level 15, State 2, Line 42

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 45

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 48

Must declare the scalar variable "@i".

Msg 137, Level 15, State 2, Line 52

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 53

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 56

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 57

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 60

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 61

Must declare the scalar variable "@sql_pivot_list".

WayneS SSC Guru Points: 95460 More actions · Answer 6

Bezan (9/1/2010)
WayneS, Result seems to be OK but there are fixed 5 periods and they have to be dynamic. I should be able to exec this with 3 or 20 periods.

Yes. That's the next difficult part. Unfortunately, the PIVOT operator also has hard-coded fields, so no matter how it's done, it has to be done with dynamic sql. So, check out the "CrossTabs and Pivots, Part 2" link in my signature - that article covers generating dynamic CrossTab queries. You should be able to handle it from here.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

irobertson Hall of Fame Points: 3695 More actions · Answer 7

very important documentation indeed

I was going to procrastinate on it, but I think I'll do that tomorrow....

[drum roll please]

😀

irobertson Hall of Fame Points: 3695 More actions · Answer 8

This should give you the result you expect. Again, I've split it into two chunks for testing, you'll probably want to combine the two sections into a single procedure.

Run this first to generate the interim data table:

DECLARE

@startdate datetime,

@periodlength int,

@periodsnumber int,

@ph nvarchar(3),

@acc nvarchar(3)

SET @startdate = '2009-10-28' -- probably will be fixed as today()

SET @periodlength = 6 -- number of days in each time period - DYNAMIC

SET @periodsnumber = 10 -- number of periods to be shown - DYNAMIC

SET @ph = 'PE1' -- Partner code

SET @acc = '330' -- Account code

; with cte as (

select [Date]

,[Partner]

,[Account]

,[Debit]

,[Credit]

, (datediff(d,@startdate,[Date]) - (datediff(d,@startdate,[Date]) % @periodlength)) / @periodlength as Period

from #SomeTableX

)

select Period

, [Partner]

, [Account]

, sum(Debit) as Debit

, sum(Credit) as Credit

into #interim

from cte

where Period < @periodsnumber

and (

[Partner] = @ph or [Account] = @acc

)

group by Period

, [Partner]

, [Account]

Now run this to output the results:

-- create a dynamic sql string to pivot the results

-- declare variables

declare @sql_main varchar(max)

, @sql_select_list varchar(max) = ''

, @sql_pivot_list varchar(max) = ''

, @sql_sum_list varchar(max) = ''

, @i int = 0 -- loop counter

-- next 4 variables are redeclared for testing

-- won't be required if you're wrapping this into a single stored procedure

, @periodsnumber int = 10

, @periodslength int = 6

, @ph nvarchar(3) = 'PE1'

, @acc nvarchar(3) = '330'

-- set the main sql string

-- note use of placeholders

set @sql_main =

'; with cte as (

select ''Partner'' as [Level], ''Debit'' as Tran_Type, <<select placeholder>>

from (

select Period, Debit

from #interim

where [Partner] = ''<<partner placeholder>>''

) src

pivot (sum([Debit]) for Period in (<<pivot placeholder>>)) pvt

union all

select ''Partner'' as [Level], ''Credit'' as Tran_Type, <<select placeholder>>

from (

select Period, Credit

from #interim

where [Partner] = ''<<partner placeholder>>''

) src

pivot (sum([Credit]) for Period in (<<pivot placeholder>>)) pvt

union all

select ''Account'' as [Level], ''Debit'' as Tran_Type, <<select placeholder>>

from (

select Period, Debit

from #interim

where [Account] = ''<<account placeholder>>''

) src

pivot (sum([Debit]) for Period in (<<pivot placeholder>>)) pvt

union all

select ''Account'' as [Level], ''Credit'' as Tran_Type, <<select placeholder>>

from (

select Period, Credit

from #interim

where [Account] = ''<<account placeholder>>''

) src

pivot (sum([Credit]) for Period in (<<pivot placeholder>>)) pvt

)

select * from cte

union all

select ''Total'', ''Total'', <<sum placeholder>>

from cte'

-- loop n times adding fields to the output list

-- and the pivot list

while @i < @periodsnumber

begin

-- convert the period numbers into meaningful headers

set @sql_select_list = @sql_select_list + quotename(cast(@i as varchar)) + ' as [' + cast(1 + (@i * @periodslength) as varchar) + '_to_' + cast((@i * @periodslength) + @periodslength as varchar) + '],'

-- just add the period numbers to the pivot list

set @sql_pivot_list = @sql_pivot_list + quotename(cast(@i as varchar)) + ','

-- add the new field names to the sum list

set @sql_sum_list = @sql_sum_list + 'sum([' + cast(1 + (@i * @periodslength) as varchar) + '_to_' + cast((@i * @periodslength) + @periodslength as varchar) + ']),'

-- increment counter

set @i = @i + 1

end

-- trim trailing commas

set @sql_select_list = left(@sql_select_list,len(@sql_select_list)-1)

set @sql_pivot_list = left(@sql_pivot_list,len(@sql_pivot_list)-1)

set @sql_sum_list = left(@sql_sum_list,len(@sql_sum_list)-1)

-- debug

print @sql_select_list

print @sql_pivot_list

print @sql_sum_list

-- replace placeholders

set @sql_main = replace(@sql_main,'<<select placeholder>>',@sql_select_list)

set @sql_main = replace(@sql_main,'<<pivot placeholder>>',@sql_pivot_list)

set @sql_main = replace(@sql_main,'<<sum placeholder>>',@sql_sum_list)

set @sql_main = replace(@sql_main,'<<partner placeholder>>',@ph)

set @sql_main = replace(@sql_main,'<<account placeholder>>',@acc)

-- debug

print @sql_main

-- execute

-- NOTE: exec carries security risks

-- and is best avoided if your procedure

-- is likely to be exposed to external inputs

exec(@sql_main)

Results for parameters entered above:

/*

Level Tran_Type 1_to_6 7_to_12 13_to_18 19_to_24 25_to_30 31_to_36 37_to_42 43_to_48 49_to_54 55_to_60

------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

Partner Debit 3.70 4.80 1.80 3.00 2.50 1.40 1.50 3.50 1.90 2.20

Partner Credit 7.00 8.10 2.90 4.50 4.90 2.60 2.70 5.10 2.30 4.90

Account Debit 5.00 3.20 1.80 3.00 2.50 1.40 1.50 3.30 1.90 2.20

Account Credit 9.40 5.40 2.90 4.50 4.90 2.60 2.70 5.70 2.30 4.90

Total Total 25.10 21.50 9.40 15.00 14.80 8.00 8.40 17.60 8.40 14.20

(5 row(s) affected)

*/

Bezan Old Hand Points: 371 More actions · Answer 9

irobertson I still get errors with your code

Meanwhile I was trying to modify WayneS solution.

I think it should work but there are still errors. I think there is problem with " ' " in parameters.

First:

DECLARE

@startdate datetime,

@periodlength int,

@periodsnumber int,

@ph nvarchar(3),

@acc nvarchar(3)

SET @startdate = '2009-10-28' -- probably will be fixed as today()

SET @periodlength = 6 -- number of days in each time period - DYNAMIC

SET @periodsnumber = 10 -- number of periods to be shown - DYNAMIC

SET @ph = 'PE1' -- Partner code

SET @acc = '330' -- Account code

; with cte as (

select [Date]

,[Partner]

,[Account]

,[Debit]

,[Credit]

, (datediff(d,@startdate,[Date]) - (datediff(d,@startdate,[Date]) % @periodlength)) / @periodlength as Period

from #SomeTableX

)

select Period

, [Partner]

, [Account]

, sum(Debit) as Debit

, sum(Credit) as Credit

into #interim

from cte

where Period < @periodsnumber

group by Period

, [Partner]

, [Account]

Than table with numbers (easy to replace with something else later):

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

and my code (with errors :/):

DECLARE

@startdate nvarchar(10), --cast datetime

@periodlength nvarchar(20), --cast int

@periodsnumber nvarchar(20), --cast int

@ph nvarchar(3),

@acc nvarchar(3),

@SQL1 nvarchar(max),

@SQLphD nvarchar(max),

@SQLphC nvarchar(max),

@SQLacctD nvarchar(max),

@SQLacctC nvarchar(max),

@SQL2 nvarchar(max),

@SQLTotal nvarchar(max),

@i int

SET @i = 5

SET @SQLphD = 'SELECT [Classification] = ''Partner Debit''' + CHAR(10)

SET @SQLphC = 'SELECT [Classification] = ''Partner Credit''' + CHAR(10)

SET @SQLacctD = 'SELECT [Classification] = ''Account Debit''' + CHAR(10)

SET @SQLacctC = 'SELECT [Classification] = ''Account Credit''' + CHAR(10)

SET @SQLTotal = 'SELECT [Classification] = ''Total''' + CHAR(10)

SET @startdate = '2009-10-28' -- probably will be fixed as today()

SET @periodlength = 6 -- number of days in each time period - DYNAMIC

SET @periodsnumber = 10 -- number of periods to be shown - DYNAMIC

SET @ph = 'PE1' -- Partner code

SET @acc = '330' -- Account code

-- Partner Debit

SELECT @SQLphD = @SQLphD

+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10)

FROM (SELECT cast(n AS nvarchar) as id

FROM dbo.Tally

WHERE n <= @i

) d

ORDER BY id

SELECT @SQLphD = @SQLphD +

'FROM CTE2

WHERE Partner = ''' + @ph + '''

UNION ALL' + CHAR(10)

-- Partner Credit

SELECT @SQLphC = @SQLphC

+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10)

FROM (SELECT cast(n AS nvarchar) as id

FROM dbo.Tally

WHERE n <= @i

) d

ORDER BY id

SELECT @SQLphC = @SQLphC +

'FROM CTE2

WHERE Partner = ''' + @ph + '''

UNION ALL' + CHAR(10)

-- Account Debit

SELECT @SQLacctD = @SQLacctD

+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10)

FROM (SELECT cast(n AS nvarchar) as id

FROM dbo.Tally

WHERE n <= @i

) d

ORDER BY id

SELECT @SQLacctD = @SQLacctD +

'FROM CTE2

WHERE Account = ''' + @acc + '''

UNION ALL' + CHAR(10)

-- Account Credit

SELECT @SQLacctC = @SQLacctC

+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10)

FROM (SELECT cast(n AS nvarchar) as id

FROM dbo.Tally

WHERE n <= @i

) d

ORDER BY id

SELECT @SQLacctC = @SQLacctC +

'FROM CTE2

WHERE Account = ''' + @acc + '''' + CHAR(10)

-- TOTAL

SELECT @SQLTotal = @SQLTotal

+ ',[Period ' + [id] +'] = sum([Period ' + [id] + '])' + CHAR(10)

FROM (SELECT cast(n AS nvarchar) as id

FROM dbo.Tally

WHERE n <= @i

) d

ORDER BY id

SELECT @SQLTotal = @SQLTotal +

'FROM CTE3' + CHAR(10)

-- SQL1

SELECT @SQL1 =

';WITH CTE AS

(

SELECT Partner, Account, Debit, Credit,

Period = CASE WHEN CEILING(DATEDIFF(day, ''' + CONVERT(@startdate, CURRENT_TIMESTAMP, 23) + ''', Date)/(''' + CAST(@periodlength AS int) + '''*1.0)) =

DATEDIFF(day, ''' + CAST(@startdate AS datetime) +''', Date)/(''' + CAST(@periodlength AS int) + '''*1.0)

THEN convert(int, DATEDIFF(day, ''' + CAST(@startdate as datetime) + ''', Date)/(''' + CAST(@periodlength as int) + '''*1.0) + 1)

ELSE CEILING(DATEDIFF(day, ''' + CAST(@startdate as datetime) + ''', Date)/(''' + CAST(@periodlength as int) + '''*1.0))

END

FROM SomeTableX

WHERE Date >= ''' + CAST(@startdate as datetime) + '''

AND Date < DATEADD(day, (''' + CAST(@periodlength as int) + ''' * ''' + CAST(@periodsnumber as int) + '''), ''' + CAST(@startdate as datetime) + ''')

), CTE2 AS

(

SELECT Partner, Account, Period, Debit = SUM(Debit), Credit = SUM(Credit)

FROM CTE

GROUP BY Partner, Account, Period

), CTE3 AS

(

'

-- @SQLphD + @SQLphC + @SQLacctD + @SQLacctC

SELECT @SQL2 =

'

)

SELECT * FROM CTE3

UNION ALL

'

-- @SQLTotal

EXEC (@SQL1+@SQLphD+@SQLphC+@SQLacctD+@SQLacctC+@SQL2+@SQLTotal)

@i ahould be replaced with @periodsnumber but later... when everything will be ok

irobertson Hall of Fame Points: 3695 More actions · Answer 10

irobertson

Hall of Fame

Points: 3695

September 2, 2010 at 4:37 am

#1215489

What errors do you get? Works fine for me?

Bezan Old Hand Points: 371 More actions · Answer 11

irobertson (9/2/2010)
What errors do you get? Works fine for me?

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 66

Must declare the scalar variable "@i".

Msg 137, Level 15, State 2, Line 69

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 72

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 75

Must declare the scalar variable "@sql_sum_list".

Msg 137, Level 15, State 2, Line 78

Must declare the scalar variable "@i".

Msg 137, Level 15, State 2, Line 82

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 83

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 84

Must declare the scalar variable "@sql_sum_list".

Msg 137, Level 15, State 2, Line 87

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 88

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 89

Must declare the scalar variable "@sql_sum_list".

Msg 137, Level 15, State 2, Line 92

Must declare the scalar variable "@sql_select_list".

Msg 137, Level 15, State 2, Line 93

Must declare the scalar variable "@sql_pivot_list".

Msg 137, Level 15, State 2, Line 94

Must declare the scalar variable "@sql_sum_list".

Msg 137, Level 15, State 2, Line 95

Must declare the scalar variable "@ph".

Msg 137, Level 15, State 2, Line 96

Must declare the scalar variable "@acc".

irobertson Hall of Fame Points: 3695 More actions · Answer 12

D'uh, you're on 2K5 of course. Can't assign variable values in the declare statement.

Try this (note not now split into two chunks, so table rewrites every time:

-- clear temp table

if isnull(object_id('tempdb..#interim'),0) <> 0

begin

drop table #interim

end

DECLARE

@startdate datetime,

@periodslength int,

@periodsnumber int,

@ph nvarchar(3),

@acc nvarchar(3)

SET @startdate = '2009-10-28' -- probably will be fixed as today()

SET @periodslength = 3 -- number of days in each time period - DYNAMIC

SET @periodsnumber = 12 -- number of periods to be shown - DYNAMIC

SET @ph = 'PE1' -- Partner code

SET @acc = '330' -- Account code

; with cte as (

select [Date]

,[Partner]

,[Account]

,[Debit]

,[Credit]

, (datediff(d,@startdate,[Date]) - (datediff(d,@startdate,[Date]) % @periodslength)) / @periodslength as Period

from SomeTableX

)

select Period

, [Partner]

, [Account]

, sum(Debit) as Debit

, sum(Credit) as Credit

into #interim

from cte

where Period < @periodsnumber

and (

[Partner] = @ph or [Account] = @acc

)

group by Period

, [Partner]

, [Account]

-- create a dynamic sql string to pivot the results

-- declare variables

declare @sql_main varchar(max)

, @sql_select_list varchar(max)

, @sql_pivot_list varchar(max)

, @sql_sum_list varchar(max)

, @i int -- loop counter

-- set variable values for SQL 2K5

set @i = 0

set @sql_select_list = ''

set @sql_pivot_list = ''

set @sql_sum_list = ''

-- set the main sql string

-- note use of placeholders

set @sql_main =

'; with cte as (

select ''Partner'' as [Level], ''Debit'' as Tran_Type, <<select placeholder>>

from (

select Period, Debit

from #interim

where [Partner] = ''<<partner placeholder>>''

) src

pivot (sum([Debit]) for Period in (<<pivot placeholder>>)) pvt

union all

select ''Partner'' as [Level], ''Credit'' as Tran_Type, <<select placeholder>>

from (

select Period, Credit

from #interim

where [Partner] = ''<<partner placeholder>>''

) src

pivot (sum([Credit]) for Period in (<<pivot placeholder>>)) pvt

union all

select ''Account'' as [Level], ''Debit'' as Tran_Type, <<select placeholder>>

from (

select Period, Debit

from #interim

where [Account] = ''<<account placeholder>>''

) src

pivot (sum([Debit]) for Period in (<<pivot placeholder>>)) pvt

union all

select ''Account'' as [Level], ''Credit'' as Tran_Type, <<select placeholder>>

from (

select Period, Credit

from #interim

where [Account] = ''<<account placeholder>>''

) src

pivot (sum([Credit]) for Period in (<<pivot placeholder>>)) pvt

)

select * from cte

union all

select ''Total'', ''Total'', <<sum placeholder>>

from cte'

-- loop n times adding fields to the output list

-- and the pivot list

while @i < @periodsnumber

begin

-- convert the period numbers into meaningful headers

set @sql_select_list = @sql_select_list + quotename(cast(@i as varchar)) + ' as [' + cast(1 + (@i * @periodslength) as varchar) + '_to_' + cast((@i * @periodslength) + @periodslength as varchar) + '],'

-- just add the period numbers to the pivot list

set @sql_pivot_list = @sql_pivot_list + quotename(cast(@i as varchar)) + ','

-- add the new field names to the sum list

set @sql_sum_list = @sql_sum_list + 'sum([' + cast(1 + (@i * @periodslength) as varchar) + '_to_' + cast((@i * @periodslength) + @periodslength as varchar) + ']),'

-- increment counter

set @i = @i + 1

end

-- trim trailing commas

set @sql_select_list = left(@sql_select_list,len(@sql_select_list)-1)

set @sql_pivot_list = left(@sql_pivot_list,len(@sql_pivot_list)-1)

set @sql_sum_list = left(@sql_sum_list,len(@sql_sum_list)-1)

-- debug

print @sql_select_list

print @sql_pivot_list

print @sql_sum_list

-- replace placeholders

set @sql_main = replace(@sql_main,'<<select placeholder>>',@sql_select_list)

set @sql_main = replace(@sql_main,'<<pivot placeholder>>',@sql_pivot_list)

set @sql_main = replace(@sql_main,'<<sum placeholder>>',@sql_sum_list)

set @sql_main = replace(@sql_main,'<<partner placeholder>>',@ph)

set @sql_main = replace(@sql_main,'<<account placeholder>>',@acc)

-- debug

print @sql_main

-- execute

-- NOTE: exec carries security risks

-- and is best avoided if your procedure

-- is likely to be exposed to external inputs

exec(@sql_main)

Although this does give ugly nulls, rather than the nice clean 0 you get with WayneS's solution. Easily handled with isnull in the expression though.

As an aside, I'd definitely be interested in seeing how the performance matches up on the interim disk write against the nested cte approach.

Bezan Old Hand Points: 371 More actions · Answer 13

irobertson (9/2/2010)
Try this (note not now split into two chunks, so table rewrites every time:

Now it is OK but like for me it is quite complicated. WayneS is simplier and quite easy to understand what is happening in each line.

Like I said I changed his ver into one with adjustable col amount but still have errors... now only 2 😉