Stored procedure with date and date-1 parameter

Question

The query below allow me to get data from the year 2019 and 2018 :

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

I want to create a procedure to get data [SALES] for the entered date, and data for the previous year [SALES Y-]

EX : if i type in the procedure parameter : @StartDate : '2019-10-01' & @EndDate : '2019-11-01' then i should get the [SALES] data from this period.

And Data of the previous year for [Sales-1] : '2018-10-01' & '2018-11-01'

Larnu Larnu · Accepted Answer · 2019-11-03T13:02:16

Going by your statement "I want to create a procedure to get data [SALES] for the entered date, and data for the previous year [SALES Y-]" this says to me that if you provide the date 20191103 you want the sales data for 20191103 and 20180101 to 20181231 inclusive.

The logic seems odd, but seems like you just need this in your WHERE:

WHERE GL_DATEPIECE = @DateParam
   OR (GL_DATEPIECE >= DATEADD(YEAR, DATEDIFF(YEAR, 0, @DataParam)-1,0)
  AND  GL_DATEPIECE < DATEADD(YEAR, DATEDIFF(YEAR, 0, @DataParam),0));

Assuming that GL_DATEPIECE is a date and not a datetime.

Based on the new information:

WHERE (GL_DATEPIECE >= @StartDate AND GL_DATEPIECE <= @EndDate)
   OR (GL_DATEPIECE >= DATEADD(YEAR, -1, @StartDate) AND GL_DATEPIECE <= DATEADD(YEAR, -1, @EndDate))

Again, assumes that GL_DATEPIECE is a date, not a datetime.

Stored procedure with date and date-1 parameter

1 Answers