Convert Crystal query to SQL - Please help

[Wyattda]

Hello,

I've created a query in Crystal and need to convert it to SQL to be run in a VB6 application. The query is as follows:

if {Calls.Date} = {Calls.DateReceived} and {Calls.Date} = Calls.DateTaken} and {Calls.TimeTaken}<>{Calls.Time} and
datediff("n",{Calls.Time},{Calls.DateReceived})>=.5 and
datediff("n",{Calls.Time},{Calls.TimeTaken}) <= 30

This Crystal query gives me the number of calls taken within the first half hour. Now in SQL i'm not sure how to get the datediff part of the query to work. This is what I have so far:

SELECT Calls.Date, Calls.DateReceived, Calls.DateTaken, Calls.Time, Calls.TimeTaken
FROM Calls
WHERE (((Calls.Date)=[DateReceived]) AND ((Calls.DateReceived)=Date()) AND ((Calls.DateTaken)=[Date]) AND ((Calls.Time)<>[TimeTaken]) AND ((Calls.TimeTaken)=[Calls]![Time] And (Calls.TimeTaken)=[Calls]![TimeTaken]));

As you can see there is no time comparison to get calls within the first 30 mins the call was taken.

Any help getting datediff("n",{Calls.Time},{Calls.DateReceived})>=.5 and
datediff("n",{Calls.Time},{Calls.TimeTaken}) <= 30 converted to SQL would be great.

Thanks,
Wyatt

 

[Crowley16]

you can just use a - operator for this, *I think* however you'd need to reformat/calc this to be hh:mm:ss

since you're only testing for 1/2 hr you might get away with not calculating...

not tried it out myself, so no guarantees

--------------------
Procrastinate Now!

 

[cmmrfrds]

You could just subtract the dates (difference is days as a number i.e. nnnnn.nnnnnn) and convert to minutes.
There are 1440 minutes in a day.
For example.
Dim date1 As Date, date2 As Date
date1 = "01/01/05 12:00:00"
date2 = "01/01/05 12:40:00"
Debug.Print "date diff (minutes) = "; (date2 - date1) * 1440
This gives the minutes as a floating point number.

(([Calls.Time] - [Calls.TimeTaken]) * 1440) > 30
Or convert the floating point to an integer
Clng(([Calls.Time] - [Calls.TimeTaken]) * 1440) > 30