How to Dynamically roll-up column results

Question

Post reply

How to Dynamically roll-up column results

shani19831

SSCommitted

Points: 1737

November 9, 2019 at 3:05 pm

Go to Answer

#3696478

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

pietlinden

SSC Guru

Points: 63500
More actions
November 9, 2019 at 8:42 pm
Answer
#3696501

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;

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

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

as_1234 SSCrazy Points: 2859 More actions · Answer 1

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 More actions · Answer 2

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

shani19831 SSCommitted Points: 1737 More actions · Answer 3

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

This has solved my issue.

shani19831 SSCommitted Points: 1737 More actions · Answer 4

shani19831

SSCommitted

Points: 1737

November 10, 2019 at 7:10 am

#3696522

Works fine as well.. thanks..

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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

shani19831 SSCommitted Points: 1737 More actions · Answer 6

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: 1004312 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

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