Subquery With Aggregates and Pivot

  • I having issues with a simple query.

    I need to GROUP BY in the OUTER Query by Company Name, AccountNumber, County, Territory, etc. I have my GROUP BY in the wrong place and get an error.:w00t:

    In the INNER Query I'm performing the following:

    (SELECT AccountNumber, COUNT(*) AS SalesCount

    FROM ProducerTracking

    WHERE SubmitYear = DATEPART(YEAR,DATEADD(year,-1,GETDATE()))

    GROUP BY AccountNumber) AS Cnt ON PT.AccountNumber = Cnt.AccountNumber

    The Submit Year returns 2010 and the SalesCount for the previous Year.

    I have a number of other columns that I need to Pivot on, CurrentYTD, CurrentMTD, PreviousMonth, PreviousWeek, etc.

    I would greatly appreciate any help.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I'd have to see the whole query to really nail this one down.

    Is it in the form of?:

    select CompanyName, AccountNumber, County, Territory, SalesCount

    from dbo.Accounts as PT

    left outer join

    (SELECT AccountNumber, COUNT(*) AS SalesCount

    FROM ProducerTracking

    WHERE SubmitYear = DATEPART(YEAR,DATEADD(year,-1,GETDATE()))

    GROUP BY AccountNumber) AS Cnt ON PT.AccountNumber = Cnt.AccountNumber

    Or is it more complex than that?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared,

    Thanks for your response.

    It is very complicated. Many of the headings are dynamic based on the current date, e,g, 2010 (Last Year), Year To Date (2011 YTD), Month to Date (MTD), Last Month (Sep 2011), etc.

    It is currently being performed in a Complicated Excel Macro.

    I was going to try and do it in SSIS but I have been informed that you can't do this in SSIS.

    I showed my boss and she is going to let me use the existing Very Complex Macro Code since I have created all but one other Sheet from SSIS.

    Thanks again.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Yeah, dynamic column-headers is a pain.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Many of the headings are dynamic based on the current date, e,g, 2010 (Last Year), Year To Date (2011 YTD), Month to Date (MTD), Last Month (Sep 2011), etc.

    I'll just mention this, though I'm sure you've considered it already.

    If the SSIS job could be made to populate a specific cell in the Excel spreadsheet with 'Run Date' or whatever (and it most certainly can), your column headings sound as if they could all be derived from this date via standard Excel formulae - no need for any macros.

    Just an idea.


  • The boss does not want to go with the Excel MAcros.

    Back to using SSIS.

    The following generates the column headers for the first two aggregates:

    SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE())) AS Year,

    CAST(DATEPART(YEAR,DATEADD(year,0,GETDATE())) AS VARCHAR(35)) + ' YTD' AS YTD

    YearYTD

    20102011 YTD

    I Still have:

    NOV 2011 MTD, OCT 2011, SEPT 2011, Week of OCT 30, Week of OCT 23, Week of OCT 16, Week of OCT 9

    Then I have to populate the Report & Column Headers.

    Write aggregates for each period inside an Outer Loop with AccountNumbers, CompanyName, etc.

    Thanks for all of the Help!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply