[Solved] SQL Server – not displaying NULL values

by


Move the CA_AN subquery to a Cross Apply and check the is not null on the where clause

SELECT 
    REGION = et_region, 
    [STORE CODE] = e.et_etablissement,
    [STORE NAME] = et_libelle,
    CA = CONVERT(DECIMAL(15,2),SUM(gl_totalttcdev)),
    NBRTICKETS = CONVERT(DECIMAL(15,0),COUNT(distinct(GL_NUMERO))),
    NBRARTICLES =  CONVERT(DECIMAL(15,0),SUM(GL_QTEFACT)),
    UPT =  CONVERT(DECIMAL(15,2),SUM(GL_QTEFACT)/COUNT(DISTINCT(GL_NUMERO))) ,
    PM =  CONVERT(DECIMAL(15,2),SUM(gl_totalttcdev)/COUNT(DISTINCT(GL_NUMERO))),
    CA_AN = MAX(CA_AN.caan)

FROM piece 
LEFT JOIN ligne ON 
    gl_souche = gp_souche 
    AND gl_naturepieceg = gp_naturepieceg 
    AND gl_numero = gp_numero 
    AND gl_indiceg = gp_indiceg 
LEFT JOIN etabliss AS e ON
    gp_etablissement = e.et_etablissement 
LEFT JOIN ARTICLE ON 
    GL_CODEARTICLE = ARTICLE.GA_CODEARTICLE
CROSS APPLY (
    SELECT 
        caan = CONVERT(DECIMAL(15,2),sum(gl_totalttcdev)) 
    FROM piece 
    LEFT JOIN ligne ON 
        gl_souche = gp_souche 
        AND gl_naturepieceg = gp_naturepieceg 
        AND gl_numero = gp_numero 
        AND gl_indiceg = gp_indiceg 
    LEFT JOIN etabliss AS e1 ON 
        e1.et_etablissement = gp_etablissement
    left join ARTICLE on 
        GL_CODEARTICLE = ARTICLE.GA_CODEARTICLE
    where 
        gp_naturepieceg = 'FFO'
        AND gl_typearticle <> ''
        AND gl_typearticle <> 'FI' 
        AND e1.et_etablissement = e.et_etablissement
        AND CAST(GP_DATEPIECE AS DATE) BETWEEN  DATEADD(YEAR,-1,CAST(@DATED AS DATE)) AND DATEADD(YEAR,-1,CAST(@DATEF AS DATE))
    GROUP BY 
        et_etablissement, 
        et_libelle

) CA_AN
WHERE 
    gp_naturepieceg = 'FFO' 
    AND (e.et_etablissement NOT IN ('F20','F44','R05','F40','F47'))
    AND gl_typearticle <> ''
    AND gl_typearticle <> 'FI' 
    AND et_LIBELLE LIKE @MAG
    AND CAST(GP_DATEPIECE AS DATE) BETWEEN  CAST(@DATED AS DATE) AND CAST(@DATEF AS DATE) 
    AND CA_AN.caan IS NOT NULL
GROUP BY 
    et_region,
    et_etablissement, 
    et_libelle
HAVING SUM(gl_totalttcdev) > 0 
ORDER BY 
    et_region,
    CA_AN DESC,
    et_etablissement

0

solved SQL Server – not displaying NULL values