Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Unanswered: SQL Server Join Question

    I have a table billing_history which consists of the following fields

    Code:
    CREATE TABLE [dbo].[BILLING_HISTORY2](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [READING_MONTH_YEAR] [date] NULL,
        [READING] [int] NULL,
        [CONSUMER_ID] [int] NULL,
        [payment_status] [bit] NOT NULL
        )
    SQL Fiddle

    The following queries return the MAX Paid And Max Unpaid Values for a Consumer

    Code:
    SELECT MAX(READING) AS 'MAXIMUM_PAID_READING',consumer_id from billing_history2
        where payment_status=0 GROUP BY consumer_id;
    Code:
    SELECT MAX(READING) AS 'MAXIMUM_UNPAID_READING',consumer_id from billing_history2
        where payment_status=1 GROUP BY consumer_id;
    However when i join them to subtract the MAXIMUM_PAID_READING from MAXIMUM_UNPAID_READING to get the current reading, by joining the above two queries, it results in returning all those records which have a matched consumer_id. So if a consumer hasn't paid any bill yet, the id would be omitted in the PAID_READING and hence an INNER JOIN doesn't return any result. IF i use FULL OUTER JOIN, it return all the records but sets the difference to NULL.

    I need to find out the current USAGE of the customer by subtracting it from the previous unpaid USAGE.

    How do i go about joining these two queries in such a way that the resulting difference is found irrespective whether the person has paid a bill in the past or not.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    (Not tested.)

    Code:
    SELECT consumer_id
         , MAX(CASE payment_status
               WHEN 0 THEN
                    READING
               END) AS MAXIMUM_PAID_READING
         , MAX(CASE payment_status
               WHEN 1 THEN
                    READING
               END) AS MAXIMUM_UNPAID_READING
     FROM  billing_history2
     GROUP BY
           consumer_id
    ;

Tags for this Thread

Posting Permissions

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