How to get the last 7 days data from sql

Question

I'm using this query that works fine and retrieves the values that I want, the only problem is that if there's no value for a given day it doesn't show the value as 0.

The data that I need is the sum of sales for the last 7 days, and if there's no sales it should return 0.

SELECT 
    DATEADD(DAY, 0, DATEDIFF(day, 0, vendas.data)) AS data, 
    COUNT(aff_sub.N_Enc) AS tot_enc, 
    SUM(aff_sub.Valor) AS tot_vendas 
FROM 
    aff_sub 
INNER JOIN 
    vendas ON aff_sub.N_Enc = vendas.ID 
WHERE 
    (aff_sub.ID = 1538)  
    AND (vendas.data >= DATEADD(day, - 7, GETDATE())) 
GROUP BY 
    DATEADD(DAY, 0, DATEDIFF(day, 0, vendas.data))

Can anyone help me out? Thanks

Answer 1

You try this below logic with the use of CTE to generate last 7 days always considering the getdate()

WITH dates as 
(
  select cast(getdate() as date) as dte, 1 as cnt
  union all
  select dateadd(day, -1, dte), cnt + 1
  from dates
  where cnt < 7
)

SELECT dte,
COUNT(A.N_Enc) AS tot_enc, 
SUM(A.Valor) AS tot_vendas
FROM dates D
LEFT JOIN vendas V ON V.data = D.dte
LEFT JOIN aff_sub A ON A.N_Enc = V.ID
GROUP BY dte