I need your help of building the following query.
I have the table "CountersTbl".
The table's fields are.
Date\Time Counter1 Counter2
The tables holds counters in different dates ant time.e.g:
19/11/04 06:00 am 10 20
19/11/04 15:00 pm 15 25
19/11/04 11:00 pm 35 90
In the above table we have the counters in three shifts in one day. Each day I hae the same shifts readings.
My task is:
I want to build a query that calculate the difference between the shifts counters in a givven day.
So, the output of the query of the 19/11/04 day is:
From 06:00 am - 15:00 pm 5 5
From 15:00 pm - 11:00 pm 20 65
I you please help me to build the code of this query.
select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
-six.Counter1 as Counter1Diff
-six.Counter2 as Counter2Diff
from CountersTbl as three
join CountersTbl as six
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
-three.Counter1 as Counter1Diff
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
join CountersTbl as three
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
here datetimecol is the name of your "Date\Time" column
also, please note, date and time represent whatever functions are available in your particular database system for extracting the date only and time only portions of the datetime values
i was going to write them using the standard sql EXTRACT function but the "standard sql" book which i own is pretty crappy and does not give enough decent examples for me to know how to extract dates and times
and anyway, most common database systems don't support EXTRACT, they have their own proprietary date functions