mjhjmtu

How can it be achieved in MS SQL 2016, when I need to always see every state under all existing categories even if there are no records, which satisfy the state formula?

Under a and b below I always want to see all three of later, earlier, and same with null counts whenever there is no corresponding record.

I guess it should be achievable with some sort of cross join?..

declare @t table (tid int not null identity(1,1) primary key clustered
 ,tcat char(1)
 ,tdat1 date
 ,tdat2 date
)

insert into @t select * from (values
 ('a', '2019-01-01', '2019-02-01')
 ,('a', '2019-02-01', '2019-01-01')
 ,('b', '2019-02-01', '2019-01-01')
 ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

select
 tcat as category
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end as state
 ,count(1) as howmany
from @t
group by
 tcat
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end

If I understand your question correctly, here is one way to achieve your goal

--demo setup
declare @t table (tid int not null identity(1,1) primary key clustered
 ,tcat char(1)
 ,tdat1 date
 ,tdat2 date
)

insert into @t select * from (values
 ('a', '2019-01-01', '2019-02-01')
 ,('a', '2019-02-01', '2019-01-01')
 ,('b', '2019-02-01', '2019-01-01')
 ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

--the solution
;with BaseData as
(
select
 tcat as category
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end as state
 ,count(1) as howmany
from @t
group by
 tcat
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end
) 
select * from BaseData
union 
select tcat,'later' as state, 0 as howmany from @t t
where not exists(select * from basedata where category = t.tcat and state = 'later')
union 
select tcat,'earlier' as state, 0 as howmany from @t t
where not exists(select * from basedata where category = t.tcat and state = 'earlier')
union 
select tcat,'same' as state, 0 as howmany from @t t
where not exists(select * from basedata where category = t.tcat and state = 'same')

| category | state | howmany |
|----------|---------|---------|
| a | earlier | 1 |
| a | later | 1 |
| a | same | 0 |
| b | earlier | 0 |
| b | later | 1 |
| b | same | 1 |

Here's a simple approach that uses CROSS JOIN to project all possible combinations of category and state, then OUTER JOIN to the data.

declare @t table (tid int not null identity(1,1) primary key clustered
 ,tcat char(1)
 ,tdat1 date
 ,tdat2 date
);

insert into @t select * from (values
 ('a', '2019-01-01', '2019-02-01')
 ,('a', '2019-02-01', '2019-01-01')
 ,('b', '2019-02-01', '2019-01-01')
 ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2);

WITH t AS (
 SELECT
 tcat AS category,
 CASE
 WHEN tdat1 > tdat2 THEN 'later'
 WHEN tdat1 < tdat2 THEN 'earlier'
 WHEN tdat1 = tdat2 THEN 'same'
 END AS STATE,
 COUNT(1) AS howmany
 FROM @t
 GROUP BY
 tcat,
 CASE
 WHEN tdat1 > tdat2 THEN 'later'
 WHEN tdat1 < tdat2 THEN 'earlier'
 WHEN tdat1 = tdat2 THEN 'same'
 END
)
SELECT
 tcat.category,
 p.state,
 ISNULL(t.howmany, 0) AS howmany
FROM (SELECT DISTINCT tcat AS category FROM @t) tcat
 CROSS JOIN (VALUES ('earlier'), ('same'), ('later')) p(state)
 LEFT OUTER JOIN t
 ON tcat.category = t.category
 AND p.state = t.state

Another method (similar to @db2's answer):

with
 grp as
 ( select
 tcat,
 sign(datediff(day, tdat2, tdat1)) as sgn,
 count(1) as howmany
 from @t
 group by
 tcat,
 sign(datediff(day, tdat2, tdat1))
 )
select 
 cat.tcat as category,
 st.state,
 coalesce(grp.howmany, 0) as howmany
from
 ( values
 (-1, 'earlier'),
 ( 0, 'same'),
 (+1, 'later')
 ) as st (sgn, state)
 cross join
 ( select distinct tcat
 from @t
 ) as cat (tcat)
 left join grp 
 on grp.sgn = st.sgn
 and grp.tcat = cat.tcat ;

Test at dbfiddle.uk

Depending on how you want to display your results, you can use PIVOT or UNPIVOT to present "same, earlier, later" as columns or rows:

More info: PIVOT

Columns:

declare @t table (tid int not null identity(1,1) primary key clustered
 ,tcat char(1)
 ,tdat1 date
 ,tdat2 date
)

insert into @t select * from (values
 ('a', '2019-01-01', '2019-02-01')
 ,('a', '2019-02-01', '2019-01-01')
 ,('b', '2019-02-01', '2019-01-01')
 ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

SELECT category,
 COALESCE(pvt.later, 0) AS later,
 COALESCE(pvt.earlier, 0) AS earlier,
 COALESCE(pvt.same, 0) AS same
FROM
(
 select
 tcat as category
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end as state
 ,count(1) as howmany
 from @t
 group by
 tcat
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end
) src
PIVOT
(
 SUM(howmany) FOR [state] IN ([later], [earlier], [same])
) pvt

Output:

category later earlier same
--------------------------------
a 1 1 0
b 1 0 1

Rows:

declare @t table (tid int not null identity(1,1) primary key clustered
 ,tcat char(1)
 ,tdat1 date
 ,tdat2 date
)

insert into @t select * from (values
 ('a', '2019-01-01', '2019-02-01')
 ,('a', '2019-02-01', '2019-01-01')
 ,('b', '2019-02-01', '2019-01-01')
 ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

SELECT *
FROM 
(
 SELECT category,
 COALESCE(pvt.later, 0) AS later,
 COALESCE(pvt.earlier, 0) AS earlier,
 COALESCE(pvt.same, 0) AS same
 FROM
 (
 select
 tcat as category
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end as state
 ,count(1) as howmany
 from @t
 group by
 tcat
 ,case
 when tdat1 > tdat2 then 'later'
 when tdat1 < tdat2 then 'earlier'
 when tdat1 = tdat2 then 'same'
 end
 ) src
 PIVOT
 (
 SUM(howmany) FOR [state] IN ([later], [earlier], [same])
 ) pvt
)src
UNPIVOT
(
 Howmany for State IN ([later], [earlier], [same])
) upv

Output:

category Howmany State
------------------------
a 1 later
a 1 earlier
a 0 same
b 1 later
b 0 earlier
b 1 same