Filling in Empty Rows

  • Given a date parameter I've a query that will return a two column result set.  The first column is a number (1 -6) that represents a condition and the second column is a number that represents the count of records in that condition.  A typical result set may look like this:

    1, 30; 2, 100; 3, 123; 4, 34; 5, 221; 6, 300

    The problem I have is in those cases where a condition isn't present such that the 2 condition may be missing or the 3 condition may be missing.  What happens then is that the numbers skip when I want to see 1, 30; 2, 0; etc.

    My first thought is to create a temp table with all the possible values, 1-6 and join it to the aforementioned result set supplying 0 for the null values; however, it seems there should be a better way.

  • If I am understanding what you appear to be describing, you could use a VALUES table construct:

    SELECT V.I,
    ISNULL(YT.YourColumn,0) AS YourColumn
    FROM (VALUES(1),(2),(3),(4),(5),(6))V(I)
    LEFT JOIN dbo.YourTable YT ON V.I = YT.OtherColumn;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • That's how I would do it.  Use a tally table or table function to generate the values needed, then left join the tally table to your dataset , then us Isnull(value, 0) to generate the values

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you for the quick responses.  Very helpful.

  • The data looks something like this?

    drop table if exists #conditions;
    go
    create table #conditions(
    some_dt date not null,
    code varchar(400));

    insert #conditions(some_dt, code) values
    ('2020-01-12','1, 30; 2, 100; 3, 123; 4, 34; 5, 221; 6, 300'),
    ('2020-01-12','1, 30; 4, 34; 5, 221; 6, 300'),
    ('2020-01-12','4, 34; 5, 221'),
    ('2020-01-11','1, 30; 2, 100; 3, 123; 4, 34; 5, 221; 6, 300');

    select *
    from #conditions
    where some_dt='2020-01-12';
    some_dtcode
    2020-01-121, 30; 2, 100; 3, 123; 4, 34; 5, 221; 6, 300
    2020-01-121, 30; 4, 34; 5, 221; 6, 300
    2020-01-124, 34; 5, 221

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Well well, this is the first time I can recall ever using a RIGHT JOIN in a "real" query.  It seems to make sense here.

    declare @dt         date='2020-01-12'

    ;with
    conditions_cte(condition) as (
    select * from (values (1),(2),(3),(4),(5),(6)) v(condition)),
    rn_cte(code, rn) as (
    select code, row_number() over (order by (select null))
    from #conditions
    where some_dt=@dt),
    xj_cte(code, rn, condition) as (
    select rn.*, c.condition
    from rn_cte rn
    cross join conditions_cte c),
    fixed_cte(code, rn, condition, condition_pair) as (
    select x.code, x.rn, x.condition,
    concat_ws(',', x.condition, isnull(prs.count_of_records, 0))
    from rn_cte a
    cross apply string_split(a.code, ';') spl
    cross apply (values (charindex(',', spl.value), len(spl.value))) v(cx, ln)
    cross apply (values (cast(left(spl.value, v.cx-1) as int),
    cast(right(spl.value, v.ln-v.cx) as int))) prs(condition, count_of_records)
    right join xj_cte x on a.rn=x.rn
    and prs.condition=x.condition)
    select code original_code,
    string_agg(condition_pair, ';') within group (order by condition) fixed_code
    from fixed_cte
    group by code, rn;
    original_codefixed_code
    1, 30; 2, 100; 3, 123; 4, 34; 5, 221; 6, 3001,30;2,100;3,123;4,34;5,221;6,300
    1, 30; 4, 34; 5, 221; 6, 3001,30;2,0;3,0;4,34;5,221;6,300
    4, 34; 5, 2211,0;2,0;3,0;4,34;5,221;6,0

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply