Best Method?

[Steve Wasser]

I posted this buried in another thread, so forgive me for being redundant.
Maybe someone can point me to the right method of doing this.

I'm building a web app that a user will enter a date, and will get daily
inventory from month beginning to that date. It calculates inventory for 44
different petroleum locations (terminals). All the data is stored in SQL
2000. There is one table for opening inventory for the month, each row being
a different terminal, the columns being beginning gallons for each product.

term, inventory_date, unleaded, premium, diesel, ethanol

We have daily activity, which means inventory can be depleted or added:
lifting, adjustment, delivery. Adjustment can be + or -. Like when we buy
more gas from the refinery that adds to our inventory. With me so far? Good.
We keep three tables for activity: lifting, adjustment, delivery. The
inventory activity will need to calculate against that table I mentioned
above. We have a stored procedure that pulls the activity from the three
tables and creates a recordset that looks like:

term, product_type, lifted_date, liftedUnl, liftedPrem, liftedDie,
liftedEth, deliveredUnl, deliveredPrem, deliveredDie, deliveredEth,
adjustedUnl, adjustedPrem, adjustedDie, adjustedEth

Example, if the product_type is G47, which is unleaded, only liftedUnl,
deliveredUnl and adjustedUnl will have data, the rest will be null. The next
line could have Ethanol activity, and so forth.
Since the data isn't structured the same as the table above, I have to
figure out a way of pushing the dataset into something I can use to
calculate against the opening inventory above by date, by terminal.You dizzy
yet? I need to create a report, one "page" per terminal, that shows the
beginning inventory, then any day that had activity show that activity and
subtract from the inventory, then give the total inventory remaining. Its
been suggested to use a Hashtable, or ListArray, which sounds good, but the
execution is eluding me. I have to extract each row from the stored
procedure's dataset, figure out which terminal to calculate against,
calculate it, then present it on the web page. I know it will end up being
deceptively easy, but I am having trouble visualizing how to pivot the data.
I was almost thinking of chunking the dataset into 44 different tables, then
iterating through the stored procedure dataset and storing it in a table
according to terminal, sorting by date. Have I lost you? Like, I'm pretty
new to c#, so a typical example of my type of errors are to assign a datarow
to a list array like this:

foreach (DataRow dr1 in MyTable1.Rows)
{
hashme.Insert(count, dr1);
count++;
}
count = 0;

Of course, I filled hashme with 44 system.data.datarow objects instead of
the actual data. My code is sloppy, redundant, inefficient, and someone
already suggested I refactor the code. Well, I have to write it first before
fixing it.

 

[Jeff S]

Hi Steve,

<<You dizzy yet?>>>

No, but close. From what I gathered, you need to grab some data from a SQL
database, do some stuff to it, do some more stuff to it, and then produce a
report - Yes?

<<<I have to figure out a way of pushing the dataset into something I can
use to calculate against the opening inventory above by date, by terminal.This sounds like a candidate for TEMP tables in SQL Server... "push" your
dataset into a temp table. After that you can query against the temp table
from within the stored procedure that put the data there.

<<<<I have to extract each row from the stored procedure's dataset, figure
out which terminal to calculate against, calculate it, then present it on
the web page.>>>>
Be careful not to confuse 'what' with 'how'. 'What' is the requirement - to
present calculated data on a web page. 'How' is totally debatable; you
present only one of many possible ways to get the calculation done. In
short, you don't "have to extract each row from the stored procedure's
dataset...". There are many ways to get your calculated data. I can't
provide a suggestion for a better way to get what you need because I'm not
clear on what it is that you need - I don't know what the calculations are,
and don't want to preclude the possibility that they can/should be done in
SQL Server in a stored procedure.

Without fully understanding your requirements, from what I did gather, I'd
suspect that *all* of your calculations and data preparations for the
reports can be accomplished within stored procedures using T-SQL and perhaps
a couple of temp tables into which interim results are stored. If this is
the case, then the reports would be a snap. Just execute a stored procedure
that returns the data and bind the results to your report (granted, the
stored procedure might be complex).

<<<< I know it will end up being deceptively easy>>>>
Not necessarily - but the solution will be much easier to come up with if we
are sure to keep 'what' separated from 'how'.

I'm sure we could provide you with better and more specific suggestions if
you could present us with plain-english P-code of what you need to
accomplish - some step-by-step description of 'what' it takes to create the
data for the final reports. Pretend you are telling someone how to do it BY
HAND (paper and pencil - no coding involved to divert our attention from
what needs to be accomplished). This will give us a clear target for which
we can provide implementation suggestions that match your skill set or
comfort level.

Jeff

MCSD, MCDBA, MCSA MCSE