Htydjtk

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