CODEWITHSUNDEEP
sqlms-access
Vezzerina
Vezzerina

Reputation: 21

How do I combine 40000 update queries into one?

I need to create a new column of data, assigning every 15 minutes in my data a new number. There are about 40,000 15-minute time bins to be assigned... Here is what I have so far.

UPDATE [Groupers final no summer] 
SET [Groupers final no summer].[Fifteen min time bin daily] ='1',
WHEN [Groupers final no summer].[DateTimeEDT]=Between #11/02/05 08:45:01# 
     and #11/02/05 09:00:00#) 
OR 
SET [Groupers final no summer].[Fifteen min time bin daily] ='2', 
WHEN [Groupers final no summer].[DateTimeEDT]=Between #11/02/05 09:00:01# 
    and #11/02/05 09:15:00#);

And the time bins would go up to 40,000.

Any ideas?

Upvotes: 1

Views: 253

Answers (3)

Vezzerina
Vezzerina

Reputation: 21

OK, here is how I finally did it!

I converted the Date/Time to Julian calendar using CVDate() and made that a new column. I then converted using [Julian date/time]/(15/(60*24)) to calculate the number of 15 minute time bins I had in another column, and then called my first date/time 0 and counted fifteen minute time bins in integers by doing

Int([15 minute time intervals]-3711251)

where 3711251 is the first 15-min integer before my actual start time.

I hope this makes sense...

I am thrilled to have solved this and didn't want to leave this thread without saying thanks.

Upvotes: 1

mdma
mdma

Reputation: 57707

This query computes the time bin based on the 15 min time interval. It uses TIMESTAMPDIFF to return the number of seconds between the two timestamps. If you are not using mysql, your rdbms most likely has something equivalent, for example, SQL Server has DATEDIFF.

UPDATE [Groupers final no summer] 
    SET [Groupers final no summer].[Fifteen min time bin daily] =((TIMESTAMPDIFF(SECOND, #11/02/05 08:45:01#, DateTimeEDT)/(15*60))+1

Before running the update query, you might try this as a select query, to verify the results are correct:

SELECT * FROM [Groupers final no summer], 
((TIMESTAMPDIFF(SECOND, #11/02/05 08:45:01#, DateTimeEDT)/(15*60))+1 AS TimeBin

Upvotes: 0

Aaron Digulla
Aaron Digulla

Reputation: 328556

Try to turn the value in [Groupers final no summer].[DateTimeEDT] into seconds since the start time, then you can:

UPDATE [Groupers final no summer]
SET [Groupers final no summer].[Fifteen min time bin daily] = timeInSeconds / (15*60)

Upvotes: 3

Related Questions