Compare Two Tables with SQL [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I have two tables; one with records being recorded by operators, the other with data stored by the machines the operators are running to log down time of the machine. The data coming from the operators is one record minimum (sometimes more) per hour that the machines are operating. The data stored for downtime is a constant stream of data logging when the machines are down or when they are running.

What I want to do is take the data from the operators and compare it with the down time system to make sure that the operators are taking their weights every hour, once an hour, that the machines are operational. If the machine is down for an hour then there would be no operator record. If the machine is active during any amount of time during the hour interv开发者_如何学Goal, then there should be a record for that hour 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 every hour on both.

My SQL knowledge isn't that broad so I don't even know if this is possible. I have tried counting items per hour and grouping data, but have not been able to successfully get them to work.

The tables reside on a Microsoft SQLExpress 2008 database. The tables themselves are lengthy.

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

8/3/11 0:37:54 1234567 23 DateCode Internal_DateCode&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 Changeover 9/3/11 22:17 53 R2 Filler 9/3/11 23:10 17

The machine of interest is the “Filler”, the duration is the amount of time in minutes that the machine is down.

The goal: Find out which hours the operators took weights for and which hours they did not. Find out what hours the machines were down and which hours they were active. Compare the times that the operators did not take weights with the times that the machines were active and determine if the machines were running but there are no operator records for weights when there should be.

---

ASSUMPTIONS: 1) If the Date and Time are actual date and time formats, this would be different. But without knowing, I am going to guess that they are strings (otherwise, why make them two columns?) So I'm going to guess that they are YYYY-MM-DD and HH:MI respectively.

2) It seems reasonable that the OP would want to know these facts either by SHIFT or by PRODUCT or by something. It would seem odd to just ask when they had taken weights of anything at all. I will guess that it is by ProductNo.

3) This could be done in code, but my example is easier if I create a table with the hours 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) The tasks are somehow timebased -- that is, the report is for some specific time period. In my example, I'll choose the month of September.

Task 1: Find out which hours the operators took weights for and which 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 the list of hours, and if measurements were taken, the number of times those measurements were taken in that hour for that productno. If no measurements were taken, the productno would be blank because of the outer join, which I am guessing is what the OP forgot about.

Task 2: Find out what hours the machines were down and which hours they were active.

I don't want to continue unless OP gives some clue as to my assumptions. As it is, there is a very good chance I am completely wasting my time.

Task 3 & 4: Compare the times that the operators did not take weights with the times that the machines were active and determine if the machines were running but there are no operator records for weights when there should be.

I don't want to continue unless OP gives some clue as to my assumptions. As it is, there is a very good chance I am completely wasting my time.