Thanks! That is so much better than what I was doing before.
I do have another question about how to better automate this,
with regard to
the "thisMonth" variable. You see, I don't always get my data
in a timely
fashion - so we could be in June, and still only have data
posted through
April. As it stands, I have to insert the month value of the
lastest
available data into a table, and reference that to find the
"thisMonth"
value. I'm wondering, however, if by running a query summing
the current
months, I can then find the last month with values >0 ...
this would tell me
what my "thisMonth" value is. Problem is ... while I can
dream up the
concept, I'm not sure how to DO it in SQL. Is it doable? Is
there a way I
can pull the month from a query like this to use as the
"thisMonth" variable
you suggested below?
"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in
message
news:er1n1q$duc$1@forums.macromedia.com...
> > 1) What is the most efficient way to sum the
COLUMNs across the row to
> gain
>> my YTD values, and still only grab the ones relevant
for my current YTD?
>> In SQL? On my page?
>
> Generally, databases shouldn't be used for mathematical
calculations that
> don't involve aggregating the data somehow, but for a
simple addition, you
> could do it either way. Because the columns being added
together change,
> I would suggest doing this in your ASP page. You can set
a month variable
> (thisMonth = Month(Date())) and use that to access your
sales figures. A
> recordset can be accessed by index or by key, so if your
recordset columns
> are [Jan, Feb, Mar, ..., Dec], then you can add together
easily:
> <%
> YTD = 0
> For X = 1 To ThisMonth
> YTD = YTD + myRS.Fields.Item(X-1).Value
> Next
> %>
>
> Why (X-1)? Because the recordset counts from 0. If you
have other
> columns in the recordset, then just adjust the index
accordingly. For
> example [ID, Jan, Feb, ..., Dec] would use just (X) to
reference the right
> month since Jan = Month 1 = Column(1) in the recordset.
>
> If you have NULL values, you have to test for them
first, because anything
> + NULL = NULL. The isNull() function will help with
that.
>
>> 2) How can I automate the YTD calculations of PREV
to include ONLY the
>> "current" months (up to the currently imported data)
... allowing for the
>> fact that the latest month may have ZERO sales?
>
> I actually answered this with the first question. You
can get the current
> month and then use that month number as the max value
for a FOR loop that
> adds each month in turn until the current month has been
reached.
>
6
Replies
AdChoices