Split value and pivot - 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)

Split value and pivot

Author

Topic

SMCE
Starting Member

3 Posts

Posted - 2011-08-04 : 12:34:51

Hi,

I have a table with customerID, incomes (amount) and the transaction dates:

c_id t_date amount
104130 2009-01-12 112.00
106265 2009-08-15 96.00
150993 2010-01-09 8.00
155210 2011-07-16 71.00
161069 2011-07-09 96.00

What I want to do now is to spread each amount on the month of the transaction date and the 11 following months:

c_id ¦ t_date ¦amount ¦ 2009_01 ¦2009_02¦...¦2011_07 ¦2011_08 ...
104130¦ 2009-01-12 ¦112.00 ¦ 9.33 ¦9.33 ¦...¦0.00 ¦0.00
106265¦ 2009-02-15 ¦96.00 ¦ 0.00 ¦8.00 ¦...¦0.00 ¦0.00
150993¦ 2010-08-09 ¦36.00 ¦ 0.00 ¦0.00 ¦...¦3.00 ¦0.00
155210¦ 2011-07-16 ¦71.00 ¦ 0.00 ¦0.00 ¦...¦5.92 ¦5.92
161069¦ 2011-08-09 ¦96.00 ¦ 0.00 ¦0.00 ¦...¦0.00 ¦8.00

Therefore I have to build the columns with the year/month dynamically.

I guess a dynamic pivot could be a possible solution, but how can I spread the amounts on the affected months?

Any ideas?

Thx in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 12:58:23

see this to build date table and use pivot on this

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SMCE
Starting Member

3 Posts

Posted - 2011-08-05 : 05:05:59

Hi Visakh

Thanks for the function, but I don't see how it could help solving this problem. I managed to create a table with all month columns I need, but I got stuck when I tried to split the incoming amount on 12 months (month of transaction date and the following 11 months).

How would you solve this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-05 : 07:05:54

[code]
declare @sql nvarchar(max),
@mths nvarchar(max)

; with dates as
(
select mn_date = min(t_date), mx_date = dateadd(month, 11, max(t_date))
from tbl
)
select @mths = isnull(@mths + ',', '') + '[' + convert(varchar(6), dateadd(month, v.number, d.mn_date), 112) + ']'
from dates d
cross join master.dbo.spt_values v
where type = 'P'
and v.number <= datediff(month, d.mn_date, d.mx_date)

select @sql = N'
; with
data as
(
select t.c_id, t_date, t.amount,
t_mth = convert(varchar(6), dateadd(month, m.n, t_date), 112),
t_amt = case when n = 0
then amount - (round(amount / 12, 2) * 11)
else round(amount / 12, 2)
end
from tbl t
cross join
(
select n = number
from master.dbo.spt_values v
where v.type = ''P''
and v.number between 0 and 11
) m
)
select *
from data d
pivot
(
sum(t_amt)
for t_mth in (' + @mths + ')
) p'

print @sql
exec (@sql)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SMCE
Starting Member

3 Posts

Posted - 2011-08-05 : 08:45:16

khtan, you brought me on the right way!

Cross join of the original data with a tally table, followed by a pivot, does exactly what I wanted!

Thanks a lot man, you saved my day!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-05 : 09:11:59

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources