Calculating Sales History for Months/Years

  • I have a table -

    TrnDate, TrnYear, TrnMonth, StockCode, InvoiceQty

    The TrnYear and TrnMonth are the Financial Periods and dont always tie back to the TrnDate, Month/Year.

    for example - 31/05/2013 - might fall into Period 6 / 2013

    What I am after is a script that will calculate historic InvoiceQty for ;

    StockCode, MonthToDate, LastMonth, Previous6Months, Previous12Months, Previous18Months, Previous24Months, Previous36Months

    the hard bit is when you want to go back 6 months and the year changes ....

    hopefully this makes sense ?

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The easy bit -

    SELECT

    StockCode,

    SUM(CASE TrnYear WHEN '2009' THEN InvoiceQty ELSE 0 END) AS Sales2009,

    SUM(CASE TrnYear WHEN '2010' THEN InvoiceQty ELSE 0 END) AS Sales2010,

    SUM(CASE TrnYear WHEN '2011' THEN InvoiceQty ELSE 0 END) AS Sales2011,

    SUM(CASE TrnYear WHEN '2012' THEN InvoiceQty ELSE 0 END) AS Sales2012,

    SUM(CASE TrnYear WHEN '2013' THEN InvoiceQty ELSE 0 END) AS Sales2013

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • SteveEClarke (6/3/2013)


    The easy bit -

    SELECT

    StockCode,

    SUM(CASE TrnYear WHEN '2009' THEN InvoiceQty ELSE 0 END) AS Sales2009,

    SUM(CASE TrnYear WHEN '2010' THEN InvoiceQty ELSE 0 END) AS Sales2010,

    SUM(CASE TrnYear WHEN '2011' THEN InvoiceQty ELSE 0 END) AS Sales2011,

    SUM(CASE TrnYear WHEN '2012' THEN InvoiceQty ELSE 0 END) AS Sales2012,

    SUM(CASE TrnYear WHEN '2013' THEN InvoiceQty ELSE 0 END) AS Sales2013

    This?

    SELECT

    StockCode,

    SUM(CASE TrnYear WHEN '2009' THEN InvoiceQty ELSE 0 END) AS Sales2009,

    SUM(CASE TrnYear WHEN '2010' THEN InvoiceQty ELSE 0 END) AS Sales2010,

    SUM(CASE TrnYear WHEN '2011' THEN InvoiceQty ELSE 0 END) AS Sales2011,

    SUM(CASE TrnYear WHEN '2012' THEN InvoiceQty ELSE 0 END) AS Sales2012,

    SUM(CASE TrnYear WHEN '2013' THEN InvoiceQty ELSE 0 END) AS Sales2013

    FROM

    dbo.MyTable -- put your actual table name here

    GROUP BY

    StockCode

    ORDER BY -- If you also want it ordered in this order as well, the GROUP BY does not ensure this

    StockCode

  • Completely untested due to the lack of sample data, but it can give you an idea.

    DECLARE @test-2TABLE(

    TrnDatedate,

    TrnYearint,

    TrnMonthint,

    StockCodeint,

    InvoiceQty int);

    DECLARE @Date date;

    WITH CTE AS(

    SELECT DISTINCT

    TrnYear,

    TrnMonth,

    ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn

    FROM @test-2

    WHERE TrnDate <= @Date)

    SELECT StockCode,

    SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,

    SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,

    SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,

    SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,

    SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,

    SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,

    SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months

    FROM @test-2 t

    JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear

    GROUP BY StockCode

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Okay ... table and data.

    The results I would expect are .

    StockCode, MonthToDate, LastMonth, Previous6Months, Previous12Months, Previous18Months, Previous24Months, Previous36Months

    38-1446-9912, 31, 98, 253, 253, 253, 253, 253

    DROP TABLE #mytemptable

    create table #mytemptable

    ( TrnDatedatetime,

    InvoiceQtydecimal(10,2),

    TrnMonth decimal(4),

    TrnYeardecimal(4),

    StockCodechar(30)

    )

    insert into #mytemptable

    (TrnDate, InvoiceQty, TrnMonth, TrnYear, StockCode)

    select '2013-04-10',40.000,4,2013,'38-1446-9912' union all

    select '2013-04-25',75.000,4,2013,'38-1446-9912' union all

    select '2013-05-09',5.000,5,2013,'38-1446-9912' union all

    select '2013-05-16',30.000,5,2013,'38-1446-9912' union all

    select '2013-05-22',50.000,5,2013,'38-1446-9912' union all

    select '2013-05-30',30.000,6,2013,'38-1446-9912' union all

    select '2013-05-30',1.000,6,2013,'38-1446-9912' union all

    select '2013-04-16',1.000,4,2013,'38-1446-9912' union all

    select '2013-04-16',3.000,4,2013,'38-1446-9912' union all

    select '2013-04-25',1.000,4,2013,'38-1446-9912' union all

    select '2013-04-26',1.000,4,2013,'38-1446-9912' union all

    select '2013-05-07',1.000,5,2013,'38-1446-9912' union all

    select '2013-05-17',2.000,5,2013,'38-1446-9912' union all

    select '2013-05-24',1.000,5,2013,'38-1446-9912' union all

    select '2013-05-24',1.000,5,2013,'38-1446-9912' union all

    select '2013-05-22',3.000,5,2013,'38-1446-9912' union all

    select '2013-04-18',3.000,4,2013,'38-1446-9912' union all

    select '2013-05-09',4.000,5,2013,'38-1446-9912' union all

    select '2013-05-09',1.000,5,2013,'38-1446-9912'

    DECLARE @Date DateTime;

    set @Date = GetDate();

    WITH CTE AS(

    SELECT DISTINCT

    TrnYear,

    TrnMonth,

    ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn

    FROM #mytemptable

    WHERE TrnDate <= @Date)

    SELECT StockCode,

    SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,

    SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,

    SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,

    SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,

    SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,

    SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,

    SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months

    FROM #mytemptable t

    JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear

    GROUP BY StockCode

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • see if this helps....

    DECLARE @Date DateTime;

    set @Date = GetDate();

    /* see changes made to the CTE */

    WITH CTE AS

    (

    SELECT

    TrnYear,

    TrnMonth,

    ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn

    FROM (

    SELECT DISTINCT

    TrnYear,

    TrnMonth

    FROM #mytemptable

    WHERE TrnDate <= @Date) dt

    )

    SELECT StockCode,

    SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,

    SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,

    SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,

    SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,

    SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,

    SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,

    SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months

    FROM #mytemptable t

    JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear

    GROUP BY StockCode

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • hmmm....take a look at this data set....I don't think you are going to get what you expect.

    suggest that you read up on using a calendar table instead of the CTE for your dates.

    create table #mytemptable

    ( TrnDate datetime,

    InvoiceQty decimal(10,2),

    TrnMonth decimal(4),

    TrnYear decimal(4),

    StockCode char(30)

    )

    insert into #mytemptable

    (TrnDate, InvoiceQty, TrnMonth, TrnYear, StockCode)

    select '2013-04-10',40.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-04-25',75.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-05-09',5.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-16',30.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-22',50.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-30',30.000 ,6 ,2013 ,'38-1446-9912' union all

    select '2013-05-30',1.000 ,6 ,2013 ,'38-1446-9912' union all

    select '2013-04-16',1.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-04-16',3.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-04-25',1.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-04-26',1.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-05-07',1.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-17',2.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-24',1.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-24',1.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-22',3.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-04-18',3.000 ,4 ,2013 ,'38-1446-9912' union all

    select '2013-05-09',4.000 ,5 ,2013 ,'38-1446-9912' union all

    select '2013-05-09',1.000 ,5 ,2013 ,'38-1446-9912' union all

    /* added another date period this will not give you the results I think you are expecting*/

    select '2000-05-09',1.000 ,5 ,2000 ,'99-9999-9999'

    /* suggest that instead of using the CTE method..that you use a calendar table */

    DECLARE @Date DateTime

    set @Date = GetDate();

    /* see changes made to the CTE */

    WITH CTE AS

    (

    SELECT

    TrnYear,

    TrnMonth,

    ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn

    FROM (

    SELECT DISTINCT

    TrnYear,

    TrnMonth

    FROM #mytemptable

    WHERE TrnDate <= @Date) dt

    )

    SELECT StockCode,

    SUM(CASE WHEN rn = 1 THEN InvoiceQty END) AS MonthToDate,

    SUM(CASE WHEN rn = 2 THEN InvoiceQty END) AS LastMonth,

    SUM(CASE WHEN rn <= 6 THEN InvoiceQty END) AS Previous6Months,

    SUM(CASE WHEN rn <= 12 THEN InvoiceQty END) AS Previous12Months,

    SUM(CASE WHEN rn <= 18 THEN InvoiceQty END) AS Previous18Months,

    SUM(CASE WHEN rn <= 24 THEN InvoiceQty END) AS Previous24Months,

    SUM(CASE WHEN rn <= 36 THEN InvoiceQty END) AS Previous36Months

    FROM #mytemptable t

    JOIN CTE ON t.TrnMonth = CTE.TrnMonth AND t.TrnYear = CTE.TrnYear

    GROUP BY StockCode

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • You're also going to need a reference (either for yourself but definitely for us) of what actual dates fall into which trnMonths and trnYears. We don't have a way to make a comparison without that data.

  • Yes - I understand - but that is what the TrnYear and TrnMonth fields are.

    so forget the TrnDate Fields -

    I suppose something like ....

    Max TrnYear, TrnMonth to give 2013, 06 - this would give current

    Max TrnYear, TrnMonth -1 to give 2013, 05 - this would give previous month

    the problem comes when it rolls back over a year !?

    does that make sense ?

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • SteveEClarke (6/6/2013)


    Yes - I understand - but that is what the TrnYear and TrnMonth fields are.

    so forget the TrnDate Fields -

    I suppose something like ....

    Max TrnYear, TrnMonth to give 2013, 06 - this would give current

    Max TrnYear, TrnMonth -1 to give 2013, 05 - this would give previous month

    the problem comes when it rolls back over a year !?

    does that make sense ?

    Are TrnMonth and TrnYear based off of TrnDate and if so how?

  • Hello,

    I think your answer is Previous6Months..

  • Thinking about this over the weekend - I think as per the previous suggestion - I need to create a Fiscal Calender with counts for the months/years.

    so the table would look like ;

    YearMonth count

    200901 001

    200902 002

    200903 003

    etc..

    201306 054

    201307 055

    then just take the (TrnDate ) which would give me the TrnYear/TrnMonth - then using calulations to go backwards - so 055 - 36 = 019 for Previous36.

    I know where I am going now -

    Thanks to all for their input.

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

Viewing 13 posts - 1 through 12 (of 12 total)

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