UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Query Desire, Any Versions    
 
   
ordnance1
post May 8 2019, 05:48 PM
Post#1



Posts: 629
Joined: 7-May 11



I have a weather database that I have created. I import my data from a flat file into a temp table "WeatherData_Temp" I then want to insert that data into "WeatherData". As you can see I am importing every second (which I will condense down later) but there are a number of rows where the data is identical. Is there any way to write a query that would not insert a row if it matched (with the exception of the timestamp) the preceding row?

Attached File  Untitled_picture.png ( 172.3K )Number of downloads: 10
Go to the top of the page
 
June7
post May 8 2019, 06:21 PM
Post#2



Posts: 648
Joined: 25-January 16



They aren't identical records because the timestamp is different.

I don't think a query alone can accomplish, at least not simply, because the data in 7 fields has to checked. For what period would you want to compare the 7 fields and discard 'dups' - every minute, hour, day?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MadPiet
post May 8 2019, 06:55 PM
Post#3



Posts: 3,163
Joined: 27-February 09



If you can return a "bin value" for a series of times, then you can use ROW_NUMBER() with an OVER BY clause to identify which sequential records could be considered duplicates, and then you could just filter those out.

Any chance you could post a CREATE TABLE and an INSERT script to populate the table with some sample data? And how much of a window of time would you consider to be in the same "bin" (well, since you're grouping times together).
This post has been edited by MadPiet: May 8 2019, 06:57 PM
Go to the top of the page
 
ordnance1
post May 9 2019, 11:05 AM
Post#4



Posts: 629
Joined: 7-May 11



Thanks for taking the time to reply.

Having given this some more thought, and not having a desire to store a line of data for each second. I will probably format the timestamp to date, hours and minutes (dropping the seconds) and generate the average of each value for that minute.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 10:13 PM