SQL Join by comparing measures or loop with cursors?
up vote
0
down vote
favorite
In order to verify if Deliveries are done on time, I need to match delivery Documents to PO schedule lines (SchLin) based on the comparison between Required Quantity (ReqQty) and Delivered Quantity (DlvQty).
The Delivery Docs have a reference to the PO and POItm but not to the SchLin.
Once a Delivery Doc is assigned to a Schedule Line I can calculate the Delivery Delta (DlvDelta) as the number of days it was delivered early or late compared to the requirement (ReqDate).
Examples of the two base tables are as follows:
Schedule lines
PO POItm SchLin ReqDate ReqQty
123 1 1 10/11 20
123 1 2 30/11 30
124 2 1 15/12 10
124 2 2 24/12 15
Delivery Docs
Doc Item PO POItm DlvDate DlvQty
810 1 123 1 29/10 12
816 1 123 1 02/11 07
823 1 123 1 04/11 13
828 1 123 1 06/11 08
856 1 123 1 10/11 05
873 1 123 1 14/11 09
902 1 124 2 27/11 05
908 1 124 2 30/11 07
911 1 124 2 08/12 08
923 1 124 2 27/12 09
After comparing the ReqQty and DlvQty the assignments should result in following:
Result
Doc Item PO POItm Schlin ReqDate DlvDate DlvDelta
810 1 123 1 1 10/11 29/10 -11
816 1 123 1 1 10/11 02/11 -08
823 1 123 1 1 10/11 04/11 -06
828 1 123 1 2 30/11 06/11 -24
856 1 123 1 2 30/11 10/11 -20
873 1 123 1 2 30/11 14/11 -16
902 1 124 2 1 15/12 27/11 -18
908 1 124 2 1 15/12 30/11 -15
911 1 124 2 2 24/12 08/12 -16
923 1 124 2 2 24/12 27/12 +03
Up till now, I have done this with loops using cursors but performance is rather sluggish.
Is there another way in SQL(script) using e.g. joins by comparing measures to achieve the same result?
Regards,
Eric
sql hana-sql-script
asked 1 hour ago
Bonsai1003
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
In order to verify if Deliveries are done on time, I need to match delivery Documents to PO schedule lines (SchLin) based on the comparison between Required Quantity (ReqQty) and Delivered Quantity (DlvQty).
The Delivery Docs have a reference to the PO and POItm but not to the SchLin.
Once a Delivery Doc is assigned to a Schedule Line I can calculate the Delivery Delta (DlvDelta) as the number of days it was delivered early or late compared to the requirement (ReqDate).
Examples of the two base tables are as follows:
Schedule lines
PO POItm SchLin ReqDate ReqQty
123 1 1 10/11 20
123 1 2 30/11 30
124 2 1 15/12 10
124 2 2 24/12 15
Delivery Docs
Doc Item PO POItm DlvDate DlvQty
810 1 123 1 29/10 12
816 1 123 1 02/11 07
823 1 123 1 04/11 13
828 1 123 1 06/11 08
856 1 123 1 10/11 05
873 1 123 1 14/11 09
902 1 124 2 27/11 05
908 1 124 2 30/11 07
911 1 124 2 08/12 08
923 1 124 2 27/12 09
After comparing the ReqQty and DlvQty the assignments should result in following:
Result
Doc Item PO POItm Schlin ReqDate DlvDate DlvDelta
810 1 123 1 1 10/11 29/10 -11
816 1 123 1 1 10/11 02/11 -08
823 1 123 1 1 10/11 04/11 -06
828 1 123 1 2 30/11 06/11 -24
856 1 123 1 2 30/11 10/11 -20
873 1 123 1 2 30/11 14/11 -16
902 1 124 2 1 15/12 27/11 -18
908 1 124 2 1 15/12 30/11 -15
911 1 124 2 2 24/12 08/12 -16
923 1 124 2 2 24/12 27/12 +03
Up till now, I have done this with loops using cursors but performance is rather sluggish.
Is there another way in SQL(script) using e.g. joins by comparing measures to achieve the same result?
Regards,
Eric
sql hana-sql-script
asked 1 hour ago
Bonsai1003
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
How is the Delivery Doc 'assigned' a scheduled line? Because from the tables you listed, it doesn't look like there's anything tying those two concepts together.
– Kevin
48 mins ago
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
In order to verify if Deliveries are done on time, I need to match delivery Documents to PO schedule lines (SchLin) based on the comparison between Required Quantity (ReqQty) and Delivered Quantity (DlvQty).
The Delivery Docs have a reference to the PO and POItm but not to the SchLin.
Once a Delivery Doc is assigned to a Schedule Line I can calculate the Delivery Delta (DlvDelta) as the number of days it was delivered early or late compared to the requirement (ReqDate).
Examples of the two base tables are as follows:
Schedule lines
PO POItm SchLin ReqDate ReqQty
123 1 1 10/11 20
123 1 2 30/11 30
124 2 1 15/12 10
124 2 2 24/12 15
Delivery Docs
Doc Item PO POItm DlvDate DlvQty
810 1 123 1 29/10 12
816 1 123 1 02/11 07
823 1 123 1 04/11 13
828 1 123 1 06/11 08
856 1 123 1 10/11 05
873 1 123 1 14/11 09
902 1 124 2 27/11 05
908 1 124 2 30/11 07
911 1 124 2 08/12 08
923 1 124 2 27/12 09
After comparing the ReqQty and DlvQty the assignments should result in following:
Result
Doc Item PO POItm Schlin ReqDate DlvDate DlvDelta
810 1 123 1 1 10/11 29/10 -11
816 1 123 1 1 10/11 02/11 -08
823 1 123 1 1 10/11 04/11 -06
828 1 123 1 2 30/11 06/11 -24
856 1 123 1 2 30/11 10/11 -20
873 1 123 1 2 30/11 14/11 -16
902 1 124 2 1 15/12 27/11 -18
908 1 124 2 1 15/12 30/11 -15
911 1 124 2 2 24/12 08/12 -16
923 1 124 2 2 24/12 27/12 +03
Up till now, I have done this with loops using cursors but performance is rather sluggish.
Is there another way in SQL(script) using e.g. joins by comparing measures to achieve the same result?
Regards,
Eric
sql hana-sql-script
asked 1 hour ago
Bonsai1003
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In order to verify if Deliveries are done on time, I need to match delivery Documents to PO schedule lines (SchLin) based on the comparison between Required Quantity (ReqQty) and Delivered Quantity (DlvQty).
The Delivery Docs have a reference to the PO and POItm but not to the SchLin.
Once a Delivery Doc is assigned to a Schedule Line I can calculate the Delivery Delta (DlvDelta) as the number of days it was delivered early or late compared to the requirement (ReqDate).
Examples of the two base tables are as follows:
Schedule lines
PO POItm SchLin ReqDate ReqQty
123 1 1 10/11 20
123 1 2 30/11 30
124 2 1 15/12 10
124 2 2 24/12 15
Delivery Docs
Doc Item PO POItm DlvDate DlvQty
810 1 123 1 29/10 12
816 1 123 1 02/11 07
823 1 123 1 04/11 13
828 1 123 1 06/11 08
856 1 123 1 10/11 05
873 1 123 1 14/11 09
902 1 124 2 27/11 05
908 1 124 2 30/11 07
911 1 124 2 08/12 08
923 1 124 2 27/12 09
After comparing the ReqQty and DlvQty the assignments should result in following:
Result
Doc Item PO POItm Schlin ReqDate DlvDate DlvDelta
810 1 123 1 1 10/11 29/10 -11
816 1 123 1 1 10/11 02/11 -08
823 1 123 1 1 10/11 04/11 -06
828 1 123 1 2 30/11 06/11 -24
856 1 123 1 2 30/11 10/11 -20
873 1 123 1 2 30/11 14/11 -16
902 1 124 2 1 15/12 27/11 -18
908 1 124 2 1 15/12 30/11 -15
911 1 124 2 2 24/12 08/12 -16
923 1 124 2 2 24/12 27/12 +03
Up till now, I have done this with loops using cursors but performance is rather sluggish.
Is there another way in SQL(script) using e.g. joins by comparing measures to achieve the same result?
Regards,
Eric
sql hana-sql-script
sql hana-sql-script
asked 1 hour ago
Bonsai1003
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
Bonsai1003
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
Bonsai1003
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
Bonsai1003
11
asked 1 hour ago
Bonsai1003
11
11
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Bonsai1003 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
How is the Delivery Doc 'assigned' a scheduled line? Because from the tables you listed, it doesn't look like there's anything tying those two concepts together.
– Kevin
48 mins ago
add a comment |
How is the Delivery Doc 'assigned' a scheduled line? Because from the tables you listed, it doesn't look like there's anything tying those two concepts together.
– Kevin
48 mins ago
How is the Delivery Doc 'assigned' a scheduled line? Because from the tables you listed, it doesn't look like there's anything tying those two concepts together.
– Kevin
48 mins ago
How is the Delivery Doc 'assigned' a scheduled line? Because from the tables you listed, it doesn't look like there's anything tying those two concepts together.
– Kevin
48 mins ago
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Bonsai1003 is a new contributor. Be nice, and check out our Code of Conduct.
Bonsai1003 is a new contributor. Be nice, and check out our Code of Conduct.
Bonsai1003 is a new contributor. Be nice, and check out our Code of Conduct.
Bonsai1003 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f53416026%2fsql-join-by-comparing-measures-or-loop-with-cursors%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
How is the Delivery Doc 'assigned' a scheduled line? Because from the tables you listed, it doesn't look like there's anything tying those two concepts together.
– Kevin
48 mins ago