ranged datetime query problem

[Ruslan Zasukhin]

On 6/1/06 9:04 AM, "Sean Wilson" <snw at paradise.net.nz> wrote:

>> Here's my SQL query:
>> SELECT SUM(time_spent) FROM tblTimes
>> WHERE
>> (start_time BETWEEN '1/5/2006 0:00:00:000' AND '31/5/2006 0:00:00:000')
>> AND
>> (stop_time BETWEEN '1/5/2006 0:00:00:000' AND '31/5/2006 0:00:00:000')
>> AND
>> (job_name_ptr->job_name = 'Job Name');
> 
> A little more probing suggests it's my use of the -> operator that's
> failing me and not necessarily date ranges. I need to do some more
> poking around before I can be categorical about where my problem
> comes from, though if anyone can spot anything obvious...
> 
> Even replacing the following clause:
> (job_name_ptr->job_name = 'Job Name')
> with
> (job_name_ptr = (SELECT recID from tblJobs WHERE job_name = 'Job Name'))
> yields a SUM() on an unrelated job name.
> So it seems I'm grabbing time_spent related to the wrong or incorrect
> job_name. Maybe I set up my objectPtr incorrectly, or I could be
> using the link table to simplify the SQL query.
> It doesn't matter what job_name I feed the above clauses, I get
> nothing back unless I use the job_name whose recID = 1 *even though*
> there are /no/ related records in tblTimes
> 
> I'm thoroughly confused now. Database and queries (~8 KB zipped) are
> available if anyone is interested.

Hi Sean,

Add this into Mantis please. And db also.
We will be able test in viSQL

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]