//Edited to (hopefully) simplify.

Here is what I currently have in my command.

    select
    "ItemTable"."ItemNum"
    , [1] MM01
    , [2] MM02
    , [3] MM03
    , ...
    from (
    select
    "ItemTable"."ItemNum"
    , DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
    , "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
    from your_table
    ) as source
    pivot
    (
    sum(NetQty) For months_ago in ([1], [2], [3], ...)
    ) as PivotTable

Here is a pic comparing what I have (with the above code) and what I want:



How do I change this to get what I want?

Edited by SOSteppenwolf - 21 Oct 2014 at 7:19am

Thanks for the WHERE tip. I edited my initial post for clarity. Please look at if you get the chance.

I am trying to do it all in SQL. I had initially tried to build it the "traditional" way but it quickly became too big due to the number of records and hundreds of formulas I was going to need.

Actually I'm able to make the report rolling by using

    DATEADD(m, -01, GETDATE()-day(GETDATE()-1)) AS "MM01"

for the header and

    SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -01, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -00, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."UnitSalesQty" - "SalesTable"."UnitReturnsQty" ELSE NULL END) AS "NetQty01

to pull the sales units. Only issue is that I have to take the above x36 plus all the other commands I'll need. That's why I wanted to go the pivot route.

Thanks for taking a stab at it anyhow.

Edited by SOSteppenwolf - 23 Oct 2014 at 9:42am

The CT is a novel approach I hadn't thought of. The report is going to require a lot more formulas, though and I'm not sure a CT is conducive to that. I've finally been able to put together a dynamic pivot table too. So, now I've got a few options to work with and have kind of got out of my stalemate.

Thanks,