Problème de jointure et de recherche dans le serveur SQL

Question

J'ai une table billing_history qui se compose des champs suivants

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

Les requêtes suivantes renvoient les valeurs MAX payées et MAX non payées pour un consommateur.

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;

Cependant, lorsque je les joins pour soustraire le MAXIMUM_PAID_READING du MAXIMUM_UNPAID_READING pour obtenir le relevé actuel, en joignant les deux requêtes ci-dessus, il en résulte le retour de tous les enregistrements qui ont un consumer_id correspondant. Donc, si un consommateur n'a pas encore payé de facture, l'ID sera omis dans PAID_READING et donc un INNER JOIN ne renvoie aucun résultat. Si j'utilise FULL OUTER JOIN, il renvoie tous les enregistrements mais met la différence à NULL.

J'ai besoin de connaître l'USAGE actuel du client en le soustrayant à l'USAGE précédent non payé.

Comment puis-je joindre ces deux requêtes de telle sorte que la différence résultante soit trouvée indépendamment du fait que la personne ait payé une facture dans le passé ou non.

Answer 1

Ne les rejoignez pas.

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;

Si vous voulez gérer les valeurs NULL à partir de la fonction MAX, utilisez la fonction ISNULL :

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;

Answer 2

Vous pouvez utiliser la jointure externe gauche, mais utilisez isnull sur votre valeur maximale.

ex :

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

Answer 3

Dans votre cas, il est nécessaire d'utiliser 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

Démonstration sur SQLFiddle