This discussion is archived
6 Replies Latest reply: Jul 24, 2013 11:28 AM by Greg.Spall RSS

how to write query

user12187801 Expert
Currently Being Moderated

Hi,

I need expert advice.

on daily basis we are updating meter reading ( meter reading is nothing but "meter number / unit / date".

 

Let say there are 10 apartment  and each apartment has separate meter.

 

i need to write query get missing meter reading on a specific date.

 

Thanks

  • 1. Re: how to write query
    SomeoneElse Guru
    Currently Being Moderated

    Please post sample table and data using CREATE TABLE and INSERT statements, or at least a working WITH clause.

     

    Also show what you want the result to be based on your sample data.


  • 2. Re: how to write query
    Greg.Spall Expert
    Currently Being Moderated

    Not enough information.

     

    Provide a small sample so we can see your tables and structure.  Without it, we have no clue where to begin.

     

    Have you read the FAQ on how to post a question?

    OTN - FAQ

  • 3. Re: how to write query
    user12187801 Expert
    Currently Being Moderated

    Unit Master

    401

    402

    403

    404

     

    Daily Meter Reading

    date               unit     meter

    01-01-01          1     401

    01-01-01          1     402

    01-01-01          1     404  ( on 1st of jan user update the meter reading but if you notice unit number 403 is missing)

     

    02-01-01          1     401

    02-01-01                402 ( on 2nd of jan user update the meter reading but if you notice this time 402 is meter unit is missing)

    02-01-01          1     403

    02-01-01          1     404

     

    03-01-01                  401

    03-01-01          1     402

    03-01-01          1     403 ( on 3rd of jan user update the meter reading but if you notice this time meter unit is missing for 401 and 404 is unit is missing)

     

    We want the following output

    if user run report on 1st of jan system should return value of 403 unit number

    if user run report on 2nd of jan then system should return value of 402 unit number

    if user run report on 3rd of jan then system should return value of 401/404 unit number

     

    Hope this help to get my expected answer.

  • 4. Re: how to write query
    user12187801 Expert
    Currently Being Moderated

    Table Structure

    Master Table Unit

     

    create table unit ( unit varchar2(100))

     

    Meter Reading

     

    create table meter ( meter number, reading number, unit varchar2(100))

  • 5. Re: how to write query
    SomeoneElse Guru
    Currently Being Moderated

    It really helps when you give us a script we can quickly copy/paste into a SQL Plus session.


  • 6. Re: how to write query
    Greg.Spall Expert
    Currently Being Moderated

    As SomeoneElse points out, without proper scripts we can copy/paste into ours, it's not easy for us to test.

     

    So here an untested script .. that hopefully gives you on idea to get your results

     

    select unit_number
      from unit_master m
     where not exists ( select 1 from daily_meter_reading d
                          where d.meter = m.unit_number );

     

    Just need to work in the date logic properly

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points