6 Replies Latest reply: Jul 24, 2013 1:28 PM by Greg Spall RSS

    how to write query

    user12187801

      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

          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

            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

              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

                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

                  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

                    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