2

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
  • 1
    It's not absolutely clear what your intended result is. Are you looking for your final result to have later, earlier, and same columns? Or do you want three rows for each category, correspond to those three values. I recommend you edit in an example of the expected output, as that may make your question clearer. – RDFozz Feb 7 at 21:27
  • are A and B fixed categories or can you change them? – seventyeightist Feb 7 at 21:45
4

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       |
4

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
3

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

1

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.