SQL Server Performance

Reporting Question - pivot table?

Discussion in 'Analysis Services/Data Warehousing' started by cbtoolkit, Jan 7, 2005.

  1. cbtoolkit New Member

    I'm not sure where I would post this so I will start here.

    I have a database that tracks calls made to customers during the day. Each call generates a history log record with the customID, the status of the call, the datetime, and a number tracking how many times this customer has been contacted in the month.

    Tables and fields to consider:
    tblMarketArea - tracks market areas
    tblCallList - tracks customer information and relates to the market area
    tblCallHist - the historical log file
    tblStatus - the status code and status description.

    I have a view that groups the Market Area (tblMarketArea), Call Count (tblCallHist), The Status code (tblCallHist), the Status Name, and then counts on CallHist_uno (tblCallHist).

    This works fine and I can report on Market areas and now how many calls fall under a certain status. Here is my dilemma.

    I want a report showing

    Market Area | Call Count | Status 1 | Status 2 | Status 3 | status 4

    I want a column for every status code in the tblStatus regardless of whether there are any numbers in that status. I want to see, for a given market area, the calls completed and the status of those calls, with a new row for that market area for accounts touched/called more than once.

    Thanks.




    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com

Share This Page