September 10, 2009 at 1:08 am
Hello,
I have a table with one column, which contains distinct data. Some of the data is missing. Now I need to get groups of this data (each group has same number of objects, let's say 4) and at the same time I need to know which data is missing.
For better understanding:
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (Id varchar(30))
insert into #temp (Id)
select '001' union all
select '002' union all
select '004' union all
select '005' union all
select '006' union all
select '007' union all
select '008' union all
select '011' union all
select '012' union all
select '014' union all
select '015' union all
select '017' union all
select '018'
-- I want to get this data (it could be in different format, but it should contain all information that I need)
select '001 - 005', 'Missing: 003' union all
select '006 - 011', 'Missing: 009, 010' union all
select '012 - 017', 'Missing: 013, 016' union all
select '018', ''
One approach is to pass whole table to C# (for example), loop through it and buid my result set there. But I still wonder if it is possible to solve this somehow with T-SQL without using cursors or other types of loops.
Thanks
September 10, 2009 at 1:39 am
How are you discriminating for the output ranges ?
Would the output of
'001 - 018', 'Missing: 003,009, 010,013, 016'
Be acceptable ?
If not, why not ?
----
EDIT : Dont worry , I see , i get it now
September 10, 2009 at 2:06 am
I think this is pretty close...
with cteWithGroup(id,Grouping)
as
(
Select Id,(Row_number() over (order by id)-1) /4
from #temp
)
,
cteRanges(MinId,MaxId,Grouping)
as
(
Select Min(id),Max(id),Grouping
from cteWithGroup
group by Grouping
),
cteFullRange(Id)
as
(
Select case when number <10 then '00' else '0' end+convert(varchar(3),number)
from master..spt_values
where TYPE='p' and number between 1 and 99
),
cteMissingIds(Grouping,MinId,MaxId,MissingId)
as
(
select cteRanges.Grouping,cteRanges.MinId,cteRanges.MaxId,MissingId = cteFullRange.id
from cteRanges
join cteFullRange
on cteFullRange.id between cteRanges.minId and cteRanges.MaxId
left outer join cteWithGroup
on cteWithGroup.grouping = cteRanges.grouping
and cteWithGroup.Id = cteFullRange.id
where cteWithGroup.grouping is null
),
cteRangesWithMissing(Grouping,MinId,MaxId,MissingId)
as
(
select Grouping,MinId,MaxId,MissingId from cteMissingIds
union
select cteRanges.Grouping,
cteRanges.MinId,
cteRanges.MaxId,
NULL
from cteRanges where Grouping not in(Select Grouping from cteMissingIds)
)
select distinct Grouping,MinId,MaxId,
stuff((Select ','+MissingId as [text()]
from cteRangesWithMissing InnerMissing
where cteRangesWithMissing.Grouping = InnerMissing.Grouping
order by Grouping for xml path('')),1,1,'')
from cteRangesWithMissing
September 16, 2009 at 1:08 am
That's pretty awesome query... have to try it out though...
Thanks a lot for your time!
September 16, 2009 at 1:30 am
Works great, I just had to change definition of cteFullRange to:
Select right('000000000000' + convert(varchar, number), 12)
from master..spt_values
where TYPE='p' and number between 1 and 2000
Thanks again 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply