Code:
select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
, three.Counter1
-six.Counter1 as Counter1Diff
, three.Counter2
-six.Counter2 as Counter2Diff
from CountersTbl as three
inner
join CountersTbl as six
on date(three.datetimecol)
= date(six.datetimecol)
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
union all
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
, eleven.Counter1
-three.Counter1 as Counter1Diff
, eleven.Counter2
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
inner
join CountersTbl as three
on date(eleven.datetimecol)
= date(three.datetimecol)
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
order
by ShiftDate
, Shift
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
dBforums
>
calculation query
11-19-04, 02:39
calculation query
Linear Mode
