TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Akhter HUssain
720
1.3k
101.3k
Balance is not calculating correct ,if date filter is applied?
Nov 24 2020 11:38 AM
I am retrieving Data between two date, but Balance is not calculating correct,
Below data with query.
Create
table
#tbl_Receivable (Rec_ID
int
,Customer_ID
int
,Rec_Date
date
,Rec_Amount
varchar
(50),Inv_type
varchar
(50));
INSERT
INTO
#tbl_Receivable
VALUES
(111,1,
'2020-03-06'
,5000,
'Payable'
),
(112,1,
'2020-03-07'
,2000,
'Received'
),
(113,1,
'2020-03-08'
,1000,
'Payable'
),
(114,1,
'2020-03-08'
,2000,
'Payable'
),
(115,1,
'2020-03-09'
,4000,
'Received'
)
;
with
Q
as
(
select
Rec_ID, Customer_ID,
''
as
[ ], Rec_Date, Rec_Amount
as
Payable, 0
as
Received
from
#tbl_Receivable
where
Inv_type =
'Payable'
union
all
select
Rec_ID, Customer_ID,
''
, Rec_Date, 0, Rec_Amount
from
#tbl_Receivable t1
where
Inv_type =
'Received'
union
all
select
0, Customer_ID,
'Opening'
, DATEADD(d, -1,
MIN
(Rec_Date)), 0, 0
from
#tbl_Receivable
group
by
Customer_ID
)
select
Customer_ID, [ ], Rec_Date, Payable, Received,
sum
(Payable - Received) over (partition
by
Customer_ID
order
by
Rec_Date, Rec_ID)
as
Balance
from
Q
where
Rec_Date
between
'2020-03-08'
and
'2020-03-09'
order
by
Customer_ID, Rec_Date, Rec_ID
Drop
table
#tbl_Receivable
Without Date Filter
Create
table
#tbl_Receivable (Rec_ID
int
,Customer_ID
int
,Rec_Date
date
,Rec_Amount
varchar
(50),Inv_type
varchar
(50));
INSERT
INTO
#tbl_Receivable
VALUES
(111,1,
'2020-03-06'
,5000,
'Payable'
),
(112,1,
'2020-03-07'
,2000,
'Received'
),
(113,1,
'2020-03-08'
,1000,
'Payable'
),
(114,1,
'2020-03-08'
,2000,
'Payable'
),
(115,1,
'2020-03-09'
,4000,
'Received'
)
;
with
Q
as
(
select
Rec_ID, Customer_ID,
''
as
[ ], Rec_Date, Rec_Amount
as
Payable, 0
as
Received
from
#tbl_Receivable
where
Inv_type =
'Payable'
union
all
select
Rec_ID, Customer_ID,
''
, Rec_Date, 0, Rec_Amount
from
#tbl_Receivable t1
where
Inv_type =
'Received'
union
all
select
0, Customer_ID,
'Opening'
, DATEADD(d, -1,
MIN
(Rec_Date)), 0, 0
from
#tbl_Receivable
group
by
Customer_ID
)
select
Customer_ID, [ ], Rec_Date, Payable, Received,
sum
(Payable - Received) over (partition
by
Customer_ID
order
by
Rec_Date, Rec_ID)
as
Balance
from
Q
--where Rec_Date between '2020-03-08' and '2020-03-09'
order
by
Customer_ID, Rec_Date, Rec_ID
Drop
table
#tbl_Receivable
Ouput is
as You can see in 2020-03-07 Balance is 3000,
Reply
Answers (
1
)
Extracting string after and before a character '-' or '<'
Store different query results into different Sheets of the same excel