SQL creating overview for data that does not exist

I need a script that will give me a total sum of amounts per sort for the past 3 days and upcoming week. I have made a script (link to dbfiddle.uk) that will sum the amount of the grouped date and sort types. However I am unclear on how to move forward at this point.

I want the past 3 days and upcoming 7 days of the today’s date (dynamically), not just the dates found in my table. I also want to show all the sort types.

So if there are no records for the date and sort type, show 0 as result.

plannings table

id  date        cell_id farmer_id
1   2020-04-21  1       1
2   2020-04-22  1       1
3   2020-04-24  1       1
4   2020-04-21  2       1
5   2020-04-22  2       1
6   2020-04-23  1       1
7   2020-04-25  1       1
8   2020-04-26  1       1
9   2020-04-22  4       1
10  2020-04-21  4       1
11  2020-04-23  4       1

planning_amounts table

id  planning_id sort_type_id    amount
2   1           1               43
3   1           3               34
4   2           1               54
5   3           1               45
6   4           1               90
7   5           3               45
8   5           1               99
9   6           1               66
10  7           1               999
11  8           3               90
12  9           1               23
13  10          1               43
14  11          1               55

sort_types table

id  name        description
1   Fijn        Fijn
2   Middel      Middel
3   Reuze       Reuze
4   Industrie   Industrie

The expected result would look like this. (this obviously for the past 3 + upcomming 7 days)

amount  description date
176     Fijn        2020-04-21
34      Reuze       2020-04-21
0       Middel      2020-04-21
0       Industrie   2020-04-21
176     Fijn        2020-04-22
45      Reuze       2020-04-22
0       Middel      2020-04-22
0       Industrie   2020-04-22
121     Fijn        2020-04-23
0       Reuze       2020-04-23
0       Middel      2020-04-23
0       Industrie   2020-04-23

Query

SELECT SUM(amount) as amount, a.date, c.description
FROM planning_amounts b
join plannings a ON b.planning_id = a.id
join (SELECT * from sort_types) c ON b.sort_type_id = c.id
group by date, c.description
order by date

Answer

Hope I understood your question a bit better now:

WITH DesiredDates AS 
(SELECT CAST(DATEADD(dd,-3,GETDATE()) as DATE) AS DesiredDate UNION ALL
 SELECT CAST(DATEADD(dd,-2,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,-1,GETDATE()) as DATE) UNION ALL
 SELECT CAST(GETDATE() as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,1,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,2,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,3,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,4,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,5,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,6,GETDATE()) as DATE) UNION ALL
 SELECT CAST(DATEADD(dd,7,GETDATE()) as DATE) 
), DesiredDatesAndSortTypes AS (
SELECT * FROM DesiredDates CROSS JOIN (select id from sort_types) t
) 
SELECT SUM(ISNULL(Amount,0)) as Amount, DesiredDate, c.Description 
FROM planning_amounts b
join plannings a ON b.planning_id = a.id
right join DesiredDatesAndSortTypes ddst ON CAST(a.date as DATE)=ddst.DesiredDate and b.sort_type_id=ddst.id
join sort_types c ON ddst.id = c.id
GROUP BY ddst.DesiredDate,c.Description 
ORDER BY DesiredDate,Description

Here’s the fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c508639ba4ec5bd49b49c9afe0692c9f

If I missunderstood yet again, please clarify where 🙂