From: harrisjr@bwsc.org
Liz,
I've got it now.
Sometime ago I had done a draft REPORT using the STREETCOST / number of
days(End-BGN)
to get a daily cost.
Using the daily cost and the # of days from DateTran.db I got from the
NewsGroup
(what a life saver) for the END/BGN dates I produced a REPORT where the Jan-Dec
amounts did not rollup to the STREETCOST.
I prepared another draft REPORT using number of days(End-BGN)+1. The Jan-Dec
amounts did not rollup to STREETCOST.
I was convinced that I was doing something wrong.
I will relay to the The Manager who requested the REPORT that the Jan-Dec
rollup is not going to happen.
THANKS AGAIN for your help and PATIENCE !
Liz McGuire wrote:
>Jeanette,
>
>In all cases, if you want both the end date and the beginning date to be
>included in your count, then the math is:
>
>(End-Begin) + 1
>
>..doesn't matter when end is, nor when begin is. Doesn't matter if you
>sum the results of one or more such calculations. It is always
>(End-Begin) + 1 to get the total number of days, including the end and
>begin days.
>
>You should be adding 1 to the a) and b) rows as well as the c) rows.
>Otherwise, in the c) rows, you're including the Begin date in your count
>and in the a) and b) rows you're excluding the Begin date.
>
>If you don't want to include the begin date or the end date, you need to
>tell us what dates you do want to include, but you've got to be
>consistent. Again, see my printed list - you've got the # column mixed
>up for the a) rows and the proper answers are 108 and 91, if you're
>going to include the Begin date as you're doing in the c) rows.
>
>Also, IMO, you've got to forget about whether STR$ is "right" or not -
>first get the date count how you want it, then worry about what you want
>to do with the date count. (Seems you're trying to make the date math
>give you a specific dollar value rather than trying to figure out why
>the dollar value isn't what you expect given a factual date count.)
>
>If something doesn't calc back to STR$, then STR$ is not being
>consistently calculated. I've got no idea what the formula is, nor what
>math has been done where before we get to what's below.
>
>I only know how to do date math.
>
>Liz
>
>
>Jeanette wrote:
>>
>> I did mis-understand what you meant by INCLUSIVE.
>> BUT I AM STILL HURTING :-(
>>
>> Year StreetCODE STR$ Begin End #
>> a) 2005 06520 $282,208.40 09/15/2005 12/31/2005 90
>> a) 2006 06520 $282,208.40 01/01/2006 04/01/2006 107
>>
>> b) 2006 29980 $71,442.00 01/15/2005 02/01/2005 17
>>
>> c) 2005 14030 $69,133.25 01/01/2005 01/10/2005 9 +1=10
>> c) 2006 14030 $69,133.25 01/01/2006 04/01/2006 46+1= 47
>>
>> If 1 is added to a) Total # of days(TNOD) as one entry
>> [ 4/1/06 - 9/15/05 ] TNOD is 198 and calcs back to STR$
>> YET
>> If 1 is added to a) # of days as 2 entries
>> TNOD is 199 and DOES NOT calc back to STR$
>> BUT
>> If 1 is added to b) TNOD is 18
>> [ 2/1/05 - 1/15/05 ] DOES NOT calc back to STR$
>> HOWEVER
>> If TNOD is 17 for b)
>> [ 2/1/05 - 1/15/05 ] DOES calc back to STR$
>>
>> from all of the above it seems
>> If the date range is in 2 years or more add 1 day to each range
>> UNLESS
>> d) The date range in 2 years or more is in consecutive months then add
1
>> day to TNOD
>> OTHERWISE
>> Use (END-BGN) as # of Days
>>
>> How do I use QBE to do d) line ??? :-)
>>
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
