August 9, 2010 at 11:47 am
Hi people,
How can do a procedure (or something ) that return me a list of numbers
example:
select nums
from generate_serie(5001, 5999)
and this return
f
------
5001
5002
5003
....
5999
August 9, 2010 at 1:00 pm
Please have a look at the TallyTable article referenced in my signature.
Once you have such a table it's as easy as
SELECT @start + N
FROM Tally
WHERE N <= (@end - @start)
(You might need to add 1 here or there depending whether your table will start with Zero or 1 and/or if you'd need the results including/excluding the range values....
August 9, 2010 at 2:07 pm
select
a.NUMBER
from
-- Function F_TABLE_NUMBER_RANGE available on this link:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
dbo.F_TABLE_NUMBER_RANGE(5001, 5999) a
Results:
NUMBER
-----------
5001
5002
5003
5004
...
...
5997
5998
5999
(999 row(s) affected)
August 9, 2010 at 2:38 pm
nosoyspam (8/9/2010)
Hi people,How can do a procedure (or something ) that return me a list of numbers
example:
select nums
from generate_serie(5001, 5999)
and this return
f
------
5001
5002
5003
....
5999
What is the smallest start value and largest end value that you believe you'll need? Will they both be valid INT's or will they be BIGINT's?
If they are INT's, I will say that I personally tested the bejeezis out of Michael's function when I first became aware of it and it works very well and quite quickly. There are also a lot of "shorter" ways to do it but shorter doesn't matter when it comes to performance.
If, for some reason, Michael's function doesn't fit your bill, post back and we'll show you some dandy alternatives.
Now... if I can just get Michael to stop using all upper case and underscores 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2010 at 12:47 pm
Thanks LutzM, Michael and Jeff.
After check both solutions I thought that must use TallyTable because the numbers that I need are from 80,000,000 to 89,999,999.
But I really not need whole numbers in each query, maybe just 5000 by query, so maybe I can use in my query something like this
select f.num+80000000, t.num, t.date
from tally as f
left join mytable as t
On t.num = f.num+80000000
mmhhhhh... Now I think, I could do the same with the function of Micheal
what method do you think will be better?
We want to do a report to see if all numbers were used
Example of records ("start" and "end"are fields)
start end
1 74
75 109
115 164
with the report we want to see which numbers 110,111,112,113 and 114 were not used. (real numers are from 80,000,001 to 80,000,074)
August 11, 2010 at 1:24 pm
I'm not sure how your final solution would look like...
I'd use the following approach:
DECLARE @tbl TABLE
(
start_ INT, end_ INT
)
INSERT INTO @tbl
SELECT 1, 74 UNION ALL
SELECT 75, 109 UNION ALL
SELECT 115, 164
;
WITH rows_numbered AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY start_) AS ROW,
start_,
end_
FROM @tbl
),used_ranges AS
(
SELECT
rn1.row,
rn2.start_ AS end2,
rn1.end_ AS start2
FROM rows_numbered rn1
INNER JOIN rows_numbered rn2
ON rn1.row=rn2.row-1
WHERE rn2.start_> rn1.end_+1
)
SELECT
ur.start2,
ur.end2,
ur.start2+ t.n AS unused
FROM used_ranges ur
INNER JOIN tally t
ON t.n < ur.end2 - ur.start2
August 12, 2010 at 3:03 pm
declare @sIni varchar(10)
set @sIni = '1/01/2010'
Thanks LutzM, but that code don't work. Maybe is because I'm using SQL 2000. However I resolved it with this query
select h.holo , h2.holo as capturado
from (
--Series generated from min to max
--from all records
select t.n, t.n+(f.minimo-1) as holo
from tally as t, (
--this return min 80000001 and max 80009999
select min(inicial) as minimo, max(final) as maximo,
max(final)-min(inicial) as diferencia
from holograma
where fecha = @sIni
--example 9 between 80000001-(80000001-1) ...
-- is then 9 between 1 and ...
) as f where t.n between f.minimo-(f.minimo-1) and f.maximo-(f.minimo-1)
) as h
left join
--series generated from data captured
--Ej. from80000001-80000014 and 80000020-80000029 se genera
(select t.n, t.n+(f.minimo-1) as holo
from tally as t,(
select min(inicial) as minimo, max(final) as maximo, max(final)-min(inicial) as diferencia
from holograma
where fecha = @sIni
group by inicial, final
) as f where t.n between f.minimo-(f.minimo-1) and f.maximo-(f.minimo-1)
) as h2
on h.holo = h2.holo
where h2.holo is null
order by h.tipo, h.holo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply