January 12, 2021 at 3:18 pm
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.
January 12, 2021 at 4:10 pm
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
January 12, 2021 at 4:13 pm
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/
January 12, 2021 at 4:15 pm
Thank you for the quick responses. Very helpful.
January 12, 2021 at 5:06 pm
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_dt code
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
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 12, 2021 at 5:40 pm
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_code fixed_code
1, 30; 2, 100; 3, 123; 4, 34; 5, 221; 6, 300 1,30;2,100;3,123;4,34;5,221;6,300
1, 30; 4, 34; 5, 221; 6, 300 1,30;2,0;3,0;4,34;5,221;6,300
4, 34; 5, 221 1,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