Sql-server – Include all possible values of sub-categories under each category

querysql server

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

Best Answer

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       |
Related Question