SQL Server Join AND QUERY Issue

Question

I have a table billing_history which consists of the following fields

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
)

http://www.sqlfiddle.com/#!3/892e0/5

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

SELECT MAX(READING) AS 'MAXIMUM_PAID_READING',consumer_id from billing_history2
where payment_status=0 GROUP BY consumer_id;

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.

TcKs · Accepted Answer · 2013-02-18 07:46:16Z

Don't join them.

SELECT
    MAX(CASE WHEN payment_status=0 THEN READING ELSE NULL END) AS 'MAXIMUM_PAID_READING',
    MAX(CASE WHEN payment_status=1 THEN READING ELSE NULL END) AS 'MAXIMUM_UNPAID_READING',

    MAX(CASE WHEN payment_status=0 THEN READING ELSE NULL END)
     - MAX(CASE WHEN payment_status=1 THEN READING ELSE NULL END)
        AS 'DIFF_READING',

    consumer_id from billing_history2
GROUP BY consumer_id;

If you want handle NULL values from MAX function, use ISNULL function:

SELECT
    ISNULL(MAX(CASE WHEN payment_status=0 THEN READING ELSE NULL END),0) AS 'MAXIMUM_PAID_READING',
    ISNULL(MAX(CASE WHEN payment_status=1 THEN READING ELSE NULL END),0) AS 'MAXIMUM_UNPAID_READING',

    ISNULL(MAX(CASE WHEN payment_status=0 THEN READING ELSE NULL END),0)
     - ISNULL(MAX(CASE WHEN payment_status=1 THEN READING ELSE NULL END),0)
        AS 'DIFF_READING',

    consumer_id from billing_history2
GROUP BY consumer_id;

Dumitrescu Bogdan · Accepted Answer · 2013-02-18 07:48:47Z

0

It's ok to use left outer join, but use isnull on your max value.

ex:

max(...) - isnull(max(...),0) as ...

answered Feb 18, 2013 at 7:48

Dumitrescu Bogdan

7,2652 gold badges26 silver badges32 bronze badges

Comments

Oleksandr Fedorenko · Accepted Answer · 2013-02-18 07:56:26Z

0

In your case is necessary to use CASE expression

SELECT CONSUMER_ID,
       MAX(CASE WHEN payment_status = 0 THEN READING ELSE 0 END) AS 'MAXIMUM_PAID_READING',
       MAX(CASE WHEN payment_status = 1 THEN READING ELSE 0 END) AS 'MAXIMUM_UNPAID_READING', 
       MAX(CASE WHEN payment_status = 0 THEN READING ELSE 0 END) - 
       MAX(CASE WHEN payment_status = 1 THEN READING ELSE 0 END) AS diff
FROM billing_history2
GROUP BY consumer_id

Demo on SQLFiddle

edited Feb 18, 2013 at 7:56

answered Feb 18, 2013 at 7:50

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Collectives™ on Stack Overflow

SQL Server Join AND QUERY Issue

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related