CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bypivot
MOHAMMED SOUFIANE
MOHAMMED SOUFIANE

Reputation: 27

How to get data of a previous year for a specifed date

I have the query below, to display the Sales by Rayon & Store, for a specific date :

SELECT
    [DATE] = GL_DATEPIECE
    [STORE] = GL_ETABLISSEMENT,
    [RAYON] = GL_FAMILLENIV1,
    [SALES] = SUM([GL_TOTALTTC])
FROM GCLIGNEARTDIM
WHERE GL_DATEPIECE BETWEEN '2019-11-01' AND '2019-11-02'
GROUP BY GL_ETABLISSEMENT, GL_FAMILLENIV1, GL_DATEPIECE

I want to add another column : [Sales Y-] to bring Sales for previous year of the date specifed in the clause where.

Upvotes: 0

Views: 48

Answers (1)

GMB
GMB

Reputation: 222382

One option is to do conditional aggregation:

SELECT
    [DATE] = GL_DATEPIECE
    [STORE] = GL_ETABLISSEMENT,
    [RAYON] = GL_FAMILLENIV1,
    [SALES] = SUM(CASE WHEN YEAR(GL_DATEPIECE) = 2019 THEN [GL_TOTALTTC] ELSE 0 END)
    [SALES Y-] = SUM(CASE WHEN YEAR(GL_DATEPIECE) = 2018 THEN [GL_TOTALTTC] ELSE 0 END)
FROM GCLIGNEARTDIM
WHERE 
    GL_DATEPIECE BETWEEN '2019-11-01' AND '2019-11-02'
    OR GL_DATEPIECE BETWEEN '2018-11-01' AND '2018-11-02'
GROUP BY GL_ETABLISSEMENT, GL_FAMILLENIV1, GL_DATEPIECE

Upvotes: 1

Related Questions