Can this been done?

Hi all,
I have a table:
CREATE TABLE [dbo].[StockStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NOT NULL ,
[CompanyID] [int] NULL ,
[ContactID] [int] NULL ,
[StockStatusID] [int] NOT NULL ,
[BY] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
I use this table for the current status and for history about the status
changes.
Everything so good so far.
BUT now the way of working has changed. There is a new status.
It is now also possible to go back in status.
I give an example:
Status 1 = create
Status2(new!) = reject
Status3 = assign.
Normaly status changed from (1)create to (3)assign.
But now after assign you can choose reject.
In an overview the user wants to see all the stock that have the status
assign (and now also reject)
but not stocks that are already assigned...:s
Enless, they are rejected ofcourse .. :s
The assigning and reject can occure multiple times in the process...
I am try to make a query this whole afternoon but without any luck :-(
Can this be done or do I need to split it into multiple tables or...'
Any advise is welcome...
TiaCan you give us some sample data (in the form of INSERT statements) and
sample output? I'm certain this is possible... Also, are you using SQL
Server 2000 or 2005? Some of the 2005 features may be helpful here.
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Goofy" <nomail@.goofy.com> wrote in message
news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I have a table:
> CREATE TABLE [dbo].[StockStatus] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [StockID] [int] NOT NULL ,
> [CompanyID] [int] NULL ,
> [ContactID] [int] NULL ,
> [StockStatusID] [int] NOT NULL ,
> [BY] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> I use this table for the current status and for history about the status
> changes.
> Everything so good so far.
> BUT now the way of working has changed. There is a new status.
> It is now also possible to go back in status.
> I give an example:
> Status 1 = create
> Status2(new!) = reject
> Status3 = assign.
> Normaly status changed from (1)create to (3)assign.
> But now after assign you can choose reject.
> In an overview the user wants to see all the stock that have the status
> assign (and now also reject)
> but not stocks that are already assigned...:s
> Enless, they are rejected ofcourse .. :s
> The assigning and reject can occure multiple times in the process...
> I am try to make a query this whole afternoon but without any luck :-(
> Can this be done or do I need to split it into multiple tables or...'
> Any advise is welcome...
> Tia
>
>|||I like to use binary encoding in cases where states need to be tracked.
Works like this:
status & 1 will be 1 if 1 bit is turned on, 0 otherwise
status & 2 will be 2 if 2 bit is turned on, 0 otherwise
status & 4 will be 4 if 4 bit is turned on, 0 otherwise
status & 8 will be 8 if 8 bit is turned on, 0 otherwise
...
So now you can have multiple states at one time: status 5 is the
combination of status 1 and status 4
You can turn on and off status bits by using bit wise operators, or simple
addition/subtraction
set status = 1 + 4 will give a status of 5
TheSQLGuru
President
Indicium Resources, Inc.
"Goofy" <nomail@.goofy.com> wrote in message
news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I have a table:
> CREATE TABLE [dbo].[StockStatus] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [StockID] [int] NOT NULL ,
> [CompanyID] [int] NULL ,
> [ContactID] [int] NULL ,
> [StockStatusID] [int] NOT NULL ,
> [BY] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> I use this table for the current status and for history about the status
> changes.
> Everything so good so far.
> BUT now the way of working has changed. There is a new status.
> It is now also possible to go back in status.
> I give an example:
> Status 1 = create
> Status2(new!) = reject
> Status3 = assign.
> Normaly status changed from (1)create to (3)assign.
> But now after assign you can choose reject.
> In an overview the user wants to see all the stock that have the status
> assign (and now also reject)
> but not stocks that are already assigned...:s
> Enless, they are rejected ofcourse .. :s
> The assigning and reject can occure multiple times in the process...
> I am try to make a query this whole afternoon but without any luck :-(
> Can this be done or do I need to split it into multiple tables or...'
> Any advise is welcome...
> Tia
>
>|||Yes, sure I can, thank you.
I am using SQL2000.
A simple version of the table:
CREATE TABLE [dbo].[StockStatus] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NOT NULL ,
[StockStatusID] [int] NOT NULL ,
[CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(22, 2, '27-8-2007 15:05:46')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(22, 4, '27-8-2007 15:10:11')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 3, '27-8-2007 16:15:51')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 4, '27-8-2007 15:10:11')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 2, '27-8-2007 15:15:46')
INSERT INTO StockStatus(
StockID, StockStatusID, CreateDate)
VALUES(23, 4, '27-8-2007 16:35:46')
What I try to get back is
StockStatus of stockid 22 is 3 (Latest time)
StockStatus of stockid 23 is 4 (Latest time)
I want only back the statuses of 2 and 3.
So in this example only stockID 22.
The table is filled with lots more records of course and statuses...
But if a status (with higher date exists in a higher status(then 2 or 3)) I
dont want them in the result.
I hope you can help me.
thnx
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
> Can you give us some sample data (in the form of INSERT statements) and
> sample output? I'm certain this is possible... Also, are you using SQL
> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Goofy" <nomail@.goofy.com> wrote in message
> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the status
>> changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>|||Hi TheSQLGuru,
Thanks for your input.
But a stock can have only have 1 status at the same time.
It is created, assigned or reject or used, etc.
thanks anyway.
Grz,
"TheSQLGuru" <kgboles@.earthlink.net> schreef in bericht
news:ebQBCAM6HHA.980@.TK2MSFTNGP06.phx.gbl...
>I like to use binary encoding in cases where states need to be tracked.
>Works like this:
> status & 1 will be 1 if 1 bit is turned on, 0 otherwise
> status & 2 will be 2 if 2 bit is turned on, 0 otherwise
> status & 4 will be 4 if 4 bit is turned on, 0 otherwise
> status & 8 will be 8 if 8 bit is turned on, 0 otherwise
> ...
> So now you can have multiple states at one time: status 5 is the
> combination of status 1 and status 4
> You can turn on and off status bits by using bit wise operators, or simple
> addition/subtraction
> set status = 1 + 4 will give a status of 5
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Goofy" <nomail@.goofy.com> wrote in message
> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the status
>> changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>|||How about:
SELECT *
FROM
(
SELECT *
FROM StockStatus ss
WHERE
ss.CreateDate = (
SELECT MAX(CreateDate)
FROM StockStatus ss1
WHERE ss1.StockId = ss.StockId
)
) x
WHERE
x.StockStatusId IN (2,3)
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Goofy" <nomail@.goofy.com> wrote in message
news:OqaY4FM6HHA.1208@.TK2MSFTNGP05.phx.gbl...
> Yes, sure I can, thank you.
> I am using SQL2000.
> A simple version of the table:
> CREATE TABLE [dbo].[StockStatus] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [StockID] [int] NOT NULL ,
> [StockStatusID] [int] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(22, 2, '27-8-2007 15:05:46')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(22, 4, '27-8-2007 15:10:11')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 3, '27-8-2007 16:15:51')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 4, '27-8-2007 15:10:11')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 2, '27-8-2007 15:15:46')
> INSERT INTO StockStatus(
> StockID, StockStatusID, CreateDate)
> VALUES(23, 4, '27-8-2007 16:35:46')
> What I try to get back is
> StockStatus of stockid 22 is 3 (Latest time)
> StockStatus of stockid 23 is 4 (Latest time)
> I want only back the statuses of 2 and 3.
> So in this example only stockID 22.
> The table is filled with lots more records of course and statuses...
> But if a status (with higher date exists in a higher status(then 2 or 3))
> I dont want them in the result.
> I hope you can help me.
> thnx
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
> news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
>> Can you give us some sample data (in the form of INSERT statements) and
>> sample output? I'm certain this is possible... Also, are you using SQL
>> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the status
>> changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>>
>|||That looks great...thanks alot
And performance wice?
Is did a good solution you think?
in this table there will be at least 100,000 records...
thank you
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
news:6CBB3FC9-3022-41EC-AFF3-3FCF421CB809@.microsoft.com...
> How about:
>
> SELECT *
> FROM
> (
> SELECT *
> FROM StockStatus ss
> WHERE
> ss.CreateDate => (
> SELECT MAX(CreateDate)
> FROM StockStatus ss1
> WHERE ss1.StockId = ss.StockId
> )
> ) x
> WHERE
> x.StockStatusId IN (2,3)
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Goofy" <nomail@.goofy.com> wrote in message
> news:OqaY4FM6HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Yes, sure I can, thank you.
>> I am using SQL2000.
>> A simple version of the table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 2, '27-8-2007 15:05:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 3, '27-8-2007 16:15:51')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 2, '27-8-2007 15:15:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 16:35:46')
>> What I try to get back is
>> StockStatus of stockid 22 is 3 (Latest time)
>> StockStatus of stockid 23 is 4 (Latest time)
>> I want only back the statuses of 2 and 3.
>> So in this example only stockID 22.
>> The table is filled with lots more records of course and statuses...
>> But if a status (with higher date exists in a higher status(then 2 or 3))
>> I dont want them in the result.
>> I hope you can help me.
>> thnx
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
>> news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
>> Can you give us some sample data (in the form of INSERT statements) and
>> sample output? I'm certain this is possible... Also, are you using SQL
>> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the
>> status changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the status
>> assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>>
>>
>|||Depending on the indexes in place, it should be pretty good. If you include
all of the columns necessary for the query (in order to cover it), the query
processor can use an index on (StockId, CreateDate DESC) to very efficiently
handle this kind of query.
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Goofy" <nomail@.goofy.com> wrote in message
news:efn3MNU6HHA.1148@.TK2MSFTNGP05.phx.gbl...
> That looks great...thanks alot
> And performance wice?
> Is did a good solution you think?
> in this table there will be at least 100,000 records...
>
> thank you
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
> news:6CBB3FC9-3022-41EC-AFF3-3FCF421CB809@.microsoft.com...
>> How about:
>>
>> SELECT *
>> FROM
>> (
>> SELECT *
>> FROM StockStatus ss
>> WHERE
>> ss.CreateDate =>> (
>> SELECT MAX(CreateDate)
>> FROM StockStatus ss1
>> WHERE ss1.StockId = ss.StockId
>> )
>> ) x
>> WHERE
>> x.StockStatusId IN (2,3)
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OqaY4FM6HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Yes, sure I can, thank you.
>> I am using SQL2000.
>> A simple version of the table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 2, '27-8-2007 15:05:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(22, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 3, '27-8-2007 16:15:51')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 15:10:11')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 2, '27-8-2007 15:15:46')
>> INSERT INTO StockStatus(
>> StockID, StockStatusID, CreateDate)
>> VALUES(23, 4, '27-8-2007 16:35:46')
>> What I try to get back is
>> StockStatus of stockid 22 is 3 (Latest time)
>> StockStatus of stockid 23 is 4 (Latest time)
>> I want only back the statuses of 2 and 3.
>> So in this example only stockID 22.
>> The table is filled with lots more records of course and statuses...
>> But if a status (with higher date exists in a higher status(then 2 or
>> 3)) I dont want them in the result.
>> I hope you can help me.
>> thnx
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> schreef in bericht
>> news:6B5AC7C1-B1C3-46A9-AE19-4325945A44FC@.microsoft.com...
>> Can you give us some sample data (in the form of INSERT statements) and
>> sample output? I'm certain this is possible... Also, are you using SQL
>> Server 2000 or 2005? Some of the 2005 features may be helpful here.
>>
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Goofy" <nomail@.goofy.com> wrote in message
>> news:OzUrt2L6HHA.2312@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> I have a table:
>> CREATE TABLE [dbo].[StockStatus] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [StockID] [int] NOT NULL ,
>> [CompanyID] [int] NULL ,
>> [ContactID] [int] NULL ,
>> [StockStatusID] [int] NOT NULL ,
>> [BY] [int] NOT NULL ,
>> [Date] [datetime] NOT NULL ,
>> [CreateDate] [datetime] NOT NULL
>> ) ON [PRIMARY]
>> I use this table for the current status and for history about the
>> status changes.
>> Everything so good so far.
>> BUT now the way of working has changed. There is a new status.
>> It is now also possible to go back in status.
>> I give an example:
>> Status 1 = create
>> Status2(new!) = reject
>> Status3 = assign.
>> Normaly status changed from (1)create to (3)assign.
>> But now after assign you can choose reject.
>> In an overview the user wants to see all the stock that have the
>> status assign (and now also reject)
>> but not stocks that are already assigned...:s
>> Enless, they are rejected ofcourse .. :s
>> The assigning and reject can occure multiple times in the process...
>> I am try to make a query this whole afternoon but without any luck :-(
>> Can this be done or do I need to split it into multiple tables or...'
>> Any advise is welcome...
>> Tia
>>
>>
>>
>sql

can not retrive @@identity value

I use select @.@.identity to return @.@.identity from my store procedure,
but I could not retrive it from my Visual basic code, like variable=
oRS.fields.item(0).value, it always says item can not be found...Hi

At a guess it is probably because it is not the first recordset being
returned.

Instead it may be best to use an output parameter to do this.

Also if you are using SQL 2000 then use the SCOPE_IDENTITY() function.

John

"Allan" <hlang121@.yahoo.com> wrote in message
news:436e7a2d.0308271236.3bd2edd2@.posting.google.c om...
> I use select @.@.identity to return @.@.identity from my store procedure,
> but I could not retrive it from my Visual basic code, like variable=
> oRS.fields.item(0).value, it always says item can not be found...|||Allan (hlang121@.yahoo.com) writes:
> I use select @.@.identity to return @.@.identity from my store procedure,
> but I could not retrive it from my Visual basic code, like variable=
> oRS.fields.item(0).value, it always says item can not be found...

So how does the VB code look like? And how does the stored procedure
look like?

Your chances to precise assistance increases if you care to share
the code you are having problem.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

One of the easiest way to get the identity value from sqlserver7.0, is

rs.open"select @.@.identity as id from tab1",..,..
box1 = rs!id

With Thanks
Raghu