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

Answer 1

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