CODEWITHSUNDEEP
sqlsql-serverdatabase
user2018756
user2018756

Reputation: 337

SQL Server Join AND QUERY Issue

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.

Upvotes: 2

Views: 72

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

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

ex:

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

Upvotes: 0

TcKs
TcKs

Reputation: 26632

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;

Upvotes: 1

Related Questions