Mixing
How to generate column that calculate with its predecessor value in SQL Server
-1
For example
Amount ReceivedAmount PendingAmount
---------------------------------------
1000 400 600
600 400 200
How to I get (600) value in Amount column?
The table structure is
1. BillID,Amount,Date from table1
and
2. BillReceive,ReceiveDate,ReceivedAmount from table2
Received Amount Can be multiple time, I want pending amount from (Amount - SUM(ReceivedAmount))
sql-server sql-server-2008 edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
add a comment |
-1
For example
Amount ReceivedAmount PendingAmount
---------------------------------------
1000 400 600
600 400 200
How to I get (600) value in Amount column?
The table structure is
1. BillID,Amount,Date from table1
and
2. BillReceive,ReceiveDate,ReceivedAmount from table2
Received Amount Can be multiple time, I want pending amount from (Amount - SUM(ReceivedAmount))
sql-server sql-server-2008 edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
add a comment |
-1
-1
-1
1
For example
Amount ReceivedAmount PendingAmount
---------------------------------------
1000 400 600
600 400 200
How to I get (600) value in Amount column?
The table structure is
1. BillID,Amount,Date from table1
and
2. BillReceive,ReceiveDate,ReceivedAmount from table2
Received Amount Can be multiple time, I want pending amount from (Amount - SUM(ReceivedAmount))
sql-server sql-server-2008 edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
For example
Amount ReceivedAmount PendingAmount
---------------------------------------
1000 400 600
600 400 200
How to I get (600) value in Amount column?
The table structure is
1. BillID,Amount,Date from table1
and
2. BillReceive,ReceiveDate,ReceivedAmount from table2
Received Amount Can be multiple time, I want pending amount from (Amount - SUM(ReceivedAmount))
sql-server sql-server-2008 sql-server sql-server-2008 edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
edited Jan 1 at 20:03
Robert Harvey♦
149k33276420
149k33276420
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
asked Dec 30 '18 at 15:25
Harshad VaghelaHarshad Vaghela
177
177
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
You'd need to fist join your tables table 1 and table 2( below I did that in CTE). And then do a self join of the above result to get cumulative values.
See working demo
; with cte as
(
select
billid,
amount,
receivedamount,
r=row_number() over (partition by t1.billid order by ReceiveDate asc)
from
table1 t1 join table2 t2
on t1.billid=t2.BillReceive
)
select
amount=max(c1.amount)+max(c1.receivedamount)-sum(c2.receivedamount),
receivedamount=max(c1.receivedamount),
pendingamount=max(c1.amount)-sum(c2.receivedamount)
from
cte c1 left join cte c2
on c2.r<=c1.r and c2.billid=c1.billid
group by c1.billid,c1.r
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53978856%2fhow-to-generate-column-that-calculate-with-its-predecessor-value-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You'd need to fist join your tables table 1 and table 2( below I did that in CTE). And then do a self join of the above result to get cumulative values.
See working demo
; with cte as
(
select
billid,
amount,
receivedamount,
r=row_number() over (partition by t1.billid order by ReceiveDate asc)
from
table1 t1 join table2 t2
on t1.billid=t2.BillReceive
)
select
amount=max(c1.amount)+max(c1.receivedamount)-sum(c2.receivedamount),
receivedamount=max(c1.receivedamount),
pendingamount=max(c1.amount)-sum(c2.receivedamount)
from
cte c1 left join cte c2
on c2.r<=c1.r and c2.billid=c1.billid
group by c1.billid,c1.r
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
add a comment |
1
You'd need to fist join your tables table 1 and table 2( below I did that in CTE). And then do a self join of the above result to get cumulative values.
See working demo
; with cte as
(
select
billid,
amount,
receivedamount,
r=row_number() over (partition by t1.billid order by ReceiveDate asc)
from
table1 t1 join table2 t2
on t1.billid=t2.BillReceive
)
select
amount=max(c1.amount)+max(c1.receivedamount)-sum(c2.receivedamount),
receivedamount=max(c1.receivedamount),
pendingamount=max(c1.amount)-sum(c2.receivedamount)
from
cte c1 left join cte c2
on c2.r<=c1.r and c2.billid=c1.billid
group by c1.billid,c1.r
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
add a comment |
1
1
1
You'd need to fist join your tables table 1 and table 2( below I did that in CTE). And then do a self join of the above result to get cumulative values.
See working demo
; with cte as
(
select
billid,
amount,
receivedamount,
r=row_number() over (partition by t1.billid order by ReceiveDate asc)
from
table1 t1 join table2 t2
on t1.billid=t2.BillReceive
)
select
amount=max(c1.amount)+max(c1.receivedamount)-sum(c2.receivedamount),
receivedamount=max(c1.receivedamount),
pendingamount=max(c1.amount)-sum(c2.receivedamount)
from
cte c1 left join cte c2
on c2.r<=c1.r and c2.billid=c1.billid
group by c1.billid,c1.r
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
You'd need to fist join your tables table 1 and table 2( below I did that in CTE). And then do a self join of the above result to get cumulative values.
See working demo
; with cte as
(
select
billid,
amount,
receivedamount,
r=row_number() over (partition by t1.billid order by ReceiveDate asc)
from
table1 t1 join table2 t2
on t1.billid=t2.BillReceive
)
select
amount=max(c1.amount)+max(c1.receivedamount)-sum(c2.receivedamount),
receivedamount=max(c1.receivedamount),
pendingamount=max(c1.amount)-sum(c2.receivedamount)
from
cte c1 left join cte c2
on c2.r<=c1.r and c2.billid=c1.billid
group by c1.billid,c1.r
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
answered Dec 30 '18 at 18:58
DhruvJoshiDhruvJoshi
12.3k62747
12.3k62747
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53978856%2fhow-to-generate-column-that-calculate-with-its-predecessor-value-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
