Building a DateTime SQL from Main Table

[mccartmd]

Trying to build an SQL. . .
Eample of thisDateTime. . .12:03:02AM . . .want to select records of the "same" table that are >=12:03:02AM - 5 minutes or =<12:03:02AM - 5 minutes

Public vthisDateTime
vThisDateTime =12:03:02AM
Select Main.Thisdatetime, Main.Diff
from
Contact1!Main
innerjoin Contact1!Main Main_A
on Main.ThisDateTime between ((Main.ThisdateTime)-
(minute(MainThisDateTime-5) and (minute(MainThisDateTime+5))
into Cursor CursorVtime

Can you help me with this SQL, have datetimes (over 20k) in a table and trying see if I can pull records that are 5minutes => than or 5minutes =< vThisDateTime? have tried
Julian(),hour(),minute() and second(). . .and can't seem to get into a Cursor

 

[Dan Freeman]

You need to clarify: Datetime is a data type, and it doesn't look like what you're showing. It looks like you're working with only the time portion of a Datetime data type.

(Terminology is important if you want an answer to your question. If everyone gets blinded by your use of Datetime but you're really working with a string, you'll get all the wrong answers, eh?)

I suspect you can nuke the entire inner join in favor of something like this:

WHERE Between(Main.ThisdateTime, vThisdateTime-(5*60), vThisdateTime+(5*60))

But that's a really wild guess because you haven't been very clear what you're actually after, and the variable vThisdateTime isn't a DateTime value either.

 

[Mike Lewis]

I agree with Dan that you need to clarify the question.

In particular, you have this line of code:

vThisDateTime =12:03:02AM

That doesn't make sense in VFP. Either vThisDateTime is a string, in which case the time needs to be delimited with quotes or square brackets:

vThisDateTime ="12:03:02AM"

or it's a datetime, in which case you need to write it something like this:

vThisDateTime =DATETIME(2011, 9, 25, 12, 3, 2)

Note that I've had to insert a date here (9 Sep 2011) because you can't have a datetime without a date.

Once we know which data type we are dealing with, we should be able to help you.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mccartmd]

Creating a Cursor called "CursorVTime" from the base table called Main.

Select CursorVTime
Go Top
MESSAGEBOX(CursorVTime.thisdatetime)
Messagebox Results are 01/20/2011 12:00:06 AM

Then trying to create a second Cursor called CursorVTime2 from same base table, where I am looking to capture all records in "CursorVTime2" that are within 5 minutes plus or minus of "CursorVTime.thisdatetime"

Select CursorVTime
MESSAGEBOX(mainparsehourly.thisdate)
Messagebox Results are 01/20/2011 12:00:06 AM

I am able to do a single view of joining main to main_a joining inner join thisdatetime to thisdatetime and get exact matches to the second, which leaves many out, but looking to get those records that are give or take 5 minutes. . .make sense (. . .maybe my whole approach is not right)

 

[mccartmd]

. . .field type datetime, width 8 results 01/20/2011 12:00:06 AM

 

[Olaf Doschke]

As you now said your field is datetime, you can take dan's advice on between() - the VFP between() function - or do between the ANSI SQL way like you did.

Your logic besides other problem mainly fails about how to add minutes. The math with datetimes alwas involves adding or subtracting seconds, so 5 minutes need to be converted mathematically via 5*60 to 300 seconds. If you query via [DateTimeA between DateteimeB-300 and DatetimeB+300] you get all records in that time span, which is about what Dan already suggested.

On the other side you need to make a join condition involvong main and main_a to really make the join on other records of the table. Your condition only involves comparing Main.ThisDateTime with an expression on Main.ThisDateTime and thus you compare the record with themselves only, that is indeed filtering, not joining. If you only want to filter records, don't join at all, add that condition to the WHERE clause.

In a simple example:

Select fields from table1 where table1.datetime between datetime()-300 and datetime()+300

would filter records in table 1 to those with a datetime in the range of NOW+/- 5 minutes

Select fields from table1
inner join table2 on table2.datetime between table1.datetime-300 and table1.datetime+300

would join records of table2 to records of table1, on the condition table2 records are within a range of a datetime in table1 +/- 5 minutes.

And if table1 and table2 actually are the same table you need to make two different aliases, like you did with main and main_a, but you also need to put those two different aliases in your join condition, otherwise your join condition would just be based on the values of one side of the join and not make the join via values of the other side of the join.

Bye, Olaf.

 

[Mike Lewis]

Mccartmd,

If I've understood it right, you don't need a join. You only need to select records from CursorVTime that meet a certain criterion.

If that's right, this would do it:

SELECT * FROM CursorVTime ;
  WHERE thisdatetime BETWEEN thisdatetime - 300 AND thisdatetime + 300 ;
  INTO CURSOR CursorVTime2

If that's not right, then I clearly still don't understand the question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

Then trying to create a second Cursor called CursorVTime2 from same base table, where I am looking to capture all records in "CursorVTime2" that are within 5 minutes plus or minus of "CursorVTime.thisdatetime"

So there's no relationship at all, you just want every time within five minutes of every record in the original table? (I suppose there's a reason but danged if I can see the usefulness.)

Select Main.Thisdatetime, Main.Diff from Contact1!Main ;
Where Main.Thisdatetime IN ;
  (Select Main.Thidatetime from Main Where ;
     thisdatetime BETWEEN thisdatetime - 300 AND thisdatetime + 300) ;
Into Cursor Whatever

Doesn't need (and can't use) a join because there is no relational condition to join. Unfortunately, the result won't mean anything either.

 

[JRB-Bldr]

I hope that you do realize that, since you are doing this in VFP, you don't have to do all of the calculations in the SQL Query.
You can acquire the 'raw' data with the SQL Query and then do the calculation and analysis after that.

The VFP SEC(tExpression) command will give you the Seconds of a DateTime value (tExpression).

Then with 2 differing values of Seconds you can easily determine the difference.

Good Luck,
JRB-Bldr

 

[mccartmd]

========================================================
Main.Parse_pk Field type int autoinc
Main.thisdatetime Field type width 8
=========================================================
Select Main.Parse_PK, Main.Thisdatetime, as Main_A_Record
Main.Parse_PK, Main.Thisdatetime, as Main_B_Record
From Contact1!Main
innerjoin Contact1!Main_A_Record
on Main_B_Record.ThisDateTime
Between Main_A_Record.ThisdateTime-300)
MainThisDateTime+300) into Cursor CursorVtime
=========================================================

 

[mccartmd]

Previous item . . . trying to see if I am doing inner joins right for inner joining datetimes of the same table within a 5 minute window? Appreciate any feedback.
THX
Mike