1. Registered User
Join Date
Mar 2004
Posts
128

Hi all,
I had posted a problem,and I didn't get the obvious solution, can you help me please:

My table looks like the following:

Date\Time Counter1 Counter2

The counters are taken by an automatic system in three shifts:
First in : 6:00 AM Second in: 15:00 PM Third in : 11:00 PM

I want a query to calculate the difference of the counters between the three shifts (Let's begin with this).

For example: The following table
22/11/04 11:00 PM 0 0
23/11/04 6:00 AM 10 20
23/11/04 15:00 PM 20 30
23/11/04 23:00 PM 30 40

The output is:
23/11/04 6:00 AM 10 20
23/11/04 15:00 PM 10 10
23/11/04 11:00 PM 10 10

I'm new I the SQL world. can you please help me and give me sum information to solve the problem.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

3. Registered User
Join Date
Mar 2004
Posts
128

SQL query

But, the answer isn't sufficient to my problem.
thanks...

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
then you will have to ask your question a bit more clearly

maybe we do not understand the problem

5. Registered User
Join Date
Mar 2004
Posts
128

SQL query

I'll explain secondly,
my really table is the following:
1. CollectDate (Date/Time): The date and time of collecting data.
2. MachineNum (Number): The machine number of the data. I have 40...50 machines.
3. WorkOrder (Number): A number witch discribe the part of the machine.
4. TotalGood (Number): An accumulator of the current parts that the machine did.

The total task of the query is to calculate how many parts did the machine do in a certain date but by shifts.
E.g:
the final date collection was:
CollectDate MachineNum WorkOrder TotalGood
25/11/04 23:00:00 40 00111 100
26/11/04 6:00:00 40 00111 200
26/11/04 15:00:00 40 00111 500
26/11/04 23:00:00 40 00111 1000

The query output is:
Shifts(26/11/04) MachineNum WorkOrder TotalGood
6:00:00 40 0011 100 (200-100)
15:00:00 40 0011 300 (500-200)
23:00:00 40 0011 500 (1000-500)