SQL Join by comparing measures or loop with cursors?

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

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 07:36
I have amended my inital problem description to further clarify. Hope this helps? — Bonsai1003, Nov 22 '18 at 07:59

score 0 · Answer 1 · answered Nov 21 '18 at 21:10

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

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 07: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 07:57

SQL Join by comparing measures or loop with cursors?

1 Answers1