Post reply

How to get rid of 'NULL'

  • January 12, 2017 at 6:07 am

    #317491

    Hello everyone, I'm reaching to you because I have a question. Right now, I'm working for a small enterprise on their ERP. They asked me to update some of their page. I have to update / repaire a Pivot Table. The problem is that the last man who wrote this didn't comment it and in the double table, when you have  'NULL' values on the whole line, it makes it disapear ? Any idea on how to solve this ? 

    DECLARE @P_A_USER VARCHAR(10)
      SET @P_A_USER = RTRIM(CAST(:A_USER AS VARCHAR(10)))
    SELECT *
    FROM
    (SELECT datename(MONTH, D1) AS 'Mois',
        COALESCE(count(*), 0) AS 'Total_nouveaux_contrats',
        'Nouveaux contrats' AS 'Donnee',
        'a' AS 'NumLigne'
     FROM CONTRAT
     WHERE C22 = @P_A_USER
      AND year(D1) = year(getdate())
      AND C27 = 01
     GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_nouveaux_contrats)
                      FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt1
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, D1) AS 'Mois',
        COALESCE(count(*), 0) AS 'Total_contrats_renew_1an_perim',
        'Renew 1 an avec modification périmètre et FI 30%' AS 'Donnee',
        'b' AS 'NumLigne'
     FROM CONTRAT
     WHERE C22 = @P_A_USER
      AND year(D1) = year(getdate())
      AND C27 = 02
     GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_contrats_renew_1an_perim)
                      FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt2
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, D1) AS 'Mois',
        COALESCE(count(*), 0) AS 'Total_contrats_renew_1an',
        'Renew 1 an sans modification périmètre' AS 'Donnee',
        'c' AS 'NumLigne'
     FROM CONTRAT
     WHERE C22 = @P_A_USER
      AND year(D1) = year(getdate())
      AND C27 = 03
     GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_contrats_renew_1an)
                      FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt3
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, D1) AS 'Mois',
        COALESCE(count(*), 0) AS 'Total_contrats_renew_3ans',
        'Renew 3 ans sans modification périmètre' AS 'Donnee',
        'd' AS 'NumLigne'
     FROM CONTRAT
     WHERE C22 = @P_A_USER
      AND year(D1) = year(getdate())
      AND C27 = 04
     GROUP BY datename(MONTH, D1)) AS NvContrats Pivot(SUM(Total_contrats_renew_3ans)
                      FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt4
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DAT_ACT_PA) AS 'Mois',
        COALESCE(count(*), 0) AS 'RDV_realises',
        'RDV réalisés' AS 'Donnee',
        'e' AS 'NumLigne'
     FROM ACTION
     WHERE COD_COM = @P_A_USER
      AND CO_T_CO_PA = 'RDVCL'
      AND year(DAT_ACT_PA) = year(getdate())
     GROUP BY datename(MONTH, DAT_ACT_PA)) AS RDVrealise Pivot(SUM(RDV_realises)
                         FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt5
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DAT_ACT_PR) AS 'Mois',
        COALESCE(count(*), 0) AS 'RDV_planifies',
        'RDV planifiés' AS 'Donnee',
        'f' AS 'NumLigne'
     FROM ACTION
     WHERE COD_COM = @P_A_USER
      AND CO_T_CO_PA = 'RDVCL'
      AND year(DAT_ACT_PR) = year(getdate())
      AND DAT_ACT_PA IS NULL
     GROUP BY datename(MONTH, DAT_ACT_PR)) AS RDVplanifie Pivot(SUM(RDV_planifies)
                         FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt6
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DATE_COMMANDE) AS 'Mois',
        COALESCE(count(DISTINCT SOCIETE.NO_SOCIETE), 0) AS 'Nouveaux_comptes',
        'Nouveaux comptes ouverts' AS 'Donnee',
        'g' AS 'NumLigne'
     FROM SOCIETE
     LEFT JOIN V_COMMANDE ON V_COMMANDE.NO_SOCIETE = SOCIETE.NO_SOCIETE
     WHERE SOCIETE.COD_COM = @P_A_USER
      AND year(DATE_COMMANDE) = year(getdate())
      AND (SOCIETE.CA = 0
        AND SOCIETE.CAN_1 = 0)
     GROUP BY datename(MONTH, DATE_COMMANDE)) AS NvComptes Pivot(SUM(Nouveaux_comptes)
                         FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt7
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, CREER_LE) AS 'Mois',
        COALESCE(count(*), 0) AS 'Nouvelles_affaires',
        'Nouvelles affaires' AS 'Donnee',
        'h' AS 'NumLigne'
     FROM AFFAIRE
     WHERE AFFAIRE.COD_COM = @P_A_USER
      AND year(CREER_LE) = year(getdate())
     GROUP BY datename(MONTH, CREER_LE)) AS NvAffaires Pivot(SUM(Nouvelles_affaires)
                        FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt8
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
        COALESCE(count(*), 0) AS 'Nouvelles_affaires_exploitation',
        'Nouvelles affaires avec exploitation' AS 'Donnee',
        'i' AS 'NumLigne'
     FROM AFFAIRE
     WHERE AFFAIRE.COD_COM = @P_A_USER
      AND year(DATE_AFF) = year(getdate())
      AND N3 != 0
     GROUP BY datename(MONTH, DATE_AFF)) AS NvAffairesExp Pivot(SUM(Nouvelles_affaires_exploitation)
                         FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt9
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
        cast(COALESCE(SUM(N1), 0) AS integer) AS 'CA_nouvelles_affaires',
        'CA nouvelles affaires (€)' AS 'Donnee',
        'j' AS 'NumLigne'
     FROM AFFAIRE
     WHERE AFFAIRE.COD_COM = @P_A_USER
      AND year(CREER_LE) = year(getdate())
     GROUP BY datename(MONTH, DATE_AFF)) AS CaNvAffaires Pivot(SUM(CA_nouvelles_affaires)
                         FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt10
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
        COALESCE(count(*), 0) AS 'Nb_affaires_gagnees',
        'Affaires gagnées' AS 'Donnee',
        'k' AS 'NumLigne'
     FROM AFFAIRE
     WHERE AFFAIRE.COD_COM = @P_A_USER
      AND year(DATE_AFF) = year(getdate())
      AND COD_ETAT = 02
     GROUP BY datename(MONTH, DATE_AFF)) AS NbAffG Pivot(SUM(Nb_affaires_gagnees)
                       FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt11
    UNION
    SELECT *
    FROM
    (SELECT datename(MONTH, DATE_AFF) AS 'Mois',
        COALESCE(count(*), 0) AS 'Nb_affaires_perdues',
        'Affaires perdues' AS 'Donnee',
        'l' AS 'NumLigne'
     FROM AFFAIRE
     WHERE AFFAIRE.COD_COM = @P_A_USER
      AND year(DATE_AFF) = year(getdate())
      AND COD_ETAT = 03
     GROUP BY datename(MONTH, DATE_AFF)) AS NbAffP Pivot(SUM(Nb_affaires_perdues)
                       FOR Mois IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS pvt12
    ORDER BY NumLigne ASC

    For now, we have a 12x12 table but this is the only that the ERP gave us back.

    https://user.oc-static.com/upload/2017/01/11/14841458697809_Capture.PNG

  • January 12, 2017 at 7:16 am

    #1922257

    Replace all the

    SELECT *

    by

     SELECT
      Donnee,
      NumLigne,
      COALESCE(January, 0) AS January,
      COALESCE(February, 0) AS February,
      COALESCE(March, 0) AS March,
      COALESCE(April, 0) AS April,
      COALESCE(May, 0) AS May,
      COALESCE(June, 0) AS June,
      COALESCE(July, 0) AS July,
      COALESCE(August, 0) AS August,
      COALESCE(September, 0) AS September,
      COALESCE(October, 0) AS October,
      COALESCE(November, 0) AS November,
      COALESCE(December, 0) AS December

    It will replace your NULL into 0

  • January 12, 2017 at 7:18 am

    #1922259

    Okay, thanks ! It works realy well but only with the line with Data in it. Let me explain my self : I have 12 line but only one is filled up with date other than 'NULL' and it's the only line that appear even if we have some 0 in it. Any idea on how to solve this problem ?

  • January 12, 2017 at 7:23 am

    #1922261

    L

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply