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

blodzoom

Board Regular
Joined
Aug 5, 2005
Messages
103
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Couldn't you just find all the records without price using a simple query on the price table?
 

blodzoom

Board Regular
Joined
Aug 5, 2005
Messages
103
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

Board Regular
Joined
Aug 5, 2005
Messages
103
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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):
Code:
SELECT DISTINCT Format([PTime],"yyyy-mm-dd hh:nn") AS Exp1
FROM Table1;

Query2 (results of times without dates):
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,114
Members
414,125
Latest member
iQQ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top