jgaull
asked on
Crosstab query
I need to create a query that will show the last N months of data for each person in the database. Here's a simplied version of what my table would look like:
create table test_table (
personid int,
value int,
cdate date)
personid value cdate
-------- ----- ----------
1 100 08/01/2003
1 200 09/01/2003
1 300 10/01/2003
2 400 08/01/2003
2 500 09/01/2003
2 600 10/01/2003
Let's say that I just want to show the last 3 months of data. Here's what I would like the query to return:
personid 08-2003 09-2003 10-2003
-------- ------- ------- -------
1 100 200 300
2 400 500 600
I actually don't even need the columns to read like shown. They could be like this:
personid date1 date2 date3
-------- ----- ----- -----
Anyone have any ideas???
create table test_table (
personid int,
value int,
cdate date)
personid value cdate
-------- ----- ----------
1 100 08/01/2003
1 200 09/01/2003
1 300 10/01/2003
2 400 08/01/2003
2 500 09/01/2003
2 600 10/01/2003
Let's say that I just want to show the last 3 months of data. Here's what I would like the query to return:
personid 08-2003 09-2003 10-2003
-------- ------- ------- -------
1 100 200 300
2 400 500 600
I actually don't even need the columns to read like shown. They could be like this:
personid date1 date2 date3
-------- ----- ----- -----
Anyone have any ideas???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
from test_table d0
join ( select personid, value from test_table where cdate=today()-1 month ) d1
on d1.personid=d0.personid
join ( select personid, value from test_table where cdate=today()-2 month ) d2
on d0.personid=d2.personid