SQL query join on time/Timevalue not working. Any tips?

[blodzoom]

I've created a Minutes table that lists every minute in the day (From 12:00:00 AM to 11:59:00 PM) and I have a Price table with Price and Ptime columns (always 00 in the 'seconds' column).

I'm trying to outter join these two tables so that minutes with no corresponding price show up in the query results as a record with a time but a blank price (for graphing purposes)

The results on the join are sporadic and seemingly random. I have data spread across the whole day but the only ones matching are between 12:00:00 and 1:00:00 and even then, some are not coming through even though the one right next to it in the same format comes through fine.

I've never joined on a time field before and I don't know if there's a trick to it.

 

[Norie]

Couldn't you just find all the records without price using a simple query on the price table?

 

[blodzoom]

Couldn't you just find all the records without price using a simple query on the price table?

If there is no price, there is no record. Basically I'm trying to create a record for minutes that are missing.

If there is no record for 12:34, I want to create one in the query that has the time but no price. I don't want to add that 'blank' record to the actual data though because I don't want to create records and then have somebody later try to paste 10000 records into the table and end up with some of them rejecting or having duplicates for a certain time.

So given that, if you can tell me how I can write a query to identify minutes that are missing, I would love you forever (or at least be grateful)

 

[blodzoom]

I'm not sure why times that had no seconds were different, but if I round them all to 6 digits, they match up. Must have something to do with the way that Bloomberg spit them out.

I am still interested in other ways to do this though, since I realize that what I'm doing is a little strange. I would prefer to pull the range into Excel and alter the data from there but I'm afraid that the data sets could be quite large and that might go quite a bit slower than this.

 

[xenou]

Hi,
What you have should work but dates with times are tricky because they are decimals values and can create mismatches even when values are extremely close but different in the 8th or 9th decimal place. You may like to use text values for your dates (in ISO format is convenient since it can be ordered). If you want numeric dates and don't need to save the precise values, use an update query to update the datetime values. But in that case I would prefer to use a date function rather than simple rounding - you want the all values to be *exactly* the same -- minutes with no seconds.

The query you want is probably simple enough - this seems to work:

Query1 (helper query):

SELECT DISTINCT Format([PTime],"yyyy-mm-dd hh:nn") AS Exp1
FROM Table1;

Query2 (results of times without dates):

SELECT Table2.DT
FROM Table2 LEFT JOIN Query1 
	ON Table2.DT = Query1.Exp1
WHERE 
	Query1.Exp1 Is Null;

Here is a sample DB (using only values from a single day to simplify this):
<a href="http://northernocean.net/etc/mrexcel/20120717_db5.zip">Sample Database</a>
sha256sum: 0053e039860b522bbc2d6342195afa00880ca9d75e67b12eee753bc7824169ac


Another approach (not necessarily any better but for kicks) is to create your full set of times from scratch. This can be done with a cartesian join on two tables, one with the hours values from 0 to 23, and one with the minutes values from 0 to 59. A VBA script would be yet another option as well.
<a href="http://northernocean.net/etc/mrexcel/20120717_db4.zip">Sample Database (Cartesian Join)</a>
sha256sum: 3e6c0de6f17315deb75a89e760d62389a00051a0966183de9021e689f4d7c8fc