Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    128

    Question 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. #2
    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 08:01.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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