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
Advertisement
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;
