Updating a table with data gathered from another table

[qaladien]

I have 2 tables (FLOOR and INVOICES) (There are floor tables for each location but making it as simple as i can)

 

Floor Data Structure

NumDateBeg

Mon1030

Mon1130

Mon1230

Mon130

.....

(Monday to Sunday from 1030 to 630 represented)

 

Invoices

Numdate

Time

(Invoice detail lines such as tax etc which i will tackle later)

 

What i am trying to do it to read through the database and update my FLOOR database with transaction that meet each day/hour grouping similar to

SELECT * FROM invoices WHERE date = (date of current record or date +1 if tuesday +2 for weds etc) AND time < 4140000 AND time > 3780000

 

 

i am a bit confused on how to do this best.

 

 

I know i need to query for each set of criterion so some loop similar to

 

for (x=0,x<num of rows in floor,x++) {
query how many records meet criterion of: 
     date = date[x]
     hour > 3780000
     hour < 4140000 (Assumes 10:30 - 11:30 ... can increment times also in a sub loop with each hour being 360000 value)

floor.Mon1030 = num_rows(Query i just did)
}

 

i know i am close logic wise but before i run through 250,000 invoices between 5 locations i wanted to see if a) i am close and b) what am i missing

 

 

TY for any help, no rush

 

 

Qaladien

 

 

 

 

 

[qaladien]

Bump : )

 

[BlueSkyIS]

why not just use one UPDATE query for each of the criteria you're using, like:

 

UPDATE invoices SET some_field = '$some_value' WHERE date = (date of current record or date +1 if tuesday +2 for weds etc) AND time < 4140000 AND time > 3780000

[qaladien]

because i am trying to read through whole INVOICES table to get quantities of transactions in a given hor for floor traffic reports to even out staffing ... i know i could do it a long winded way and just do 59 queries in a loop for each week but just seeing if there is amore efficient way to do it w/o doing 59 queries 208 times for 5 diferent stores (4 years of data) to seed the database