Cumalative Sum and Supply/Demand Delay

[TSmooth]

Hi,

I have a table consisting of the following columns of relevance:

Day - Integer ranging from 1-9
Demand_Qty - Double value specifying the demand for that particular day.
Demand_Filled - Doulbe value specifying how much of the item was recieved that day.

What I need to find out is how many days it takes before each demand is filled. For example:

Day Demand_Qty Demand_Filled Days_to_Fill
1 10 0 2
2 10 0 2
3 20 10 1
4 5 30 1
5 10 10 -1

The first 3 columns are provided and the Days_to_Fill column is what I need to calculate. A -1 in the Days_To_Fill means the demand was never met. As you can see, the Demand for Day 1 is 10, which is not filled until Day 3. If further clarification is needed, pls inquire.

I'm not sure exactly where to begin so any tips/ideas/solutions would be greatly appreciated. One thing that I believe could get me closer to a solution would be acquiring a cumalative sum of Demand_Qty and Demand_Filled for each day, but I don't know how to do this in SQL... anyone know how? What I mean is a sum that would look like this based on the above sample:

Day SumDemand SumFilled
1 10 0
2 20 0
3 40 10
4 45 40
5 55 50

Thank you for any help in advance, hoping I can get a solution figured out soon for my Project Manager.

-Tom

[Krzemo]

Hmm... the table is small than there is no problem.
One of many solutions for is to use subquerys..:

 SELECT 
Day
,(SELECT SUM(mm.Demand_Qty) FROM Mytable mm WHERE mm.Day<=m.Day) SumDemand
,(SELECT SUM(mm.Demand_Filled) FROM Mytable mm WHERE mm.Day<=m.Day) SumFilled
FROM Mytable m

U could use also iterative approach using cursor or statistical agregate functions (if yor RDBMS supports them)

Best regards,
Krzemo.

[TSmooth]

Unfortunately for this project I'm restricted to MS Access at the moment. I did get cumulative sums working now. The dataset is not small though and I am just working with a small subset til I get it right. Then I will run it on the full dataset. Here is my query:

SELECT Class, Node, Phase, Experiment, Day, (SELECT SUM(Demand_Qty) FROM SmallSample WHERE Day <= O.Day AND Class=O.Class AND  Node=O.Node AND Phase=O.Phase AND Experiment=O.Experiment) As SumOfDemand_Qty, (SELECT SUM(Demand_Filled) FROM SmallSample WHERE Day <= O.Day AND Class=O.Class AND  Node=O.Node AND Phase=O.Phase AND Experiment=O.Experiment) As SumOfDemand_Filled
FROM SmallSample O
GROUP BY Class, Node, Phase, Experiment, Day;

Now.. any thoughts on how to determine the number of days until each demand is met?

[Krzemo]

I don't understand what exactly U trying to do, but U can also use COUNT subquery (the same way as SUM) or save this as access query. Than U can join That query with table again (virtual table) joining them by Day.

Hope it helps.

[womalley]

Unfortunately for this project I'm restricted to MS Access at the moment.

I was just curious...if you are using MS Access can you also employ some automation using Excel or VBA to get you cumulative sums?

If you have the ability to use Excel or VBA it may be a lot easier then constructing a query with Access.

Do you mind if I ask why you are restricted to MS Access?


Will

[TSmooth]

I can use excel/vba if needed yes.. thought it would be simpler to do it all in access though since the table has about 800,000 records in it and excel is limited in that sense. I'm restricted to MS Access because there is no SQL server setup in the office and most people aren't familiar with it anyhow.

Now that we have the cumalative sums, need to come up with a query that for each row, calculates the first or minimum day on which the CumalativeDemand_Filled >= CumalativeDemand_Qty.

[womalley]

Ok,
Now my next question would be does this need to be printable? or can you stick to using just a form with out having to worry about printing all of your results...

If you can use a form and the information does not have to be printable. I would use a series of sub forms.

The first would have a listing of parts using the date range specified by the user. The second subform would calculate sums for each part listed.

How ever you end up solving this you have quite a challenge in front of you.. Good luck!


Will

[TSmooth]

Well, I have what I believe to be working queries. The first one calculate's the cumalative sums of the requested quantity and the amount recieved on each day.

Query: CumalativeSum

SELECT Class, Node, Phase, Experiment, Day, (SELECT SUM(Demand_Qty) FROM SmallSample WHERE Day <= O.Day AND Class=O.Class AND  Node=O.Node AND Phase=O.Phase AND Experiment=O.Experiment) As SumOfDemand_Qty, (SELECT SUM(Demand_Filled) FROM SmallSample WHERE Day <= O.Day AND Class=O.Class AND  Node=O.Node AND Phase=O.Phase AND Experiment=O.Experiment) As SumOfDemand_Filled
FROM SmallSample O
GROUP BY Class, Node, Phase, Experiment, Day;

This next query finds the minimum day at which each requested quantity ins met:

Query GetDayMet:

SELECT Phase, Experiment, Node, Class, Day, SumOfDemand_Qty, SumOfDemand_Filled, (SELECT Min(Day) AS MinOfDay
FROM CumalativeSumTest
WHERE (((SumOfDemand_Filled)>=M.SumOfDemand_Qty))
GROUP BY Phase, Experiment, Node, Class
HAVING (((Phase)=M.Phase) AND ((Experiment)=M.Experiment) AND ((Node)=M.Node) AND ((Class)=M.Class))) AS DayMet
FROM CumalativeSumTest AS M;

Now... while this seems to work fine for a small set of data, it takes a REALLY LONG time to run in access. Can someone please look at these queries and tell me how to tweak it because it needs to run on a table with about 760,000 rows and there's no type of progress bar to know how long its going to take or how far along it is. I could really use some help on this guys and gals. Thank you in advance!

P.S. Would running these queries on the same DB on SQL Server SIGNIFICANTLY speed up the process?

[womalley]

there are some really cool ideas in THIS article that you may be able to apply to your solution.

Will

[Krzemo]

To womalley:

Solution 1 uses a temporary table and a cursor and executes extremely fast. I must admit that my original intent was to show this solution as the way *not* to solve the problem. In general, I tell developers to avoid cursors at all costs. In this particular case, however, the cursor approach is much quicker than the single SELECT solutions.

But unfortunetly this solutions are for MsSQL and not for MsAccess..

To TSmooth:
1) First U have to make complex index on table SmallSample with columns Class, Node, Phase, Experiment, Day.

2) Move HAVING (replace it) with WHERE clause ie:

...
WHERE (((Phase)=M.Phase) AND ((Experiment)=M.Experiment) AND ((Node)=M.Node) AND ((Class)=M.Class)) AND
(((SumOfDemand_Filled)>=M.SumOfDemand_Qty))
GROUP BY Phase, Experiment, Node, Class

) AS DayMet
...

using HAVING is very cost intensive since it is evaluated AFTER proceeding ALL records (no prefiltering occures)

3) If it won't work: Identify what part of it executes slowly if it is first select then U need to change first select to other solution

Best regards,
Krzemo.

[womalley]

To: Krzemo

In general, I tell developers to avoid cursors at all costs

How nice that you over looked that

I was hoping that solution 3 from the article was the one that would be looked at and talked about as I have stated before cursors are slow. I am really getting tired of talking about this with you..and it is sad that this little spat has spilled into another users post.

To TSmooth:
The article uses standard SQL syntax and if you look other at solution 3 it is a good fit for your problem.

If you want more information look into Derived Tables.

Good luck,
Will

[TSmooth]

I appreciate all the help you guys have provided. Due to time constraints and the inability to really see the progress of the querying, I ended up writing a quick and dirty VB.NET app to calculate the cumulative sums and the day on which each demand is met. This not only provided a progress indicator, but also sped things up considerably.

I would however like to look into the recommendations you guys provided to learn them for any future problems that may come up.

Again, thank you all for your help!

Tom