How to Dynamically roll-up column results
-
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:
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 -
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?
-
Yes each month will have data. I will let know if that works
-
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;
-
Its SQL Server 2016. Thanks, i used your query as the results are consistent.
This has solved my issue.
-
Works fine as well.. thanks..
-
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) -
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..
- 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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply