I am having a mental spiral down and need some help - Brain freeze...

I’m pretty sure this is simple but I can’t even get started
I have:
Code #
bgnDate
endDate
dailyCost ( pre-calculated )

using SunDialServices DateTran.db for
TheDate, MonthNumber, Day, DaysInMonth

I have selected a subset of records from DateTran for the exact Day for every
bgnDate with the above info

I have selected a subset of records from DateTran for the exact Day for every
endDate with the above info

I have selected a subset of records from DateTran for every month (Day 1)
between the bgnEnd and endDate via Code with the above info

User will enter the bgn & end Dates.
NEED TO scan PRIV tables to calculate money amounts via Code by Month to
be stored in a WORK table.
EX if Code = 040568 & bgnDate = 1/1/04 and endDate = 4/15/07 then
Jan = dailyCost * ( 1/31 * 22 ) : the Jan tbl_field would have this amount
for 2004 + 2005 + 2006 + 2007
( dailyCost times Jan 1 divided by days in month times working
days of 22 which is the # I was told to use )

Apr = dailyCost * ( 1/30 * 22 ) the Apr tbl_field would have this amount
for 2004 + 2005 + 2006 +
dailyCost * ( 15/30 * 22 ) for 2007

my scan is not working at all except to update the Month fields with $0.00

HELP ! HELP! HELP!

it's kinda hard to troubleshoot a scan that we can't see..


--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales - Corel CTech Paradox

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards
"JeaNette" <harrisjr (AT) bwsc (DOT) org> wrote

oh well,
I was hoping for help without having to DISPLAY my ineptness...
:-(

HTH !!!

bgn_tbl.open(":PRIV:BDates")
cFlow_tbl.open("CASHFLOW")
cFlow_tbl.edit()

scan bgn_tbl:

st = bgn_tbl.StreetCode
bgn = bgn_tbl.BgnDate
end = bgn_tbl.EndDate
day = bgn_tbl.Day
last = bgn_tbl.DaysInMonth
month = bgn_tbl.MonthNumber

if cFlow_tbl.locate("StreetCode",st, "BgnDate",
bgn, "EndDate", end) then
st_c = cFlow_tbl.StreetCode
bgn_c = cFlow_tbl.BgnDate
end_c = cFlow_tbl.EndDate
daily = cFlow_tbl.DailyCost

if bgn_tbl.TheDate = bgn_c then
if bgn_tbl.MonthNumber = 1 then
cFlow_tbl.Jan = cFlow_tbl.Jan +
(daily * (day/last)) * 22
endif
ongoing for months 2 - 12...


"Steven Green" <greens (AT) diamondsg (DOT) com> wrote:

JeaNette wrote:
Have you considered liquid nitrogen? I'm pretty sure it will freeze
you brain... <gdrvvf>

Well, I guess I need to tighten the lid on that can of liquid nitrogen
some more....

Slight improvement.... Immediate thoughts:

1. Show the code as Steve said.

2. Given the whole 1/31 * 22 thing, are you sure the numbers aren't
just less than 0.01 and therefore *appear* to be zero? What if you go
to the table move to the field and hit F2 - what's the number then?

3. Is "dailyCost" really a per-calendar-day cost or a per-working-day
cost? This whole bit just seems weird to me...

Liz

JeaNette wrote:
OK, read the other post... Can we maybe start over. Given the above,
can you confirm dailyCost is per-calendar-day or per-working-day and
if the latter, how do we determine the number of working days in a
given month - do you have this in your DateTran table or...?

Meanwhile, if bgnDate and endDate can be in different months (take Jan
- May), then, if I understand right, you want:

Code | Jan | Amount (dailyCost*someDays)
Code | Feb | Amount
Code | Mar | Amount

....etc, where there could be multiple codes per month and multiple
months per code, right?

Liz

I am working a construction App & the Proj MGR (PM) is looking for cashflow
by month for all contracts.
I CAN say that I don't understand a lot of what the reports I create actually
relate BUT as long as the User is happy I am happy...

the bgn & end dates are entered for a streetcode where construction is expected
to bgn on a date and end on a date

F2 DID NOT display $$ amts
multiple codes per month - yes
multiple months per code - yes
daily cost is the total contract $ / end_date - bgn_date per PM
working days is 22 per PM
prorate per PM, EX: Jan = Jan + dailyCost * ( 1/31 * 22 )

you got me thinking - maybe this pro-rate should be:
Jan + dailycost * (( 31-1) /31 * 22)

my thinking on the scan....
3 selects
saved in priv:BGNDATES (1)
Code -045
BgnDate 1/1/2004
EndDate 4/15/2007
Above from cashflow

TheDate - 1/1/2004
MonthNumber - 1
Day - 1
DaysInMonth - 31
Above from DateTran

StreetCode will appear only once


saved in priv:ENDDATES (2)
Code -045
BgnDate 1/1/2004
EndDate 10/15/2005
Above from cashflow

TheDate - 10/15/2005
MonthNumber - 3
Day - 15
DaysInMonth - 30
Above from DateTran

StreetCode will appear only once


saved in priv:BandEdATES (3)
Code -045
Above from cashflow

TheDate - 1/1/2004 thru 10/1/2005
MonthNumber - 1
DaysInMonth - 31
Above from DateTran

# of records via StreetCode will be the # of 1st of the months between bgn
& end dates this Ex is 22 records

My thought was to get:
1. all bgn month $ amts
2. all $ amts between bgn and end month
3. all end month $ amts

JeaNette wrote:
OK, read the other post... Can we maybe start over. Given the above,
can you confirm dailyCost is per-calendar-day or per-working-day and
if the latter, how do we determine the number of working days in a
given month - do you have this in your DateTran table or...?

Meanwhile, if bgnDate and endDate can be in different months (take Jan
- May), then, if I understand right, you want:

Code | Jan | Amount (dailyCost*someDays)
Code | Feb | Amount
Code | Mar | Amount

....etc, where there could be multiple codes per month and multiple
months per code, right?

Liz





"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote:

CODETBL.DB | CodeNo | bgnDate | endDate | dailyCost |
DATETRAN.DB | TheDate | MonthNumber | DaysInMonth |
....would this not get you one row per month per code number? Could
you not then just scan the table and calc one total per row?

Liz


JeaNette wrote:

I think you might be making this harder than it really is. You just need to
make the calendar table do a little more work for you.

As I understand it, you have

Code bgnDate endDate dailyCost
040568 1/1/2004 4/15/2007 ? <= can you give an example?

What do you want for a result? Show the fields and values those fields
would have given the example data.

Code Year Month Cost
040568 2004 Jan $a.00
040568 2004 Feb $b.00
....
040568 2007 Mar $yy.00
040568 2007 Apr $zz.00

Something like that? Or maybe aggregated for all years for the given month?

Your calendar table should have a flag for any attribute you care about. So
if it is a working day/weekday/holiday/weekend day/whatever, you should be
able to tell. Simplest approach is to build it with a column for each, and
1 for yes and 0 for no. Then you can do sums across days and get actual
counts.

In your case, maybe 22 has to be used as some sort of standard. Except
1/31*22*dailyCost for the entire month of January comes up to be less than
15/30*22*dailyCost for half the month of April. I don't get that formula.

Anyhow, the whole point to a calendar table is to simplify this sort of
thing. I suspect it could easily be done in a query if you have the needed
date attributes. Answer the above questions and we can probably make this
happen.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

HTH !!!
my scan is not working at all except to update the Month fields with $0.00

bgn_tbl.open(":PRIV:BDates")
cFlow_tbl.open("CASHFLOW")
cFlow_tbl.edit()

scan bgn_tbl:

st = bgn_tbl.StreetCode
bgn = bgn_tbl.BgnDate
end = bgn_tbl.EndDate
day = bgn_tbl.Day
last = bgn_tbl.DaysInMonth
month = bgn_tbl.MonthNumber

if cFlow_tbl.locate("StreetCode",st, "BgnDate",
bgn, "EndDate", end) then
st_c = cFlow_tbl.StreetCode
bgn_c = cFlow_tbl.BgnDate
end_c = cFlow_tbl.EndDate
daily = cFlow_tbl.DailyCost

if bgn_tbl.TheDate = bgn_c then
if bgn_tbl.MonthNumber = 1 then
cFlow_tbl.Jan = cFlow_tbl.Jan +
(daily * (day/last)) * 22
endif
ongoing for months 2 - 12...


I am working a construction App & the Proj MGR (PM) is looking for cashflow
by month for all contracts.
I CAN say that I don't understand a lot of what the reports I create actually
relate BUT as long as the User is happy I am happy...

the bgn & end dates are entered for a streetcode where construction is expected
to bgn on a date and end on a date

F2 DID NOT display $$ amts
multiple codes per month - yes
multiple months per code - yes
daily cost is the total contract $ / end_date - bgn_date per PM
working days is 22 per PM
prorate per PM, EX: Jan = Jan + dailyCost * ( 1/31 * 22 )

you got me thinking - maybe this pro-rate should be:
Jan + dailycost * (( 31-1 +1) /31 * 22)

my thinking on the scan....
3 selects
saved in priv:BGNDATES (1)
Code -045
BgnDate 1/1/2004
EndDate 4/15/2007
Above from cashflow

TheDate - 1/1/2004
MonthNumber - 1
Day - 1
DaysInMonth - 31
Above from DateTran

StreetCode will appear only once


saved in priv:ENDDATES (2)
Code -045
BgnDate 1/1/2004
EndDate 10/15/2005
Above from cashflow

TheDate - 10/15/2005
MonthNumber - 3
Day - 15
DaysInMonth - 30
Above from DateTran

StreetCode will appear only once


saved in priv:BandEdATES (3)
Code -045
Above from cashflow

TheDate - 1/1/2004 thru 10/1/2005
MonthNumber - 1
DaysInMonth - 31
Above from DateTran

# of records via StreetCode will be the # of 1st of the months between bgn
& end dates this Ex is 22 records

My thought was to get:
1. all bgn month $ amts
2. all $ amts between bgn and end month
3. all end month $ amts


in the end: the cashflow table for a given code should have a total $ amt
for ALL of the Januarys in the bgn / end date range


"Larry DiGiovanni" <nospam@nospam> wrote:

That makes a little more sense. Not much. It looks like you are trying to
get the proprated number of workdays in a month. My earlier suggestion was
that you could find the actual number of workdays in that month between the
supplied date range.

If you post a few rows of the answer you are looking for given some simple
input it would be easier to help you.

As I said before, modifying your calendar table will probably greatly
simplify this.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.