Hi,
This is my table and data
CREATE TABLE [dbo].[Inquiry_GL_BudgetComparison_001_Detail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Batch_Id] [uniqueidentifier] NULL,
[company_code] [smallint] NULL,
[MMonth] [smallint] NULL,
[MMonth_Name] [varchar](50) NULL,
[YYear] [int] NULL,
[ref1] [varchar](200) NULL,
[ref2] [varchar](200) NULL,
[level1_code] [int] NULL,
[level2_code] [varchar](50) NULL,
[gl_code] [varchar](50) NULL,
[trans_date] [datetime] NULL,
[descrp] [varchar](200) NULL,
[AG_Descrp] [varchar](200) NULL,
[GL_Descrp] [varchar](200) NULL,
[thisAmt] [decimal](16, 2) NULL,
[thisBalance] [decimal](16, 2) NULL,
CONSTRAINT [PK_Inquiry_GL_BudgetComparison_001_Detail] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Inquiry_GL_BudgetComparison_001_Detail] ON
GO
This is the physical look
See thisBalance column. I want to get thisBalance value. How to query to get return result as following
Id | descrp | thisAmt | thisBalance
----------------------------------------------------------
1 Item 11 6215.03 6215.03
2 Item 12 149294.34 155509.37
3 Item 13 198778.89 354288.26
4 Item 14 154159.96 508448.22
5 Item 15 15000.00 523448.22
....
....
Id = 1 . thisBalance = 6215.03
Id = 2. thisBalance = 6215.03 + 149294.34
Id = 3. thisBalance = 6215.03 + 149294.34 + 198778.89
Id = 4. thisBalance = 6215.03 + 149294.34 + 198778.89 + 154159.96
Id = 5. thisBalance = 6215.03 + 149294.34 + 198778.89 + 154159.96 + 15000.00
and so on
Please help
For a running total - you can use: thisBalance = SUM(thisAmt) OVER(PARTITION BY Batch_Id ORDER BY Id)
If you want to break out the totals even further, add those to the partition.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2023 at 4:32 pm
Hello Sir,
It is masterpiece
September 17, 2023 at 7:05 pm
For a running total - you can use: thisBalance = SUM(thisAmt) OVER(PARTITION BY Batch_Id ORDER BY Id)
If you want to break out the totals even further, add those to the partition.
I recommend that you ALWAYS include the window (in this case ROWS UNBOUNDED PRECEDING. If you don't include it, you can get unexpected results, although I've only ever seen this with LAST_VALUE().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply