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_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
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_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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy