# Thread: calculation query

1. Registered User
Join Date
Mar 2004
Posts
128

## Unanswered: calculation query

Hi all,
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.
Best regards...

2. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
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
Last edited by r937; 11-19-04 at 07:01.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•