quote:

---

Originally posted by kopzen

I mean telephone extensions listed on the database, see everytime theres a new user I have to physically go the records trying to find a spare number to use. So I need to automate that process so that SQL can find me the numbers for me instead.

---

select n.[number]
FROM
	dbo.F_TABLE_NUMBER_RANGE(2000,4000) AS n
WHERE
	NOT EXISTS (SELECT 1 FROM users u WHERE u.[UserDIDNumber] = n.[number])

; WITH powers AS (
	SELECT
		[N01], [N02], [N03]
	FROM
		(
			SELECT [N01] =  0 UNION SELECT    1 UNION SELECT    2 UNION SELECT    3
			UNION SELECT    4 UNION SELECT    5 UNION SELECT    6 UNION SELECT    7
			UNION SELECT    8 UNION SELECT    9 UNION SELECT   10 UNION SELECT   11
			UNION SELECT   12 UNION SELECT   13 UNION SELECT   14 UNION SELECT   15
		) NO1

		CROSS JOIN (
			SELECT [N02] =  0 UNION SELECT   16 UNION SELECT   32 UNION SELECT   48
			UNION SELECT   64 UNION SELECT   80 UNION SELECT   96 UNION SELECT  112
			UNION SELECT  128 UNION SELECT  144 UNION SELECT  160 UNION SELECT  176
			UNION SELECT  192 UNION SELECT  208 UNION SELECT  224 UNION SELECT  240
		) NO2

		CROSS JOIN (
			SELECT [N03] =  0 UNION SELECT  256 UNION SELECT  512 UNION SELECT  768
			UNION SELECT 1024 UNION SELECT 1280 UNION SELECT 1536 UNION SELECT 1792
			UNION SELECT 2048 UNION SELECT 2304 UNION SELECT 2560 UNION SELECT 2816
			UNION SELECT 3072 UNION SELECT 3328 UNION SELECT 3584 UNION SELECT 3840
		) N03
	)

,  NUMBER_RANGE AS (
	
	SELECT TOP 100 PERCENT
		[number] = ( a.[number] + b.[number] ) + 2000
	FROM
		(
			SELECT TOP 100 PERCENT
				[number] = CAST([N01] + [N02] + [N03] AS INT)
			FROM
				powers
			WHERE
				[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(2001))) AS INT)
			ORDER BY
				1
		) a
		CROSS JOIN (
			SELECT TOP 100 PERCENT
				[number] =  CAST(([N01] + [N02] + [N03]) * CAST(CEILING(SQRT(ABS(2001 ))) AS INT) AS INT)
			FROM 
				powers
			WHERE
				[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(2001 ))) AS INT)
			ORDER BY
				1
		) b
	WHERE
		a.[number] + b.[number] < ABS(2001)
		AND (16777216 - ABS(1999)) > 0
	)

select n.[number]
FROM
	number_range n
WHERE
	NOT EXISTS (SELECT 1 FROM users u WHERE UserDIDNumber = [number])

quote:

---

Wouldn't a simple not between clause be useful in this case?

PBUH
Edited by - Idera on 04/12/2010 05:53:31

---

quote:

---

Originally posted by Idera

Wouldn't a simple not between clause be useful in this case?

PBUH

---

quote:

---

Originally posted by Transact Charlie

Do you mean that the allowed list of phone extensions is between the numbers 2000 and 4000? so there are 2000 possible phone extensions and you want sql to show you which are not in use?
[/red]

---

/****** Object:  UserDefinedFunction [dbo].[F_TABLE_NUMBER_RANGE]    Script Date: 04/12/2010 11:26:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[F_TABLE_NUMBER_RANGE]
(
	@startNumber INT
	, @endNumber INT
)
RETURNS TABLE AS RETURN
(
WITH powers AS (
	SELECT
		[N01], [N02], [N03]
	FROM
		(
			SELECT [N01] =  0 UNION SELECT    1 UNION SELECT    2 UNION SELECT    3
			UNION SELECT    4 UNION SELECT    5 UNION SELECT    6 UNION SELECT    7
			UNION SELECT    8 UNION SELECT    9 UNION SELECT   10 UNION SELECT   11
			UNION SELECT   12 UNION SELECT   13 UNION SELECT   14 UNION SELECT   15
		) NO1

		CROSS JOIN (
			SELECT [N02] =  0 UNION SELECT   16 UNION SELECT   32 UNION SELECT   48
			UNION SELECT   64 UNION SELECT   80 UNION SELECT   96 UNION SELECT  112
			UNION SELECT  128 UNION SELECT  144 UNION SELECT  160 UNION SELECT  176
			UNION SELECT  192 UNION SELECT  208 UNION SELECT  224 UNION SELECT  240
		) NO2

		CROSS JOIN (
			SELECT [N03] =  0 UNION SELECT  256 UNION SELECT  512 UNION SELECT  768
			UNION SELECT 1024 UNION SELECT 1280 UNION SELECT 1536 UNION SELECT 1792
			UNION SELECT 2048 UNION SELECT 2304 UNION SELECT 2560 UNION SELECT 2816
			UNION SELECT 3072 UNION SELECT 3328 UNION SELECT 3584 UNION SELECT 3840
		) N03
	)
SELECT TOP 100 PERCENT
	[number] = ( a.[number] + b.[number] ) + CASE WHEN @startNumber <= @endNumber THEN @startNumber ELSE @endNumber END
FROM
	(
		SELECT TOP 100 PERCENT
			[number] = CAST([N01] + [N02] + [N03] AS INT)
		FROM
			powers
		WHERE
			[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(@startNumber - @endNumber) + 1 )) AS INT)
		ORDER BY
			1
	) a
	CROSS JOIN (
		SELECT TOP 100 PERCENT
			[number] =  CAST(([N01] + [N02] + [N03]) * CAST(CEILING(SQRT(ABS(@startNumber - @endNumber) + 1 )) AS INT) AS INT)
		FROM 
			powers
		WHERE
			[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(@startNumber - @endNumber) + 1 )) AS INT)
		ORDER BY
			1
	) b
WHERE
	a.[number] + b.[number] < ABS(@startNumber - @endNumber) + 1
	AND (16777216 - ABS( @startNumber - @endNumber ) - 1 ) > 0
ORDER BY
	1
)

DECLARE @i AS INT 
DECLARE @temptable AS TABLE( ext int)

SELECT @i = 2000
WHILE @i <= 4000 
BEGIN 
	IF NOT EXISTS ( SELECT 1 FROM table1 WHERE Ext = @i )
	BEGIN 
		INSERT INTO @temptable VALUES(@i)
	END 
	SELECT @i = @i + 1	
END
SELECT * FROM @temptable

quote:

---

Originally posted by vaibhavktiwari83

Can this work

---

quote:

---

Originally posted by kopzen

Thanks TC

Im using SQL 2005 and I dont know how to create or call a function. All I know is how to SLECT * FROM TABLE.

Mabye we should begin there.

So what should I run first and what will it do?

---

; WITH powers AS (
	SELECT
		[N01], [N02], [N03]
	FROM
		(
			SELECT [N01] =  0 UNION SELECT    1 UNION SELECT    2 UNION SELECT    3
			UNION SELECT    4 UNION SELECT    5 UNION SELECT    6 UNION SELECT    7
			UNION SELECT    8 UNION SELECT    9 UNION SELECT   10 UNION SELECT   11
			UNION SELECT   12 UNION SELECT   13 UNION SELECT   14 UNION SELECT   15
		) NO1

		CROSS JOIN (
			SELECT [N02] =  0 UNION SELECT   16 UNION SELECT   32 UNION SELECT   48
			UNION SELECT   64 UNION SELECT   80 UNION SELECT   96 UNION SELECT  112
			UNION SELECT  128 UNION SELECT  144 UNION SELECT  160 UNION SELECT  176
			UNION SELECT  192 UNION SELECT  208 UNION SELECT  224 UNION SELECT  240
		) NO2

		CROSS JOIN (
			SELECT [N03] =  0 UNION SELECT  256 UNION SELECT  512 UNION SELECT  768
			UNION SELECT 1024 UNION SELECT 1280 UNION SELECT 1536 UNION SELECT 1792
			UNION SELECT 2048 UNION SELECT 2304 UNION SELECT 2560 UNION SELECT 2816
			UNION SELECT 3072 UNION SELECT 3328 UNION SELECT 3584 UNION SELECT 3840
		) N03
	)

,  NUMBER_RANGE AS (
	
	SELECT TOP 100 PERCENT
		[number] = ( a.[number] + b.[number] ) + 2000
	FROM
		(
			SELECT TOP 100 PERCENT
				[number] = CAST([N01] + [N02] + [N03] AS INT)
			FROM
				powers
			WHERE
				[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(2001))) AS INT)
			ORDER BY
				1
		) a
		CROSS JOIN (
			SELECT TOP 100 PERCENT
				[number] =  CAST(([N01] + [N02] + [N03]) * CAST(CEILING(SQRT(ABS(2001 ))) AS INT) AS INT)
			FROM 
				powers
			WHERE
				[N01] + [N02] + [N03] < CAST(CEILING(SQRT(ABS(2001 ))) AS INT)
			ORDER BY
				1
		) b
	WHERE
		a.[number] + b.[number] < ABS(2001)
		AND (16777216 - ABS(1999)) > 0
	)

select n.[number]
FROM
	number_range n
WHERE
	NOT EXISTS (SELECT 1 FROM users u WHERE UserDIDNumber = [number])