Case help and Identity help

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?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, mutemode@.gmail.com wrote:

Quote:

Originally Posted by

>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?

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

(snip)

Quote:

Originally Posted by

>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|||(mutemode@.gmail.com) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

On 23 Aug 2006 12:14:02 -0700, mutemode@.gmail.com wrote:
>
(snip)

Quote:

Originally Posted by

>>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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 23 Aug 2006 21:51:18 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

>On 23 Aug 2006 12:14:02 -0700, mutemode@.gmail.com wrote:
>>
>(snip)

Quote:

Originally Posted by

>>>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|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Capturing value of identity column for use later?

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @.@.IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@.WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

-------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@.WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@.CurWeekReportedLineID = @.@.IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@.WeekReportedID1,
@.CurWeekReportedLineID,
@.BeginDate1,
Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM#EstimateLines;
--------

The #Temp table create via VB:
--------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
--------

--
PeteCresswell"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.4ax.com...
> This doesn't work because the first INSERT is creating multiple
> records for multiple projects. @.@.IDENTITY, then, contains the Identity
> column value for the last tblWeekReportedLine record inserted.
> Consequently, all the hours records are then associated with
> that last value.

<snip
Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@.BeginDate1,
e.Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @.WeekReportedID1;

Simon|||RE/
>this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet...

Thanks. I may make my Monday deadline yet....

--
PeteCresswell

can't use identity column in where clause.

Hello,
Can anybody tell me why I can't use the identity column in the where clause
of the openxml query below? I am getting the following error "Invalid column
name 'DepId' ", but if I change the column "DepId" to not be an identity, the
query compiles.
********************Query************************* ********
CREATE PROCEDURE ESS_UpdateEmergencyCnt
@.emgData as nvarchar(4000)
AS
Declare @.hDoc int
DECLARE @.ReturnCode INT
exec sp_xml_prepareDocument @.hDoc OUTPUT, @.emgData
update prdepend
set prdepend.name = XMLPRDEPEND.name
from OPENXML(@.hDoc, '/ROOT/Contact')
with prdepend XMLPRDEPEND
where prdepend.DepId = XMLPRDEPEND.DepId
Exec sp_xml_removedocument @.hDoc
GO
This is a restriction of the OpenXML WITH clause since IDENTITY values are
generated automatically and we do not know, whether you want it to take from
the XML document or not.
The solution is to give an explicit WITH clause (instead of using the table
name).
Best regards
Michael
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:F55E156B-3FCE-4964-AFF5-ECB57782D59D@.microsoft.com...
> Hello,
> Can anybody tell me why I can't use the identity column in the where
> clause
> of the openxml query below? I am getting the following error "Invalid
> column
> name 'DepId' ", but if I change the column "DepId" to not be an identity,
> the
> query compiles.
> ********************Query************************* ********
> CREATE PROCEDURE ESS_UpdateEmergencyCnt
> @.emgData as nvarchar(4000)
> AS
> Declare @.hDoc int
> DECLARE @.ReturnCode INT
> exec sp_xml_prepareDocument @.hDoc OUTPUT, @.emgData
> update prdepend
> set prdepend.name = XMLPRDEPEND.name
> from OPENXML(@.hDoc, '/ROOT/Contact')
> with prdepend XMLPRDEPEND
> where prdepend.DepId = XMLPRDEPEND.DepId
> Exec sp_xml_removedocument @.hDoc
> GO
>
|||Hi Michael,
Thank you for the response. However, I am not sure what you mean by use an
explicit with clause instead of the table name. I already have a with clause
in the query. Would you mind giving me an example of what you mean?
thanks,
Sher
"Michael Rys [MSFT]" wrote:

> This is a restriction of the OpenXML WITH clause since IDENTITY values are
> generated automatically and we do not know, whether you want it to take from
> the XML document or not.
> The solution is to give an explicit WITH clause (instead of using the table
> name).
> Best regards
> Michael
> "Sher" <Sher@.discussions.microsoft.com> wrote in message
> news:F55E156B-3FCE-4964-AFF5-ECB57782D59D@.microsoft.com...
>
>
|||If your table prdepend looks like (key int identity, foo nvarchar(50), bar
int)
then instead of saying
WITH prdepend
say
WITH (
foo nvarchar(50).
bar int)
if you do not have values for key or
WITH (
key int,
foo nvarchar(50),
bar int)
if you have.
Best regards
Michael
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:1257118A-523E-4380-8F9B-AC4E50C0E0DC@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thank you for the response. However, I am not sure what you mean by use an
> explicit with clause instead of the table name. I already have a with
> clause
> in the query. Would you mind giving me an example of what you mean?
> thanks,
> Sher
> "Michael Rys [MSFT]" wrote:
|||Thanks Michael. It worked.
regards,
SA
"Michael Rys [MSFT]" wrote:

> If your table prdepend looks like (key int identity, foo nvarchar(50), bar
> int)
> then instead of saying
> WITH prdepend
> say
> WITH (
> foo nvarchar(50).
> bar int)
> if you do not have values for key or
> WITH (
> key int,
> foo nvarchar(50),
> bar int)
> if you have.
> Best regards
> Michael
> "Sher" <Sher@.discussions.microsoft.com> wrote in message
> news:1257118A-523E-4380-8F9B-AC4E50C0E0DC@.microsoft.com...
>
>

Can't Update Identity Column in Transactional rep.

Hi,
I have Transactional replication setup where at PUBLISHER 90% of my tables
have PK Auto Increment. Which replicated to SUBSCRIBER.
Publisher(Publishing database) get updated by Importer(In-House tool)
Subcriber get updated by Publisher as well from Website and needs to have PK
Auto Incremental enable.
So what have done is to set up different kind of range at Publisher (odd
number) and Subscriber(even number) for Identity Seed.
Also have modify the SP (sp_MSins) with SET IDENTITY_INSERT TABLE ON.
But I am keep getting error that can't update Identity column X and
reference to sp_MSUpd_
Could any one please let me know why I am getting this error, as SET
IDENTITY_INSERT TABLE ON is only when you inserting records not updating
records?
Shan
This is usually a compilation error from the stored procedure. Do you have
the identity attribute existing on the subscriber tables? If so, it
shouldn't be there for vanilla transactional replication as the identity
values are maintained by the publisher only. For queued updating subscribers
the identity property exists, but the stored procedures have a modified
syntax.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)