August 9, 2014 at 2:41 am
I have been fighting this and wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314
Desired out put:
From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
WITH T
AS (SELECT DENSE_RANK() OVER (ORDER BY voucher) - voucher AS Grp,
voucher
FROM #tempVoucher)
-- WHERE status = 0)
SELECT MIN(voucher) AS [From],
MAX(voucher) AS [To]
FROM T
GROUP BY Grp
ORDER BY MIN(voucher)
Thanks in advance for the assistance.
August 9, 2014 at 3:17 am
The first issue based on your sample data: 209 usually isn't followed by 2010 but rather 309.
The "classic" gaps'n'islands will fail here since there's no consecutive pattern.
A solution could be to modify your sample data (at least for the query by using a cte) to return consecutive numbers where you'll expect them.
This would mean to turn 209 into 2009 and all 14-digit values into 15-digit values, respectively.
After that, remove the first 11 character (ABCD-001869) and apply the gaps'n'islands solution (you might want to look at Jeffs article on how to do it efficiently).
August 9, 2014 at 3:22 am
Thanks. I will try that and as for the 2 sets of numbers 2010 and 2011, those are typos. Should be 210 and 211.
August 10, 2014 at 11:34 am
Ok, I am able to gather the missing gaps with the following:
SELECT distinct voucher vouchers,right(voucher, charindex('-', voucher) + 4) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '07/31/2014' and VOUCHER like 'APIN%'
select start, stop from (
select m.voucher + 1 as start,
(select min(voucher) - 1 from #tempVoucher as x where x.voucher > m.voucher) as stop
from #tempVoucher as m
left outer join #tempVoucher as r on m.voucher = r.voucher - 1
where r.voucher is null
) as x
where stop is not null
order by start
However, though this gives me only the missing gaps, I am wanting to do the same thing, but instead of missing gaps, I want to see the non missing gaps. I have used CTE and tried various ways and I am beginning to wonder if it is possible.
Thanks
August 10, 2014 at 11:47 am
Here's a slightly different approach:
create table #x (col1 int)
insert into #x
values
(202),(203),(204),(205),(209),(210),(211),(309),
(310),(311),(312),(313),(314);
WITH cte as
(
SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) as id, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp
FROM #x
)
SELECT MIN(col1) as [start], MAX(col1) as [end]
FROM cte
GROUP BY grp
ORDER BY grp
DROP TABLE #x
August 10, 2014 at 12:54 pm
Thanks so much. That worked. I was getting to the point that I could not see the trees through the forest.
August 11, 2014 at 4:07 pm
Just a quick note about duplicates.
If the possibility exists for duplicate values then you would use DENSE_RANK instead of ROW_NUMBER. In the examples below I took Lutz' code and added a duplicate value (312) so you can see what I am talking about.
create table #x (col1 int)
insert into #x
values
(202),(203),(204),(205),(209),(210),(211),(309),
(310),(311),(312),(312),(313),(314);
WITH cte as
(
SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) as id, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp
FROM #x
)
SELECT MIN(col1) as [start], MAX(col1) as [end]
FROM cte
GROUP BY grp
ORDER BY grp
DROP TABLE #x;
GO
create table #x (col1 int)
insert into #x
values
(202),(203),(204),(205),(209),(210),(211),(309),
(310),(311),(312),(312),(313),(314);
WITH cte as
(
SELECT col1, DENSE_RANK() OVER (ORDER BY col1) as id, col1 - DENSE_RANK() OVER (ORDER BY col1) as grp
FROM #x
)
SELECT MIN(col1) as [start], MAX(col1) as [end]
FROM cte
GROUP BY grp
ORDER BY grp
DROP TABLE #x
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply