Case help and Identity help

[mutemode]

I have this query

SELECT 'bracket' = CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END, count(income) AS number
FROM #persons
GROUP BY CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END
ORDER BY min(income) ASC

which returns

bracket number
-------- -----------
50-99 4
100-499 4
500-1000 2

I want it to return this

bracket number
-------- -----------
0-49 0
50-99 4
100-499 4
500-1000 2
Other 0

Showing that there are no incomes within the 0-49 category and 0
incomes in the other category. Halp?
AND

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

[Roy Harvey]

Consider creating a table Brackets, which would allow:

SELECT B.bracket, count(income) AS number
FROM Brackets as B
LEFT OUTER JOIN #persons as P
ON O.income BETWEEN B.FromIncome AND B.ToIncome
GROUP BY B.bracket
ORDER BY min(income) ASC

You will have to add one for negative numbes, and another for positive
numbers 1000, both assigned 'Other' for the bracket column.

Roy Harvey
Beacon Falls, CT

On 23 Aug 2006 12:14:02 -0700, mu******@gmail.com wrote:

>I have this query

SELECT 'bracket' = CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END, count(income) AS number
FROM #persons
GROUP BY CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END
ORDER BY min(income) ASC

which returns

bracket number
-------- -----------
50-99 4
100-499 4
500-1000 2

I want it to return this

bracket number
-------- -----------
0-49 0
50-99 4
100-499 4
500-1000 2
Other 0

Showing that there are no incomes within the 0-49 category and 0
incomes in the other category. Halp?
AND

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

[Hugo Kornelis]

On 23 Aug 2006 12:14:02 -0700, mu******@gmail.com wrote:

(snip)

>AND

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

Hi mutemode,

SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b
--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

(mu******@gmail.com) writes:

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

Why IDENTITY?

Here is a script for a million numbers. It's a tad slow for a temp
table, but why temp table? A table of numbers comes in handy in
several places.

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
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 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

If you insist on exactly 1000 numbers, you can easily cut it down. It
will probably run a lot faster than when inserting a million numbers.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:

On 23 Aug 2006 12:14:02 -0700, mu******@gmail.com wrote:

(snip)

>>AND

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

Hi mutemode,

SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b

IF (SELECT COUNT(*) FROM #ten) < 1000 OR
(SELECT MIN(id) FROM #ten) <1 OR
(SELECT MAX(id) FROM #ten) <1000
BEGIN
RAISERROR ('Fill of #ten failed!', 16, 1)
RETURN 1
END

That is, I don't think one should trust the code above to always return
what you looking for. Adding some paranoia can avoid incorrect results.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Wed, 23 Aug 2006 21:51:18 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:

>On 23 Aug 2006 12:14:02 -0700, mu******@gmail.com wrote:

(snip)

>>>AND

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

Hi mutemode,

SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b

IF (SELECT COUNT(*) FROM #ten) < 1000 OR
(SELECT MIN(id) FROM #ten) <1 OR
(SELECT MAX(id) FROM #ten) <1000
BEGIN
RAISERROR ('Fill of #ten failed!', 16, 1)
RETURN 1
END

That is, I don't think one should trust the code above to always return
what you looking for. Adding some paranoia can avoid incorrect results.

Hi Erland,

Some paranoia is good, but too much is, well, too much <g>

I agree with the test for a COUNT(*) of less than 1000 (though even in
an empty database, sysobjects has 47 rows so the cross join should be
good 2209 rows).

The tests for MIN and MAX remind me of the examples of "defensive
programming" I have seen when I still programmed PL/I on a mainframe. In
T-SQL equivalent, the code read something like this:
SET @SomeVariable = 15;
IF @SomeVariable <15
BEGIN;
RAISERROR ('The DBMS has a bug!', 16, 1);
END;
Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).

Finally, the COUNT(*) check can be replaced by a much more efficient
check for @@ROWCOUNT. The end result would be (correcting my error in
the requested table name while I'm at it:

SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #thousand
FROM sysobjects AS a, sysobjects AS b;

IF @@ROWCOUNT < 1000
BEGIN;
RAISERROR ('Fill of #thousand failed - not enough rows in sysobjects!
Please add an extra occurence of sysobejcts to the FROM clause.', 16,
1);
ROLLBACK TRANSACTION;
END;
--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:

Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).

It's difficult to say what is advertised. We know that you should not
reply on ORDER BY. Here is a TOP, that I don't really whether I can trust.

The paranoid check is cheap. The cost for an unexpected result is expensive.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx