Help needed with SQL Query...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Help needed with SQL Query...

  1. #1
    Michael Cole Guest

    Help needed with SQL Query...

    I have been trying to do this for ages, and still can't get it complete.
    Any assistance gratefully received.

    Situation is: -

    (Water Billing)
    We have many Accounts.
    Each Account may have one or more Services (Meters).
    Each Service may have one of more Readings.
    Readings have a Date, a Usage and can also be marked as an Exception (such
    as a broken meter, which would require a manual process to check.)
    Each Service may of may not have been read in a particular Batch. Note that
    we have no way of knowing which Reading came from which particular Batch,
    merely that one of those Readings for that Service came from a particular
    Batch. We can however make an assumption that the most recent Reading is
    the one we want.

    Complications: -
    1. A particular Service (meter) may either be Primary or Effluent. For
    those who care, Effluent meters are used where a pipe goes in-line from one
    premises to the next, so that the second property is billed directly off the
    second meter (its primary), but the first property is billed off its Primary
    (what water enters the property) minus the Effluent (what water leaves the
    property to go to the second). Now a Account /must/ have one or more
    Primary Services, and /may/ also have one of more Effluent Services. OK?

    Now, the problem is that we need to know which Accounts need to be billed
    post the import of a particular batch of Readings. We can bill under the
    following circumstances: -
    1. At least one of the Primary meters and /all/ of the Effluent meters have
    been read _in that particular batch_.
    2. None of those meters that have been read _in that particular batch_,
    have their most recent Readings either being of zero Usage, or marked as an
    Exception.

    I would appreciate any thoughts.

    --
    Regards,

    Michael Cole



  2. #2
    Daniel Reber Guest

    Re: Help needed with SQL Query...

    As long as the data is there then you should be able to get it. Could you
    post the table definitions and their relationships.

    Thanks
    Daniel Reber
    Datamasters, Inc

    "Michael Cole" <michael.cole@hansen.com> wrote in message
    news:3c68dc45@10.1.10.29...
    > I have been trying to do this for ages, and still can't get it complete.
    > Any assistance gratefully received.
    >
    > Situation is: -
    >
    > (Water Billing)
    > We have many Accounts.
    > Each Account may have one or more Services (Meters).
    > Each Service may have one of more Readings.
    > Readings have a Date, a Usage and can also be marked as an Exception (such
    > as a broken meter, which would require a manual process to check.)
    > Each Service may of may not have been read in a particular Batch. Note

    that
    > we have no way of knowing which Reading came from which particular Batch,
    > merely that one of those Readings for that Service came from a particular
    > Batch. We can however make an assumption that the most recent Reading is
    > the one we want.
    >
    > Complications: -
    > 1. A particular Service (meter) may either be Primary or Effluent. For
    > those who care, Effluent meters are used where a pipe goes in-line from

    one
    > premises to the next, so that the second property is billed directly off

    the
    > second meter (its primary), but the first property is billed off its

    Primary
    > (what water enters the property) minus the Effluent (what water leaves the
    > property to go to the second). Now a Account /must/ have one or more
    > Primary Services, and /may/ also have one of more Effluent Services. OK?
    >
    > Now, the problem is that we need to know which Accounts need to be billed
    > post the import of a particular batch of Readings. We can bill under the
    > following circumstances: -
    > 1. At least one of the Primary meters and /all/ of the Effluent meters

    have
    > been read _in that particular batch_.
    > 2. None of those meters that have been read _in that particular batch_,
    > have their most recent Readings either being of zero Usage, or marked as

    an
    > Exception.
    >
    > I would appreciate any thoughts.
    >
    > --
    > Regards,
    >
    > Michael Cole
    >
    >




  3. #3
    Michael Cole Guest

    Re: Help needed with SQL Query...


    "Daniel Reber" <dreber@dminconline.com> wrote in message
    news:3c691349$1@10.1.10.29...
    > As long as the data is there then you should be able to get it. Could you
    > post the table definitions and their relationships.


    Pretty much what I wrote: -
    Showing only what is relevent-

    Account
    AccountKey
    Billable (Flag)

    Service
    ServiceKey
    AccountKey (1:M to Account)
    Effluent (Flag)

    Reading
    ReadingKey
    ServiceKey (1:M to Service)
    Date
    Usage
    Exception (Flag)

    Batch
    BatchNo
    ServiceKey (1:M to Service)


    >
    > "Michael Cole" <michael.cole@hansen.com> wrote in message
    > news:3c68dc45@10.1.10.29...
    > > I have been trying to do this for ages, and still can't get it complete.
    > > Any assistance gratefully received.
    > >
    > > Situation is: -
    > >
    > > (Water Billing)
    > > We have many Accounts.
    > > Each Account may have one or more Services (Meters).
    > > Each Service may have one of more Readings.
    > > Readings have a Date, a Usage and can also be marked as an Exception

    (such
    > > as a broken meter, which would require a manual process to check.)
    > > Each Service may of may not have been read in a particular Batch. Note

    > that
    > > we have no way of knowing which Reading came from which particular

    Batch,
    > > merely that one of those Readings for that Service came from a

    particular
    > > Batch. We can however make an assumption that the most recent Reading

    is
    > > the one we want.
    > >
    > > Complications: -
    > > 1. A particular Service (meter) may either be Primary or Effluent. For
    > > those who care, Effluent meters are used where a pipe goes in-line from

    > one
    > > premises to the next, so that the second property is billed directly off

    > the
    > > second meter (its primary), but the first property is billed off its

    > Primary
    > > (what water enters the property) minus the Effluent (what water leaves

    the
    > > property to go to the second). Now a Account /must/ have one or more
    > > Primary Services, and /may/ also have one of more Effluent Services.

    OK?
    > >
    > > Now, the problem is that we need to know which Accounts need to be

    billed
    > > post the import of a particular batch of Readings. We can bill under

    the
    > > following circumstances: -
    > > 1. At least one of the Primary meters and /all/ of the Effluent meters

    > have
    > > been read _in that particular batch_.
    > > 2. None of those meters that have been read _in that particular batch_,
    > > have their most recent Readings either being of zero Usage, or marked as

    > an
    > > Exception.
    > >
    > > I would appreciate any thoughts.
    > >
    > > --
    > > Regards,
    > >
    > > Michael Cole
    > >
    > >

    >
    >




  4. #4
    Daniel Reber Guest

    Re: Help needed with SQL Query...

    Without the data it is difficult to test but this is a starting point.
    Since there isn't a relational way to seperate your primary meter from your
    Effluent meter I reccomend a union. If you are able to change the model
    you might want to put a self referencing key in the service table. That way
    a union will not be needed.

    Try this:

    select
    MeterType 'Primary'
    a.AccountKey,
    s.ServiceKey,
    ReadingKey,
    r.[Date],
    r.Usage
    from Account a
    join Service s on s.AccountKey = a.AccountKey
    join Batch b on b.ServiceKey = s.ServiceKey
    join Reading r on r.ServiceKey = s.ServiceKey
    where Exception = 0
    and Usage > 0
    and b.BatchNo = <fill in value here>

    union all

    select
    MeterType 'Effluent'
    a.AccountKey,
    s.ServiceKey,
    ReadingKey,
    r.[Date],
    r.Usage
    from Account a
    join Service s on s.AccountKey = a.AccountKey
    join Batch b on b.ServiceKey = s.ServiceKey
    join Reading r on r.ServiceKey = s.ServiceKey
    where Effluent = 1
    and Exception = 0
    and Usage > 0
    and b.BatchNo = <fill in value here>

    order by a.AccountKey, MeterType

    HTH
    Daniel Reber
    Datamasters, Inc

    "Michael Cole" <michael.cole@hansen.com> wrote in message
    news:3c69ab2b@10.1.10.29...
    >
    > "Daniel Reber" <dreber@dminconline.com> wrote in message
    > news:3c691349$1@10.1.10.29...
    > > As long as the data is there then you should be able to get it. Could

    you
    > > post the table definitions and their relationships.

    >
    > Pretty much what I wrote: -
    > Showing only what is relevent-
    >
    > Account
    > AccountKey
    > Billable (Flag)
    >
    > Service
    > ServiceKey
    > AccountKey (1:M to Account)
    > Effluent (Flag)
    >
    > Reading
    > ReadingKey
    > ServiceKey (1:M to Service)
    > Date
    > Usage
    > Exception (Flag)
    >
    > Batch
    > BatchNo
    > ServiceKey (1:M to Service)
    >
    >
    > >
    > > "Michael Cole" <michael.cole@hansen.com> wrote in message
    > > news:3c68dc45@10.1.10.29...
    > > > I have been trying to do this for ages, and still can't get it

    complete.
    > > > Any assistance gratefully received.
    > > >
    > > > Situation is: -
    > > >
    > > > (Water Billing)
    > > > We have many Accounts.
    > > > Each Account may have one or more Services (Meters).
    > > > Each Service may have one of more Readings.
    > > > Readings have a Date, a Usage and can also be marked as an Exception

    > (such
    > > > as a broken meter, which would require a manual process to check.)
    > > > Each Service may of may not have been read in a particular Batch.

    Note
    > > that
    > > > we have no way of knowing which Reading came from which particular

    > Batch,
    > > > merely that one of those Readings for that Service came from a

    > particular
    > > > Batch. We can however make an assumption that the most recent Reading

    > is
    > > > the one we want.
    > > >
    > > > Complications: -
    > > > 1. A particular Service (meter) may either be Primary or Effluent.

    For
    > > > those who care, Effluent meters are used where a pipe goes in-line

    from
    > > one
    > > > premises to the next, so that the second property is billed directly

    off
    > > the
    > > > second meter (its primary), but the first property is billed off its

    > > Primary
    > > > (what water enters the property) minus the Effluent (what water leaves

    > the
    > > > property to go to the second). Now a Account /must/ have one or more
    > > > Primary Services, and /may/ also have one of more Effluent Services.

    > OK?
    > > >
    > > > Now, the problem is that we need to know which Accounts need to be

    > billed
    > > > post the import of a particular batch of Readings. We can bill under

    > the
    > > > following circumstances: -
    > > > 1. At least one of the Primary meters and /all/ of the Effluent

    meters
    > > have
    > > > been read _in that particular batch_.
    > > > 2. None of those meters that have been read _in that particular

    batch_,
    > > > have their most recent Readings either being of zero Usage, or marked

    as
    > > an
    > > > Exception.
    > > >
    > > > I would appreciate any thoughts.
    > > >
    > > > --
    > > > Regards,
    > > >
    > > > Michael Cole
    > > >
    > > >

    > >
    > >

    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center