Home Forums Reporting Services Reporting Services 2008 Development How to show results from 2 timeframes? RE: How to show results from 2 timeframes?

  • July 28, 2013 at 5:23 pm

    #1636505

    pluto1415 (7/26/2013)

    We would like to generate a report that shows the following:

    SalesPersonA - Sales for Jan-June2012 - Sales for Jan-June2013

    SalesPersonB - Sales for Jan-June2012 - Sales for Jan-June2013

    SalesPersonC - Sales for Jan-June2012 - Sales for Jan-June2013

    I can write 2 datasets that find "Sales for Jan-June2012" and "Sales for Jan-June2013" - but I can't figure out how to display the results in one table, sorted by Sales Person...

    Then we will want to calculate the diff between 2012 & 2013, but I think I can write the expression for that.

    SELECT

    result.SalesPersonID,

    result.SalesPerson,

    result.[2013 Sales] - result.[2012 Sales] AS [Difference]

    FROM

    (

    SELECT

    Y12.SalesPersonID,

    Y12.SalesPerson,

    SUM(Y12.Sales) OVER (PARTITION BY Y12.SalesPersonID) AS [2012 Sales],

    SUM(Y13.Sales) OVER (PARTITION BY Y13.SalesPersonID) AS [2013 Sales],

    FROM

    [Sales for Jan-June2012] AS Y12

    INNER JOIN

    [Sales for Jan-June2013] AS Y13

    ON Y12.SalesPersonID = Y13.SalesPersonID

    ) result

    ORDER BY

    result.SalesPerson