SQL query to return aggregations in a constant format, even when the categories are missing-CodePudding

Is it possible with SQL Server to return table with a constant format?

Let's say we have the following raw data:

DATE       |  CATEGORY  |  VALUE
---------------------------------
01.01.2022 | Category 1 |   10
01.01.2022 | Category 1 |   20
01.01.2022 | Category 1 |   33
01.01.2022 | Category 3 |   15
03.01.2022 | Category 1 |   10
03.01.2022 | Category 2 |   20
03.01.2022 | Category 3 |   50
              (...)

And the desired output would be:

DATE       |  CATEGORY  |  VALUE
---------------------------------
01.01.2022 | Category 1 |    63
01.01.2022 | Category 2 |     0
01.01.2022 | Category 3 |    15
02.01.2022 | Category 1 |     0
02.01.2022 | Category 2 |     0
02.01.2022 | Category 3 |     0
03.01.2022 | Category 1 |    10
03.01.2022 | Category 2 |    20
03.01.2022 | Category 3 |    50
              (...)

Please notice that in the desired outcome there's a date present that's missing in the raw data, as well as sum of VALUE are 0 when the category is not present for a given date in the raw data.

CodePudding user response：

-- Contiguous dates table
DECLARE @dates TABLE(dt date) ;   
DECLARE @dateFrom date;
DECLARE @dateTo date;

select @dateFrom = (Select DateAdd(day, -1, Min(date)) from Agg);
select @dateTo = (Select Max(date) from Agg);

-- Query:


WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END

-- Category table 
DECLARE @categories TABLE(category nvarchar(20)) ;
insert into @categories values ('Category 1'),('Category 2'),('Category 3');

-- This cte helps in creating the constant output required 
with cte1 as (
select dt, category from 
@dates cross join @categories

) 
select cte1.dt as [Date], cte1.category, Sum(coalesce(yourTableName.value,0)) as Value
from cte1 left join yourTableName
on cte1.dt = yourTableName.[Date] and cte1.category = yourTableName.category
group by cte1.dt, cte1.category
order by cte1.dt, cte1.category

CodePudding user response：


WITH  ctedate AS
(
    SELECT d= v2.d * 10   v1.d
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(d)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(d)
)

Select b.date, a.category, sum(coalesce(a.value,0)) from 
yourtablename a 
cross join
 (SELECT DATEADD(DAY, ctedate.d, '2022-01-01') date
FROM ctedate
ORDER BY ctedate.d) b 
group by b.date, a.category