Cumulative calculation using SQL

Question

I have a supply and a demand table as shown below,

Supply
Center1  1-Sep  500
Center1  1-Dec  1000

Demand
Center1  Req-1  1-Aug   300
Center1  Req-2  15-Aug  250
Center1  Req-3  1-Sep   1100

write a SQL code to get the output as shown below,

Expected output,

                 Req_Dt   Supply_Dt    Units supplied
Center1  Req-1   1-Aug      1-Sep       300
Center1  Req-2   15-Aug     1-Sep       200
Center1  Req-2   15-Aug     1-Dec       50
Center1  Req-3   1-Sep      1-Dec       950
Center1  Req-3   1-Sep      NULL        150

For a center1, if there is request-1 which came in asking for 300 units on 1-Aug but there is no supply available as of 1-Aug, so we cant fulfill the request until we get a supply. Next, supply received only on 1-Sep with 500 units. So, for the request-1, supply will happen on 1-Sep and 300 units will be supplied, so 200 is still remaining.

Now, the next request-2 on 15-Aug requires 250 units and since still 1-Sep can fulfill another 200 units of supply left over after using for req-1 we split the fulfillment for request by stating Req-2 can be fulfilled by 200 units on 1-Sep and remaining 50 only on 1-Dec when the next supply comes and so on.

Tried using while loop in Stored procedure but it takes more time when processing millions of rows. so either a function or sql needs to be written

Expected output,

Center   Request    Req_Dt    Supply_Dt    Units supplied
Center1  Req-1      1-Aug      1-Sep       300
Center1  Req-2      15-Aug     1-Sep       200
Center1  Req-2      15-Aug     1-Dec       50
Center1  Req-3      1-Sep      1-Dec       950
Center1  Req-3      1-Sep      NULL        150

Dwight Reynoldson · Accepted Answer

Here is one way of looping over the data to get the desired outcome.

Essentially I have built a series of handling tables. One for filtering each demand and supply row at a time and one for actually manipulating the counts of things supplied/in demand rows, this is done so the data itself can be left alone. Finally there is a table created on the fly to generate the output itself.

The loop basically continues until there is nothing left to filter for and if anything is still outstanding either from a supply row or a demand row then that is also interted in to the output table. On each pass the current requirement and supplier handler tables are queried to see if something new needs adding, then at the end of the loop after the output row has been generated anything which is at 0 or less is removed from the handler tables to ensure only one supply or demand row is being dealt with at a time.

EDIT: I've added a loop for the center. This means that a data set with multiple centers will now run successfully.

I've also tested the example with more rows. With 48 supply records and 80 demand records this query produces 128 transaction rows in one second so I suspect the performance issues you were noticing may have had to do with the center looping rather than the query performance itself. Let me know how you get on with the updated query and if there are performance issues we can look in to why that is.

Please see example query and data using the Center1 and Center 2 data provided from your comment and output result set pasted below query.

declare @Supply table (
    Center nvarchar(20),
    [Date] nvarchar(10),
    Supply int
);

declare @Demand table (
    Center nvarchar(20),
    Requirement nvarchar(10),
    [Date] nvarchar(10),
    Demand int
);

insert @Supply (Center, Date, Supply)
values
('Center1', '1-Sep', 500),
('Center1', '1-Dec', 1000),
('Center2', '1-Oct', 700);


insert @Demand (Center, Requirement, Date, Demand)
values
('Center1', 'Req-1', '1-Aug', 300),
('Center1', 'Req-2', '15-Aug', 250),
('Center1', 'Req-3', '1-Sep', 1100),
('Center2', 'Req-1', '1-Sep', 500), 
('Center2', 'Req-2', '1-Oct', 250);

declare @output table
( Center nvarchar(20),
  Requirement nvarchar(10),
  Req_Dt nvarchar(10),
  Supply_Dt nvarchar(10),
  Units_supplied int
);

declare @reqfilter table (
    Requirement nvarchar(10),
    Center nvarchar(20)
);

declare @supfilter table (
    Date nvarchar(10),
    Center nvarchar(20)
);

declare @req table (
    Center nvarchar(20),
    Requirement nvarchar(10),
    Date nvarchar(10),
    Demand int
);

declare @sup table (
    Center nvarchar(20),
    Date nvarchar(10),
    Supply int
);

Declare @Centerfilter table (Center nvarchar(20));

insert @Centerfilter
select distinct Center from @Supply
union
select distinct Center from @Demand;

--select count(*) from @Supply as supply
--select count(*) from @Demand as demand

while exists (select 1 from @Centerfilter)
begin
    insert @reqfilter 
    select requirement, c.Center from @Demand d inner join 
    (select top 1 Center from @Centerfilter order by Center) c
    on d.Center=c.Center;

    insert @supfilter
    select date, s.Center from @Supply s inner join
    (select top 1 Center from @Centerfilter order by Center) c
    on s.Center=c.Center;

    while exists (select 1 from @reqfilter outer apply @supfilter)
    begin 
        if not exists (select 1 from @req)
        begin
                insert @req
                select top 1 d.Center, d.Requirement, date, demand from
                @Demand d inner join @reqfilter r on d.center=r.Center where d.Requirement in (select top 1 Requirement from @reqfilter order by Center, Requirement)
                order by Center, parse(date as date);
                delete @reqfilter from @reqfilter rq inner join @req r on rq.Requirement=r.Requirement and rq.Center=r.Center;
        end
        if not exists (select 1 from @sup)
        begin
            insert @sup
            select top 1 s.Center, date, supply from
            @Supply s inner join @Centerfilter c on s.Center=c.Center where date in (select top 1 Date from @supfilter order by Center, parse(date as date))
            order by center, parse(date as date);
            delete @supfilter from @supfilter s inner join @sup su on s.Date=su.date and s.Center=su.Center;
        end
        insert @output
        select coalesce(d.center, s.center), requirement, d.date, s.date, case when supply>Demand then demand else supply end
        from @req d inner join @sup s on d.Center=s.Center

        update @req set demand=demand-o.Units_supplied 
        from @req r inner join (select top 1 Center, Requirement, Units_supplied from @output order by Center desc, parse(Req_Dt as date) desc, parse(Supply_Dt as date) desc) o on r.Center=o.Center and r.Requirement=o.Requirement;

        update @sup set Supply=supply-units_supplied
        from @sup s inner join @req r on s.Center=r.Center 
        inner join (select top 1 center, requirement, units_supplied from @output order by Center desc, parse(Req_Dt as date) desc, parse(Supply_Dt as date) desc) 
        o on s.Center=o.Center and r.Requirement=o.Requirement;
        delete from @req where Demand<=0;
        delete from @sup where Supply<=0;
    end

    if exists (select 1 from @req)
    begin
        insert @output
        select center, requirement, Date, null, demand from @req;
    end

    if exists (select 1 from @sup)
    begin
        insert @output
        select center, null, null, date, supply from @sup;
    end
    delete @Centerfilter where Center in (select top 1 Center from @Centerfilter order by Center);
    delete from @reqfilter;
    delete from @supfilter;
    delete from @req;
    delete from @sup;
end

    select * from @output;

Cumulative calculation using SQL

Answers (2)

Related Questions