Comparing two tables with SQL

Question

I have two tables; one with records recorded by the operators, and the other with data stored on the machines on which the operators work, to exit the system. Data from operators is one record low (sometimes more) per hour that runs on machines. Data stored for downtime is a constant stream of data logging when machines are down or when they are running.

What I want to do is take the data from the operators and compare them with the downtime system to make sure that the operators take their weights every hour every hour when the machines are running. If the machine does not work for an hour, the operator will not record. If the machine is active for any period of time within an hour, then there should be a record for this time interval. To do this, I will need to simplify the data to one record per hour for each table and compare that there is a record for each hour on both.

My knowledge of SQL is not so wide, so I don’t even know if this is possible. I tried counting the elements per hour and grouping the data, but could not successfully get them to work.

The tables are in the Microsoft SQLExpress 2008 database. The tables themselves are lengthy.

OPERATOR Table: Date Time Product_No Seq DateCode Internal_DateCode & ProductNo Product_Description Row Size Weight1 Weight2 Weight3 Weight4 Weight4 XR LSL_WT TAR_WT USL_WT LABEL MAV

8/3/11 0:37:54 1234567 23 DateCode Internal_DateCode and ProductNo Product Description L-1A 50 1575 1566 1569.5 1575.5 1573.4 1550.809 1574.623 1598.437 1564.623 1525.507 L- 1A_50

DOWNTIME table:

Line_Name Machine_Name t_stamp Shift Details Duration R2 Switch 3/3/11 22:17 53 R2 Filler 9/3/11 23:10 17

The machine of interest is “Filler,” the duration is the time in minutes that the machine has been idle.

Purpose: to find out what hours the operators took the weight and what hours they did not have. Find out what hours the cars fell and what hours they were active. Compare the time during which the operators did not take the weight with the time when the machines were active, and determined whether the machines were running, but there are no operator entries for the scales, when they should be.

Answer 1

Assumptions: 1) If the date and time are the actual date and time formats, this will be different. But, not knowing, I'm going to assume that they are strings (otherwise, why make them two columns?) Therefore, I am going to assume that they are YYYY-MM-DD and HH: MI respectively.

2) It seems reasonable that the OP wants to know these facts either SHIFT, or PRODUCT, or something. It would seem strange to just ask when they took the weight of anything. I guess this is from ProductNo.

3) This can be done in code, but my example is simpler if I create a table with a clock in it:

create table hours (hr varchar(2)); insert into hours ('01'); insert into hours ('02'); insert into hours ('03'); insert into hours ('04'); etc. 

4) Tasks are somehow based on time - that is, a report for a specific period of time. In my example, I will select the month of September.

Task 1: find out what hours the operators took weight and what hours they did not.

 select h.hr, o.productno, count(*) as nbr from hours h left outer join operator o on h.hr = substr (o.time, 1, 2) where o.date between '2011-09-01' and '2011-09-30' group by h.hr, o.productno 

This should show a list of hours, and if measurements have been taken, the number of times these measurements have been taken during this hour for this product. If no measurements were made, the product would be empty due to an external connection, which I assume is what the OP forgot about.

Task 2: find out what hours the cars fell and what hours they were active.

I do not want to continue if the OP does not give some clue regarding my assumptions. Be that as it may, there is a very good chance that I completely lose time.

Task 3 and 4: compare the time during which the operators did not take the weight with the times when the machines were active, and determine if the machines were running, but there are no operator entries for the scales, when they should be.

I do not want to continue if the OP does not give some clue regarding my assumptions. Be that as it may, there is a very good chance that I completely lose time.