Reputation: 285
In my query I'm Selecting row by row closing balance. It's consume 100% CPU while executing is sql server 2014. Here is my query:
;WITH summary(id,reference_id,entry_date,particular,remarks,debit,credit,balance)AS(
SELECT
id,
reference_id,
entry_date,
particular,
remarks,
debit,
credit,
(credit-debit)+(SELECT ISNULL(SUM(l.credit-l.debit) ,0) FROM member_transaction l WHERE l.entry_date<a.entry_date AND l.member_id=@mId AND is_succeed=1 AND isnull(l.reference_id,0) NOT IN(SELECT user_reference_id FROM recharge_request WHERE status='Failure'))AS balance
FROM member_transaction a
WHERE member_id=@mId AND is_succeed=1
AND isnull(reference_id,0) NOT IN(SELECT user_reference_id FROM recharge_request WHERE status='Failure')),
openingbalance(
id,
reference_id,
entry_date,
particular,
remarks,
debit,
credit,
balance
)AS(SELECT TOP 1 0,'','','OPENING BALANCE','',0,0,balance FROM summary WHERE entry_date<'2017/03/10' ORDER BY entry_date DESC
)SELECT * FROM openingbalance UNION SELECT * FROM summary ORDER BY entry_date DESC
Is there any other way to calculate row by row closing balance on every transaction? Please help me to solve this problem.
HERE is table structure:
CREATE TABLE [member_transaction](
[id] [int] IDENTITY(1,1) NOT NULL,
[member_id] [int] NULL,
[t_type] [varchar](50) NULL,
[debit] [decimal](12, 2) NOT NULL,
[credit] [decimal](12, 2) NOT NULL,
[particular] [varchar](100) NULL,
[remarks] [varchar](150) NULL,
[reference_id] [varchar](50) NULL,
[entry_date] [datetime] NOT NULL,
[is_succeed] [bit] NOT NULL
)
CREATE TABLE [recharge_request](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[mobile_no] [varchar](50) NULL,
[amount] [decimal](12, 0) NULL,
[user_reference_id] [varchar](50) NULL,
[uid] [int] NULL,
[rdate] [datetime] NOT NULL,
[status] [varchar](50) NOT NULL
)
Upvotes: 0
Views: 109
Reputation: 12317
Assuming you're in SQL Server 2012+, you should try something like this:
SELECT
id,
reference_id,
entry_date,
particular,
remarks,
debit,
credit,
sum(isnull(credit,0)-isnull(debit,0)) over (order by entry_date asc) AS balance
FROM
member_transaction a
WHERE
member_id=@mId AND
is_succeed=1 AND
not exist (select 1 FROM recharge_request r WHERE r.user_reference_id = a.reference_id and r.status='Failure')
If you want to fetch more than one member, then you should have partition by in the over part of the sum.
Upvotes: 1