QQQ. Days of the month, close to open.

Profit/loss (%) calculated for each day of the month for the period from 3/10/1999 till 11/28/2011: go long on close, exit on open next day.

day	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	Total
1	3.44	5.61	4.59	-0.84	1.10	0.41	2.05	-0.01	-1.84	-0.55	0.30	5.20	0.52	19.96
2	-0.89	8.55	-0.67	-1.39	4.70	1.70	-0.18	0.18	0.95	3.72	-2.90	3.67	-1.12	16.28
3	8.49	7.11	-8.62	-3.95	5.04	0.89	0.64	-0.97	0.62	-1.08	3.16	1.17	0.48	12.93
4	2.85	-0.53	-0.99	-0.51	-1.13	0.61	1.17	1.52	-0.24	-2.39	4.52	-1.47	-2.36	1.05
5	4.08	-9.47	7.98	0.04	-2.30	1.58	-0.71	0.13	-0.92	-2.88	-0.87	0.76	-0.13	-2.68
6	-0.22	3.09	-1.94	2.78	1.91	-2.67	1.96	-0.20	1.53	-4.72	0.96	-0.68	-0.93	0.84
7	3.13	7.31	-5.22	-0.18	3.61	0.57	0.87	-0.67	-2.09	1.24	-1.11	1.48	3.04	11.97
8	-0.52	7.86	0.27	4.04	-1.64	-0.23	0.19	-0.80	0.66	1.52	2.35	0.81	-3.57	11.00
9	3.21	-0.48	-2.76	-4.46	3.11	-0.24	0.32	2.60	-1.74	0.88	2.46	4.03	-1.48	5.43
10	1.55	-3.11	2.12	4.07	-2.85	-0.47	-0.21	-0.55	-1.18	-1.30	1.68	5.39	-1.73	3.42
11	5.35	2.18	6.64	2.18	1.73	0.32	1.20	-1.51	1.22	-3.80	0.77	-5.18	0.75	11.87
12	-1.30	5.38	-4.01	-2.02	-0.44	-0.91	-0.43	-0.13	1.28	-1.94	-0.92	-3.26	0.16	-8.55
13	1.43	-0.22	4.05	-3.67	2.48	3.01	-0.33	-0.03	2.25	5.26	-0.67	2.06	1.49	17.09
14	0.35	6.88	-2.63	0.97	2.19	-1.47	1.44	1.26	0.61	4.04	-0.90	0.88	1.30	14.90
15	-0.64	-3.92	2.38	3.29	5.50	-0.41	0.58	3.21	0.43	-4.68	-1.28	1.59	-2.18	3.83
16	4.32	2.58	-8.88	-7.30	2.16	2.16	-0.42	1.60	-0.33	0.56	4.30	-1.24	-2.42	-2.91
17	1.34	-3.08	-6.13	13.72	-5.43	1.58	1.07	-3.15	2.43	-6.64	-5.12	2.92	0.88	-5.62
18	-0.81	-1.54	6.32	-6.07	2.89	-0.96	-0.38	0.30	0.36	6.48	2.50	1.74	-4.09	6.76
19	2.53	2.13	7.58	-6.03	0.04	2.14	1.15	0.57	0.28	7.75	0.73	-1.87	-1.11	15.89
20	1.24	-4.29	-6.68	-0.60	-0.69	0.78	-1.31	1.69	2.95	-1.11	-2.21	-3.03	1.91	-11.35
21	-0.06	-3.64	-6.33	-0.37	3.47	2.63	2.73	-1.36	1.06	1.17	1.47	2.79	3.65	7.22
22	1.44	3.11	2.66	-5.47	-0.66	-0.75	-1.02	-1.51	0.61	-7.41	-2.55	-0.25	-2.09	-13.86
23	4.86	-0.18	-1.25	-2.50	0.88	0.53	0.46	0.34	2.64	-1.98	4.82	-1.75	-3.71	3.20
24	-0.03	3.28	5.07	-5.49	-3.84	1.40	0.35	0.11	-0.24	-3.33	0.80	0.82	1.03	-0.08
25	2.25	2.77	-1.94	0.30	-0.91	0.23	1.04	1.79	1.08	4.81	-0.48	-1.54	-1.26	8.11
26	2.28	2.79	2.61	0.14	1.05	-0.89	1.49	0.74	3.30	-6.33	0.51	0.39	0.74	8.78
27	4.39	0.94	2.16	4.01	-2.25	1.06	-0.46	0.32	-1.54	-2.28	-6.39	2.01	4.23	6.21
28	2.96	-1.93	-0.07	2.24	1.85	0.32	1.13	-0.47	0.44	3.76	2.94	0.84	3.05	17.09
29	1.89	2.45	-0.02	1.15	1.17	2.36	0.69	1.39	1.89	-2.90	0.16	0.93	1.52	12.67
30	4.63	-3.94	-1.13	-2.74	3.38	-0.49	-0.45	0.13	-0.17	2.97	1.74	-1.90	-0.88	1.15
31	3.56	2.64	2.85	0.29	-2.10	0.28	1.57	0.55	2.01	-3.24	0.21	-0.93	0.66	8.39

Query used:


--1
select [day], [1999], [2000], [2001], [2002], [2003], [2004], [2005], [2006],[2007], [2008], [2009], [2010], [2011], [2012] Total into #t
from 
	(	select cast((t.o- t_1.c)/t_1.c * 100 as decimal(10,2))  pl, DATEPART(d, t.d) [day], YEAR(t.d) y 
		from qqq t
		join qqq t_1 on t_1.id = t.id -1
		) p
pivot
(
sum(pl)
for y in ([1999], [2000], [2001], [2002], [2003], [2004], [2005], [2006],[2007], [2008], [2009], [2010], [2011],[2012])
) r	
order by [day]

--2
update t
set t.Total = p.pl
from #t t
join  (
	select
      datepart(d, t.d) [day], 
      cast(sum((t.o- t_1.c)/t_1.c * 100) as decimal(10,2)) 'PL'  
      from qqq t
      join qqq t_1 on t_1.id = t.id -1
      group by   datepart(d, t.d)
      ) p on T.[day] = p.[day]

--3
DECLARE @HeaderList varchar(max)
SELECT @HeaderList = COALESCE(@HeaderList + '', '') +    name 
from tempdb.sys.columns where object_id = object_id('tempdb..#t') order by column_id
select '' + @HeaderList + ''


--4
DECLARE @SelectList varchar(max)
SELECT @SelectList = COALESCE(@SelectList + '+''''+', '') +    'CAST([' + name + '] AS varchar(50))'
from tempdb.sys.columns where object_id = object_id('tempdb..#t') order by column_id
--select '' + @SelectList + ''
declare @sql varchar(max)

select @sql = 'select ''''+' + @SelectList + '+'''' from #t'

exec (@sql)



3 and 4 can be replaced with:

DECLARE @HeaderList varchar(max), @head varchar(1000)
SELECT @HeaderList = COALESCE(@HeaderList + '', '') +    name 
from tempdb.sys.columns where object_id = object_id('tempdb..#t') order by column_id
select @head = '' + @HeaderList + ''

DECLARE @SelectList varchar(max)
SELECT @SelectList = COALESCE(@SelectList + '+''''+', '') +    'CAST([' + name + '] AS varchar(50))'
from tempdb.sys.columns where object_id = object_id('tempdb..#t') order by column_id

declare @sql varchar(max)
select @sql = 'select ''' + @head + ''' union all select ''''+' + @SelectList + '+'''' from #t'
exec (@sql)