Clipper - Filtering between two dates

Author

Filtering between two dates

Pablo García

2005-03-21, 3:55 pm

I'm trying to get a report of sales between two dates. How do you recommend
doing it?

I've tried the set filter option, and also doing it:

do while .not. eof()
if date<date1 .and. date>date2
....
dbskip()
else
dbskip()
endif
enddo

But doesn't seem to work. I also tried reindexing de dbf file before
entering the "do" loop, and didn't get much further.

I'm a little lost now, can't find the solution.

Any help would be appreciated.

Thank you.
Pablo

Joe Wright

2005-03-21, 8:55 pm

Pablo García wrote:
> I'm trying to get a report of sales between two dates. How do you recommend
> doing it?
>
> I've tried the set filter option, and also doing it:
>
> do while .not. eof()
> if date<date1 .and. date>date2
> ....
> dbskip()
> else
> dbskip()
> endif
> enddo
>
> But doesn't seem to work. I also tried reindexing de dbf file before
> entering the "do" loop, and didn't get much further.
>
> I'm a little lost now, can't find the solution.
>
> Any help would be appreciated.
>
> Thank you.
> Pablo
>
>

beg = '20040101'
end = '20050101'

sold = 0
use sales
do while ! eof()
if dtos(sell_date) >= beg .and. ;
dtos(sell_date) < end
sold = sold + amount
endif
skip
enddo

--
Joe Wright mailto:joewwright@comcast.net
"Everything should be made as simple as possible, but not simpler."
--- Albert Einstein ---

Przemyslaw Czerpak

2005-03-22, 3:55 am

On Mon, 21 Mar 2005 19:05:13 -0500,
Joe Wright <joewwright@comcast.net> wrote:
> Pablo García wrote:
> beg = '20040101'
> end = '20050101'
> sold = 0
> use sales
> do while ! eof()
> if dtos(sell_date) >= beg .and. ;
> dtos(sell_date) < end
> sold = sold + amount
> endif
> skip
> enddo

Is it your computer too fast? ;-)
Comparing dates is many times faster the converting them to
strings and comparing strings.

best regards,
Przemek

Stephen Quinn

2005-03-22, 3:55 am

Pablo

I don't recomend you use 'DATE' as a field name

INDEX ON DATE FOR DATE < Date1 .AND. DATE > Date2 TO DATEINDX
DBGOTOP()
DO WHILE ! EOF
// Do something with his date range
DBSKIP()
ENDDO
--
HTH
Steve

Pablo García

2005-03-22, 8:55 am

Thank you all. I think the real problem was that the system was taking some
dates wrong. When I put "century on" and made the report, some weird dates
came up: some in the year 1905 and others in 2005, I don't know why. Then
y added "set epoch to 1950" and that was fixed.

Thanks.

Pablo

"Stephen Quinn" <steveqNOSPAM@integritynet.com.au> escribió en el mensaje
news:3a9iunF62maqqU1@individual.net...
> Pablo
>
> I don't recomend you use 'DATE' as a field name
>
> INDEX ON DATE FOR DATE < Date1 .AND. DATE > Date2 TO DATEINDX
> DBGOTOP()
> DO WHILE ! EOF
> // Do something with his date range
> DBSKIP()
> ENDDO
> --
> HTH
> Steve
>
>

Pablo García

2005-03-22, 8:55 am

I like that solution Stephen, but the problem is that Date1 and Date2 are
selected by the user before creating the report, and with your code I would
be creating an Index every time I use this report. Could this be a problem?

"Stephen Quinn" <steveqNOSPAM@integritynet.com.au> escribió en el mensaje
news:3a9iunF62maqqU1@individual.net...
> Pablo
>
> I don't recomend you use 'DATE' as a field name
>
> INDEX ON DATE FOR DATE < Date1 .AND. DATE > Date2 TO DATEINDX
> DBGOTOP()
> DO WHILE ! EOF
> // Do something with his date range
> DBSKIP()
> ENDDO
> --
> HTH
> Steve
>
>

Ray Marron

2005-03-22, 3:55 pm

"Pablo García" <pgarcia@chasque.net> wrote in message
news:114002i86f2ff3c@news.supernews.com...
> I like that solution Stephen, but the problem is that Date1 and Date2 are
> selected by the user before creating the report, and with your code I
would
> be creating an Index every time I use this report. Could this be a
problem?

If you run the report fairly often, it would be wasted effort to keep
reindexing like that. I would just have a permanent index on the date field
in question. If you're using Comix (or something with similar
functionality), you can set a range on the index and loop through all
visible records. Otherwise, just softs

to the minimum date and loop
until the date field exceeds the maximum (or eof, whichever comes first).

--
Ray Marron

Stephen Quinn

2005-03-22, 8:55 pm

Pablo

> selected by the user before creating the report, and with your code I would
> be creating an Index every time I use this report. Could this be a problem?

I'd create & use a temp index whenever I printed the report

--
HTH
Steve

cal

2005-03-22, 8:55 pm

Pablo,

What RDD is being used by your Clipper app?

If it is Comix, you can index on dtos(<datefield> )

then use cmxSetScope() to scope the records to the range you desire.
You can also allow the program to prompt for "from" and "through"
dates and use these dates to scope the index to the desired date
range. I believe the Six driver provides this functionality as well.

Cal

On Tue, 22 Mar 2005 09:24:53 -0300, "Pablo García"
<pgarcia@chasque.net> wrote:

>I like that solution Stephen, but the problem is that Date1 and Date2 are
>selected by the user before creating the report, and with your code I would
>be creating an Index every time I use this report. Could this be a problem?
>
>
>"Stephen Quinn" <steveqNOSPAM@integritynet.com.au> escribió en el mensaje
>news:3a9iunF62maqqU1@individual.net...
>

Andreas Moroder

2005-03-23, 3:55 am

Przemyslaw Czerpak schrieb:
> On Mon, 21 Mar 2005 19:05:13 -0500,
> Joe Wright <joewwright@comcast.net> wrote:
>
>
>
> Is it your computer too fast? ;-)
> Comparing dates is many times faster the converting them to
> strings and comparing strings.
>
> best regards,
> Przemek
Hello Przemak,

is direct date comparison not limited only to equality ?

Bye
andreas

Przemyslaw Czerpak

2005-03-23, 3:55 pm

On Wed, 23 Mar 2005 07:44:40 +0100,
Andreas Moroder <Andreas.moroder@[nospam]> wrote:
> is direct date comparison not limited only to equality ?

No. The internal date representation is 32bit integer number.
It is number of days from Julian's day until now.
Any operations on date vars are simple integer number manipulations
(you can use +, -, <, <=, >, >=) when operations are string are much
more complex (the string contents is comparing char by char respecting
the used code page, set exact on/off, trailing spaces, etc.)

best regards,
Przemek

Dave Pearson

2005-03-23, 3:55 pm

* Przemyslaw Czerpak <druzus@polbox.com>:

> Any operations on date vars are simple integer number manipulations (you
> can use +, -, <, <=, >, >=) [SNIP]

And ++ (pre/postfix), -- (pre/postfix), +=, -=.

--
Dave Pearson | OSLib - Timeslice release functions.
http://www.davep.org/ | eg - Norton Guide reader for Linux.
http://www.davep.org/clipper/ | weg - Norton Guide reader for Windows.
http://www.davep.org/norton-guides/ | dgscan - DGROUP scanner for Clipper.

Sponsored Links