How to Dynamically roll-up column results

  • shani19831

    SSCommitted

    Points: 1737

    I have a table that contains data as per below script.

    What i would like to archive, is the previous months amount. For monthly policy, So that the data can look something like this:

    data

    CREATE TABLE [dbo].[#dummy](
    [Policy] [nvarchar](255) NULL,
    [BatchMonth] [nvarchar](255) NULL,
    [Amount] [float] NULL
    ) ON [PRIMARY]

    INSERT INTO [dbo].[#dummy]
    ([Policy]
    ,[BatchMonth]
    ,[Amount])
    VALUES
    ('A', 7, 100)
    ,('A', 8, 205)
    ,('A', 9, 350)
    ,('A', 10, 470)
    ,('A', 11, 590)
    GO
    select * from #dummy
    drop table #dummy
  • as1981

    SSCrazy

    Points: 2392

    I think this might work:

    select #dummy.Policy,#dummy.batchmonth, #dummy.Amount,isnull(#dummy2.Amount,0) as previousbatchmonth from #dummy left outer join #dummy #dummy2
    on #dummy.Policy=#dummy2.Policy
    and ((cast(#dummy.BatchMonth as int)-cast (#dummy2.BatchMonth as int )=1)
    or (#dummy.BatchMonth=12 and #dummy2.BatchMonth=1))

    Others may find fault with this solution or have better solutions. In particular the last line is a workaround. You may also wish to include an order by.

    This solution will have problems if there is no data for a particular month. Will you always have data for each month?

     

  • shani19831

    SSCommitted

    Points: 1737

    Yes each month will have data. I will let know if that works

  • pietlinden

    SSC Guru

    Points: 62607

    Unless you're using a super old version of SQL Server, this should work fine:

    SELECT [Policy]

    , BatchMonth

    , Amount

    , LAG(Amount,1) OVER (PARTITION BY [Policy] ORDER BY BatchMonth) AS PrevMonthAmount

    FROM #dummy

    ORDER BY [Policy], BatchMonth;

  • shani19831

    SSCommitted

    Points: 1737

    Its SQL Server 2016. Thanks, i used your query as the results are consistent.

    This has solved my issue.

  • shani19831

    SSCommitted

    Points: 1737

    Works fine as well.. thanks..

  • Jeff Moden

    SSC Guru

    Points: 994867

    Are the datatypes for the sample table in the original post representative of what you have for the real table in production?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • shani19831

    SSCommitted

    Points: 1737

    Hi Jeff

    Yes the datatype is represented that way within the DB.

    I'm also worried about Policy and BatchMonth. I will talk to the Database Administrator as Policy should be Char while BatchMonth should date. Thank you for your help guys..

  • Jeff Moden

    SSC Guru

    Points: 994867

    shani19831 wrote:

    Hi Jeff

    Yes the datatype is represented that way within the DB.

    I'm also worried about Policy and BatchMonth. I will talk to the Database Administrator as Policy should be Char while BatchMonth should date. Thank you for your help guys..

    That means that a Developer designed the table with Visual Studio or somesuch and knows or cares little about right sizing.  if you talk to them about it, they'll probably misquote Knuth about the root of all evil, etc.

    As you know, those datatypes really should be fixed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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