Crosstab problem help

Hi,
I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year.
Let me put it again...

I have a table policy :

CREATE TABLE Policy(
Policyno VARCHAR(20)
,Inceptiondate DATETIME
,agentid VARCHAR(20))

I want a report like this (dates in DD/MM/YYYY)

date 32001 32002 32003 32004 2006 2005
01/08/2006 3 1 11 1 16 12
02/08/2006 1 1 1 2 5 22
03/08/2006 1 1 1 1 4 3

Now the problem is I can get a report using crosstab like this in crystal report

| agentid
------
date | sum(policyno)
------

which is giving a result like this

date 32001 32002 32003 32004 2006/ToTal
01/08/2006 3 1 11 1 16
02/08/2006 1 1 1 2 5
03/08/2006 1 1 1 1 4
Total 4 3 13 4 25

But I can't bring the previous year aggregate column ,i.e 2005 in the above case.

So can you suggest me a way or direction to acheive that?

Any help would be really appreciated.
BTW,Pootle gave these links in my previous post...
link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817)
Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)I'd pre-process the pig into a scratch table, then crosstab that, something like:-- ptp 20060920 Sample for crosstab using prior year data

DROP TABLE s1
DROP TABLE s2
GO

CREATE TABLE s1 (
agent_id INT
, asof DATETIME
, policy INT IDENTITY (1001, 1)
)

INSERT INTO s1 (
agent_id, asof
) SELECT 32001, '2005-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2005-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2005-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2005-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2005-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2005-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2005-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2005-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2005-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2006-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2006-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2006-08-01'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2006-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2006-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2006-08-02'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32001, '2006-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32002, '2006-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()
UNION ALL SELECT 32003, '2006-08-03'
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m
WHERE m.n < 10 * Rand()

CREATE TABLE s2 (
agent_id INT
, asof DATETIME
, count_this INT
, count_prior INT
)

INSERT INTO s2 (
agent_id, asof, count_this, count_prior
) SELECT agent_id, asof, Count(*)
, (SELECT Count(*)
FROM s1 AS z
WHERE z.agent_id = a.agent_id
AND z.asof = DateAdd(year, -1, a.asof))
FROM s1 AS a
GROUP BY a.agent_id, a.asof

SELECT *
FROM s2
ORDER BY agent_id, asof-patP|||Note that the bulk of the code that I posted creates the test data, and that the test data is different almost every time the snippet is run... The real "solution" is actually the next to the last INSERT / SELECT statement that "pre-digests" the raw data into a summarized form that is easy to report.

An interesting side note is that this solution is tailored for the simplest case, as simple 1d to 2d crosstab, with a simple variable... The results are directly comparable for agent to themselves, year over year. They are NOT comparable for the agency year over year, and they may or may not be comparable agent to agent. Other crosstabs are possible, but they get wickedly difficult to manage in reporting tools, especially Crystal Reports.

-PatP|||INSERT INTO s2 (
agent_id, asof, count_this, count_prior
) SELECT agent_id, asof, Count(*)
, (SELECT Count(*)
FROM s1 AS z
WHERE z.agent_id = a.agent_id
AND z.asof = DateAdd(year, -1, a.asof))
FROM s1 AS a
GROUP BY a.agent_id, a.asof
--PatP

So short and concise, I can't believe it...:shocked:
Pat you are great and I am grateful..:rolleyes: