Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: 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 :
    Code:
    CREATE TABLE Policy(
    	 Policyno VARCHAR(20)
    	,Inceptiondate DATETIME
    	,agentid VARCHAR(20))

    I want a report like this (dates in DD/MM/YYYY)
    Code:
    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
    Code:
         |   agentid
    -----------------
    date |  sum(policyno)
    -----------------
    which is giving a result like this
    Code:
    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
    Link2
    Last edited by rudra; 09-21-06 at 00:04.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd pre-process the pig into a scratch table, then crosstab that, something like:
    Code:
    --  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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Code:
    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....
    Pat you are great and I am grateful..
    Last edited by rudra; 09-21-06 at 01:30.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •