Thanks for the link, webfred, it's good read! I believe that I could make it work, but maybe there is an easier way?
So, here is a simplified way to look at my problem. I have a table as below, where I want to summarize by id several variables (C1-C8, L1-L8). Now, I would like to do it for each of several years back (3 for now, but let's say n in general), which is where my "ldate between effdate-10000*@n and expdate-10000*@n" is needed. How can I do this with a single procedure that creates a table with unique id and C1_P1, C1_P2,..., C1_Pn, L1_P1...L1_Pn as field names?
Hope this makes sense.
create table dbo.mytable (id int, effdate int, expdate int, ldate int, C1 int, C2 int)
insert into dbo.mytable
(id, effdate, expdate, ldate, C1, C2)
SELECT '29','20101017','20110417','20110305','0','1' UNION ALL
SELECT '29','20101017','20110417','20110305','0','1' UNION ALL
SELECT '36','20091128','20101128','20100916','1','0' UNION ALL
SELECT '61','20090526','20100526','20090810','0','1' UNION ALL
SELECT '61','20090526','20100526','20090810','0','1' UNION ALL
SELECT '35','20070405','20071005','20070903','1','0' UNION ALL
SELECT '61','20090705','20100105','20090919','0','1' UNION ALL
SELECT '61','20090705','20100105','20090919','0','1' UNION ALL
SELECT '67','20080719','20090119','20081017','0','1' UNION ALL
SELECT '56','20090516','20100516','20090914','0','1' UNION ALL
SELECT '29','20100503','20101103','20100726','0','1'
Procedure with parameter
Posted - 11/28/2012 : 09:07:50
