SQL Join by comparing measures or loop with cursors?












0














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


Important: Schedule Lines and Deliveries should have the same PO and POItm.
The other logic to link is to sum the DlvQty until we reach (or exceed) ReqQty.
Those deliveries are then linked to the schedule line. Subsequent deliveries are used for the following schedule line(s). A delivery schould be matched to only one schedule line.



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










share|improve this question
























  • 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
    Nov 21 '18 at 16:31










  • The link between are PO and POItm. I should also mention a rather imprtant fact that when going through the delivery docs, once a Doc is linked to a Schedule line it cannot be tied again to another schedule line.
    – Bonsai1003
    Nov 22 '18 at 7:36










  • I have amended my inital problem description to further clarify. Hope this helps?
    – Bonsai1003
    Nov 22 '18 at 7:59
















0














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


Important: Schedule Lines and Deliveries should have the same PO and POItm.
The other logic to link is to sum the DlvQty until we reach (or exceed) ReqQty.
Those deliveries are then linked to the schedule line. Subsequent deliveries are used for the following schedule line(s). A delivery schould be matched to only one schedule line.



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










share|improve this question
























  • 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
    Nov 21 '18 at 16:31










  • The link between are PO and POItm. I should also mention a rather imprtant fact that when going through the delivery docs, once a Doc is linked to a Schedule line it cannot be tied again to another schedule line.
    – Bonsai1003
    Nov 22 '18 at 7:36










  • I have amended my inital problem description to further clarify. Hope this helps?
    – Bonsai1003
    Nov 22 '18 at 7:59














0












0








0







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


Important: Schedule Lines and Deliveries should have the same PO and POItm.
The other logic to link is to sum the DlvQty until we reach (or exceed) ReqQty.
Those deliveries are then linked to the schedule line. Subsequent deliveries are used for the following schedule line(s). A delivery schould be matched to only one schedule line.



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










share|improve this question















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


Important: Schedule Lines and Deliveries should have the same PO and POItm.
The other logic to link is to sum the DlvQty until we reach (or exceed) ReqQty.
Those deliveries are then linked to the schedule line. Subsequent deliveries are used for the following schedule line(s). A delivery schould be matched to only one schedule line.



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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 7:51

























asked Nov 21 '18 at 16:03









Bonsai1003

12




12












  • 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
    Nov 21 '18 at 16:31










  • The link between are PO and POItm. I should also mention a rather imprtant fact that when going through the delivery docs, once a Doc is linked to a Schedule line it cannot be tied again to another schedule line.
    – Bonsai1003
    Nov 22 '18 at 7:36










  • I have amended my inital problem description to further clarify. Hope this helps?
    – Bonsai1003
    Nov 22 '18 at 7:59


















  • 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
    Nov 21 '18 at 16:31










  • The link between are PO and POItm. I should also mention a rather imprtant fact that when going through the delivery docs, once a Doc is linked to a Schedule line it cannot be tied again to another schedule line.
    – Bonsai1003
    Nov 22 '18 at 7:36










  • I have amended my inital problem description to further clarify. Hope this helps?
    – Bonsai1003
    Nov 22 '18 at 7:59
















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
Nov 21 '18 at 16:31




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
Nov 21 '18 at 16:31












The link between are PO and POItm. I should also mention a rather imprtant fact that when going through the delivery docs, once a Doc is linked to a Schedule line it cannot be tied again to another schedule line.
– Bonsai1003
Nov 22 '18 at 7:36




The link between are PO and POItm. I should also mention a rather imprtant fact that when going through the delivery docs, once a Doc is linked to a Schedule line it cannot be tied again to another schedule line.
– Bonsai1003
Nov 22 '18 at 7:36












I have amended my inital problem description to further clarify. Hope this helps?
– Bonsai1003
Nov 22 '18 at 7:59




I have amended my inital problem description to further clarify. Hope this helps?
– Bonsai1003
Nov 22 '18 at 7:59












1 Answer
1






active

oldest

votes


















0














If you can express the rule for matching a delivery with a schedule line, you can produce the results you want in a single query. And, yes, I promise it will be faster (and simpler) than executing the same logic in loops on cursors.



I can't reproduce your exact results because I don't quite understand how the two tables relate. Hopefully from the code below you'll be able to figure it out by adjusting the join criteria.



I don't have your DBMS. My code uses SQLite, which has its own peculiar date functions. You'll have to substitute the ones your system provides. In any event, I can't recommend 5-character strings for dates. Use a datetime type if you have one, and include 4-digit years regardless. Else how many days are there between Christmas and New Years Day?



create table S (
PO int not NULL,
POItm int not NULL,
SchLin int not NULL,
ReqDate char not NULL,
ReqQty int not NULL,
primary key (PO, POItm, SchLin)
);

insert into S values
(123, 1, 1, '10/11', 20 ),
(123, 1, 2, '30/11', 30 ),
(124, 2, 1, '15/12', 10 ),
(124, 2, 2, '24/12', 15 );

create table D (
Doc int not NULL,
Item int not NULL,
PO int not NULL,
POItm int not NULL,
DlvDate char not NULL,
DlvQty int not NULL,
primary key (Doc, Item)
);

insert into D values
(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 );

select D.Doc, D.Item, D.PO, S.SchLin, S.ReqDate, D.DlvDate
, cast(
julianday('2018-' || substr(DlvDate, 4,2) || '-' || substr(DlvDate, 1,2))
- julianday('2018-' || substr(ReqDate, 4,2) || '-' || substr(ReqDate, 1,2))
as int) as DlvDelta
from S join D on S.PO = D.PO and S.POItm = D.POItm
;


Result:



Doc         Item        PO          SchLin      ReqDate     DlvDate     DlvDelta  
---------- ---------- ---------- ---------- ---------- ---------- ----------
810 1 123 1 10/11 29/10 -12
810 1 123 2 30/11 29/10 -32
816 1 123 1 10/11 02/11 -8
816 1 123 2 30/11 02/11 -28
823 1 123 1 10/11 04/11 -6
823 1 123 2 30/11 04/11 -26
828 1 123 1 10/11 06/11 -4
828 1 123 2 30/11 06/11 -24
856 1 123 1 10/11 10/11 0
856 1 123 2 30/11 10/11 -20
873 1 123 1 10/11 14/11 4
873 1 123 2 30/11 14/11 -16
902 1 124 1 15/12 27/11 -18
902 1 124 2 24/12 27/11 -27
908 1 124 1 15/12 30/11 -15
908 1 124 2 24/12 30/11 -24
911 1 124 1 15/12 08/12 -7
911 1 124 2 24/12 08/12 -16
923 1 124 1 15/12 27/12 12
923 1 124 2 24/12 27/12 3





share|improve this answer





















  • Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
    – Bonsai1003
    Nov 22 '18 at 7:44










  • As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
    – Bonsai1003
    Nov 22 '18 at 7:57











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


















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

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














If you can express the rule for matching a delivery with a schedule line, you can produce the results you want in a single query. And, yes, I promise it will be faster (and simpler) than executing the same logic in loops on cursors.



I can't reproduce your exact results because I don't quite understand how the two tables relate. Hopefully from the code below you'll be able to figure it out by adjusting the join criteria.



I don't have your DBMS. My code uses SQLite, which has its own peculiar date functions. You'll have to substitute the ones your system provides. In any event, I can't recommend 5-character strings for dates. Use a datetime type if you have one, and include 4-digit years regardless. Else how many days are there between Christmas and New Years Day?



create table S (
PO int not NULL,
POItm int not NULL,
SchLin int not NULL,
ReqDate char not NULL,
ReqQty int not NULL,
primary key (PO, POItm, SchLin)
);

insert into S values
(123, 1, 1, '10/11', 20 ),
(123, 1, 2, '30/11', 30 ),
(124, 2, 1, '15/12', 10 ),
(124, 2, 2, '24/12', 15 );

create table D (
Doc int not NULL,
Item int not NULL,
PO int not NULL,
POItm int not NULL,
DlvDate char not NULL,
DlvQty int not NULL,
primary key (Doc, Item)
);

insert into D values
(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 );

select D.Doc, D.Item, D.PO, S.SchLin, S.ReqDate, D.DlvDate
, cast(
julianday('2018-' || substr(DlvDate, 4,2) || '-' || substr(DlvDate, 1,2))
- julianday('2018-' || substr(ReqDate, 4,2) || '-' || substr(ReqDate, 1,2))
as int) as DlvDelta
from S join D on S.PO = D.PO and S.POItm = D.POItm
;


Result:



Doc         Item        PO          SchLin      ReqDate     DlvDate     DlvDelta  
---------- ---------- ---------- ---------- ---------- ---------- ----------
810 1 123 1 10/11 29/10 -12
810 1 123 2 30/11 29/10 -32
816 1 123 1 10/11 02/11 -8
816 1 123 2 30/11 02/11 -28
823 1 123 1 10/11 04/11 -6
823 1 123 2 30/11 04/11 -26
828 1 123 1 10/11 06/11 -4
828 1 123 2 30/11 06/11 -24
856 1 123 1 10/11 10/11 0
856 1 123 2 30/11 10/11 -20
873 1 123 1 10/11 14/11 4
873 1 123 2 30/11 14/11 -16
902 1 124 1 15/12 27/11 -18
902 1 124 2 24/12 27/11 -27
908 1 124 1 15/12 30/11 -15
908 1 124 2 24/12 30/11 -24
911 1 124 1 15/12 08/12 -7
911 1 124 2 24/12 08/12 -16
923 1 124 1 15/12 27/12 12
923 1 124 2 24/12 27/12 3





share|improve this answer





















  • Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
    – Bonsai1003
    Nov 22 '18 at 7:44










  • As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
    – Bonsai1003
    Nov 22 '18 at 7:57
















0














If you can express the rule for matching a delivery with a schedule line, you can produce the results you want in a single query. And, yes, I promise it will be faster (and simpler) than executing the same logic in loops on cursors.



I can't reproduce your exact results because I don't quite understand how the two tables relate. Hopefully from the code below you'll be able to figure it out by adjusting the join criteria.



I don't have your DBMS. My code uses SQLite, which has its own peculiar date functions. You'll have to substitute the ones your system provides. In any event, I can't recommend 5-character strings for dates. Use a datetime type if you have one, and include 4-digit years regardless. Else how many days are there between Christmas and New Years Day?



create table S (
PO int not NULL,
POItm int not NULL,
SchLin int not NULL,
ReqDate char not NULL,
ReqQty int not NULL,
primary key (PO, POItm, SchLin)
);

insert into S values
(123, 1, 1, '10/11', 20 ),
(123, 1, 2, '30/11', 30 ),
(124, 2, 1, '15/12', 10 ),
(124, 2, 2, '24/12', 15 );

create table D (
Doc int not NULL,
Item int not NULL,
PO int not NULL,
POItm int not NULL,
DlvDate char not NULL,
DlvQty int not NULL,
primary key (Doc, Item)
);

insert into D values
(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 );

select D.Doc, D.Item, D.PO, S.SchLin, S.ReqDate, D.DlvDate
, cast(
julianday('2018-' || substr(DlvDate, 4,2) || '-' || substr(DlvDate, 1,2))
- julianday('2018-' || substr(ReqDate, 4,2) || '-' || substr(ReqDate, 1,2))
as int) as DlvDelta
from S join D on S.PO = D.PO and S.POItm = D.POItm
;


Result:



Doc         Item        PO          SchLin      ReqDate     DlvDate     DlvDelta  
---------- ---------- ---------- ---------- ---------- ---------- ----------
810 1 123 1 10/11 29/10 -12
810 1 123 2 30/11 29/10 -32
816 1 123 1 10/11 02/11 -8
816 1 123 2 30/11 02/11 -28
823 1 123 1 10/11 04/11 -6
823 1 123 2 30/11 04/11 -26
828 1 123 1 10/11 06/11 -4
828 1 123 2 30/11 06/11 -24
856 1 123 1 10/11 10/11 0
856 1 123 2 30/11 10/11 -20
873 1 123 1 10/11 14/11 4
873 1 123 2 30/11 14/11 -16
902 1 124 1 15/12 27/11 -18
902 1 124 2 24/12 27/11 -27
908 1 124 1 15/12 30/11 -15
908 1 124 2 24/12 30/11 -24
911 1 124 1 15/12 08/12 -7
911 1 124 2 24/12 08/12 -16
923 1 124 1 15/12 27/12 12
923 1 124 2 24/12 27/12 3





share|improve this answer





















  • Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
    – Bonsai1003
    Nov 22 '18 at 7:44










  • As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
    – Bonsai1003
    Nov 22 '18 at 7:57














0












0








0






If you can express the rule for matching a delivery with a schedule line, you can produce the results you want in a single query. And, yes, I promise it will be faster (and simpler) than executing the same logic in loops on cursors.



I can't reproduce your exact results because I don't quite understand how the two tables relate. Hopefully from the code below you'll be able to figure it out by adjusting the join criteria.



I don't have your DBMS. My code uses SQLite, which has its own peculiar date functions. You'll have to substitute the ones your system provides. In any event, I can't recommend 5-character strings for dates. Use a datetime type if you have one, and include 4-digit years regardless. Else how many days are there between Christmas and New Years Day?



create table S (
PO int not NULL,
POItm int not NULL,
SchLin int not NULL,
ReqDate char not NULL,
ReqQty int not NULL,
primary key (PO, POItm, SchLin)
);

insert into S values
(123, 1, 1, '10/11', 20 ),
(123, 1, 2, '30/11', 30 ),
(124, 2, 1, '15/12', 10 ),
(124, 2, 2, '24/12', 15 );

create table D (
Doc int not NULL,
Item int not NULL,
PO int not NULL,
POItm int not NULL,
DlvDate char not NULL,
DlvQty int not NULL,
primary key (Doc, Item)
);

insert into D values
(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 );

select D.Doc, D.Item, D.PO, S.SchLin, S.ReqDate, D.DlvDate
, cast(
julianday('2018-' || substr(DlvDate, 4,2) || '-' || substr(DlvDate, 1,2))
- julianday('2018-' || substr(ReqDate, 4,2) || '-' || substr(ReqDate, 1,2))
as int) as DlvDelta
from S join D on S.PO = D.PO and S.POItm = D.POItm
;


Result:



Doc         Item        PO          SchLin      ReqDate     DlvDate     DlvDelta  
---------- ---------- ---------- ---------- ---------- ---------- ----------
810 1 123 1 10/11 29/10 -12
810 1 123 2 30/11 29/10 -32
816 1 123 1 10/11 02/11 -8
816 1 123 2 30/11 02/11 -28
823 1 123 1 10/11 04/11 -6
823 1 123 2 30/11 04/11 -26
828 1 123 1 10/11 06/11 -4
828 1 123 2 30/11 06/11 -24
856 1 123 1 10/11 10/11 0
856 1 123 2 30/11 10/11 -20
873 1 123 1 10/11 14/11 4
873 1 123 2 30/11 14/11 -16
902 1 124 1 15/12 27/11 -18
902 1 124 2 24/12 27/11 -27
908 1 124 1 15/12 30/11 -15
908 1 124 2 24/12 30/11 -24
911 1 124 1 15/12 08/12 -7
911 1 124 2 24/12 08/12 -16
923 1 124 1 15/12 27/12 12
923 1 124 2 24/12 27/12 3





share|improve this answer












If you can express the rule for matching a delivery with a schedule line, you can produce the results you want in a single query. And, yes, I promise it will be faster (and simpler) than executing the same logic in loops on cursors.



I can't reproduce your exact results because I don't quite understand how the two tables relate. Hopefully from the code below you'll be able to figure it out by adjusting the join criteria.



I don't have your DBMS. My code uses SQLite, which has its own peculiar date functions. You'll have to substitute the ones your system provides. In any event, I can't recommend 5-character strings for dates. Use a datetime type if you have one, and include 4-digit years regardless. Else how many days are there between Christmas and New Years Day?



create table S (
PO int not NULL,
POItm int not NULL,
SchLin int not NULL,
ReqDate char not NULL,
ReqQty int not NULL,
primary key (PO, POItm, SchLin)
);

insert into S values
(123, 1, 1, '10/11', 20 ),
(123, 1, 2, '30/11', 30 ),
(124, 2, 1, '15/12', 10 ),
(124, 2, 2, '24/12', 15 );

create table D (
Doc int not NULL,
Item int not NULL,
PO int not NULL,
POItm int not NULL,
DlvDate char not NULL,
DlvQty int not NULL,
primary key (Doc, Item)
);

insert into D values
(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 );

select D.Doc, D.Item, D.PO, S.SchLin, S.ReqDate, D.DlvDate
, cast(
julianday('2018-' || substr(DlvDate, 4,2) || '-' || substr(DlvDate, 1,2))
- julianday('2018-' || substr(ReqDate, 4,2) || '-' || substr(ReqDate, 1,2))
as int) as DlvDelta
from S join D on S.PO = D.PO and S.POItm = D.POItm
;


Result:



Doc         Item        PO          SchLin      ReqDate     DlvDate     DlvDelta  
---------- ---------- ---------- ---------- ---------- ---------- ----------
810 1 123 1 10/11 29/10 -12
810 1 123 2 30/11 29/10 -32
816 1 123 1 10/11 02/11 -8
816 1 123 2 30/11 02/11 -28
823 1 123 1 10/11 04/11 -6
823 1 123 2 30/11 04/11 -26
828 1 123 1 10/11 06/11 -4
828 1 123 2 30/11 06/11 -24
856 1 123 1 10/11 10/11 0
856 1 123 2 30/11 10/11 -20
873 1 123 1 10/11 14/11 4
873 1 123 2 30/11 14/11 -16
902 1 124 1 15/12 27/11 -18
902 1 124 2 24/12 27/11 -27
908 1 124 1 15/12 30/11 -15
908 1 124 2 24/12 30/11 -24
911 1 124 1 15/12 08/12 -7
911 1 124 2 24/12 08/12 -16
923 1 124 1 15/12 27/12 12
923 1 124 2 24/12 27/12 3






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 21 '18 at 21:10









James K. Lowden

5,28211025




5,28211025












  • Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
    – Bonsai1003
    Nov 22 '18 at 7:44










  • As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
    – Bonsai1003
    Nov 22 '18 at 7:57


















  • Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
    – Bonsai1003
    Nov 22 '18 at 7:44










  • As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
    – Bonsai1003
    Nov 22 '18 at 7:57
















Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
– Bonsai1003
Nov 22 '18 at 7:44




Thanks. I see in your result set that delivery docs are matched up several times. That's actually a rather imortant fact I forgot to mention: Once a delivery is matched to a schedule line it's out of scope. In fact we add deliveries (DlvQty) until we reach (or exeed) the required quantity (ReqQty) for that schedule line. Following deliveries will be used to fullfil the next schedule line. The link between schedule line and deliveries is the PO and POItm. these need to be the same.
– Bonsai1003
Nov 22 '18 at 7:44












As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
– Bonsai1003
Nov 22 '18 at 7:57




As for dates they would be fully specified e.g. 30/11/2018. I was just a bit lazy in my notes and took the 'short'cut... :-)
– Bonsai1003
Nov 22 '18 at 7:57


















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.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • 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














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





















































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







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'