Find the last time that 'A SET' was authorised. How ?

Hello,
I am working on a problem, have not yet found 'the solution',
but am not stuck either. (So the work is still in progress).
I am trying to solve the problem with 'set' operations.
(No cursors, no control statements, 'to keep it simple').
'THINGY' a set of rows spanning several tables. Each row
has an order (an identity field) and a status field.
I am looking for when the status field changed the last time
to 50 or above.
(Find the last time that 'THINGY' was authorized (status>=50))
Examples :
Order of values in 'THINGY' (Order from left to right, status is the value)
A. 10 20 50(<ok) 60 70
B. 10 20 50(<ok) 50 50 60 70
C. ... 10 50 60 40 45 55(<ok) 60 70
D. ... 10 50 60 40 45 60(<ok) 70 20 30
A and B a simple. (First value equal or above 50).
C (First value equal or above 50 not followed by a lower value).
D (Question Mark).
As said I am not stuck yet, but the code is not getting prettier.
(Keep it simple is my motto).
Anybody an elegant set solution ? Or tips ?
Some DDL and data follows this message. (Thingy table and example data).
Thanks for your time,
ben brugman.
CREATE TABLE [dbo].[THINGY_table] (
[setname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ordernr] [int] IDENTITY (1, 1) NOT NULL ,
[authorised] [int] NULL ,
[comment] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--INSERTs generated by 'sp_generate_inserts' stored procedure written by
Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @. vyaskn@.hotmail.com
--http://vyaskn.tripod.com
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[THINGY_table] ON
GO
PRINT 'Inserting values into [THINGY_table]'
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('A',1,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('A',2,20,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('A',3,50,'Last authorised
A')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('A',4,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('A',5,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',6,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',7,20,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',8,50,'Last authorised
B')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',9,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',10,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',11,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('B',12,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',13,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',14,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',15,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',16,40,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',17,45,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',18,55,'Last
authorised C')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',19,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('C',20,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',21,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',22,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',23,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',24,40,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',25,45,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',26,60,'Last
authorised D')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',27,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',28,20,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comment])VALUES('D',29,30,NULL)
PRINT 'Done'
SET IDENTITY_INSERT [dbo].[THINGY_table] OFF
GO
SET NOCOUNT OFFSELECT t1.setname, MAX(t1.ordernr)
FROM THINGY_table t1
WHERE t1.authorised >= 50
AND (SELECT TOP 1 t2.authorised FROM THINGY_table t2
WHERE t2.setname = t1.setname AND t2.ordernr < t1.ordernr
ORDER BY t2.ordernr DESC) < 50
GROUP BY t1.setname
Thanks for the scripts btw, made life a lot easier.
--
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eUsIin$4DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on a problem, have not yet found 'the solution',
> but am not stuck either. (So the work is still in progress).
> I am trying to solve the problem with 'set' operations.
> (No cursors, no control statements, 'to keep it simple').
> 'THINGY' a set of rows spanning several tables. Each row
> has an order (an identity field) and a status field.
> I am looking for when the status field changed the last time
> to 50 or above.
> (Find the last time that 'THINGY' was authorized (status>=50))
> Examples :
> Order of values in 'THINGY' (Order from left to right, status is the
value)
> A. 10 20 50(<ok) 60 70
> B. 10 20 50(<ok) 50 50 60 70
> C. ... 10 50 60 40 45 55(<ok) 60 70
> D. ... 10 50 60 40 45 60(<ok) 70 20 30
> A and B a simple. (First value equal or above 50).
> C (First value equal or above 50 not followed by a lower value).
> D (Question Mark).
> As said I am not stuck yet, but the code is not getting prettier.
> (Keep it simple is my motto).
> Anybody an elegant set solution ? Or tips ?
> Some DDL and data follows this message. (Thingy table and example data).
> Thanks for your time,
> ben brugman.
> CREATE TABLE [dbo].[THINGY_table] (
> [setname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ordernr] [int] IDENTITY (1, 1) NOT NULL ,
> [authorised] [int] NULL ,
> [comment] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --INSERTs generated by 'sp_generate_inserts' stored procedure written by
> Vyas
> --Build number: 22
> --Problems/Suggestions? Contact Vyas @. vyaskn@.hotmail.com
> --http://vyaskn.tripod.com
> SET NOCOUNT ON
> SET IDENTITY_INSERT [dbo].[THINGY_table] ON
> GO
>
> PRINT 'Inserting values into [THINGY_table]'
>
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('A',1,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('A',2,20,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('A',3,50,'Last
authorised
> A')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('A',4,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('A',5,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',6,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',7,20,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',8,50,'Last
authorised
> B')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',9,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',10,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',11,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('B',12,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',13,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',14,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',15,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',16,40,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',17,45,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',18,55,'Last
> authorised C')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',19,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('C',20,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',21,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',22,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',23,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',24,40,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',25,45,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',26,60,'Last
> authorised D')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',27,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',28,20,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comment])VALUES('D',29,30,NULL)
> PRINT 'Done'
>
> SET IDENTITY_INSERT [dbo].[THINGY_table] OFF
> GO
> SET NOCOUNT OFF
>
>
>|||Thanks for the very speedy response.
This is what I am looking for.
Still have to study the solution.
thanks for your attention
ben brugman
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:#YbUk3$4DHA.880@.TK2MSFTNGP10.phx.gbl...
> SELECT t1.setname, MAX(t1.ordernr)
> FROM THINGY_table t1
> WHERE t1.authorised >= 50
> AND (SELECT TOP 1 t2.authorised FROM THINGY_table t2
> WHERE t2.setname = t1.setname AND t2.ordernr < t1.ordernr
> ORDER BY t2.ordernr DESC) < 50
> GROUP BY t1.setname
> Thanks for the scripts btw, made life a lot easier.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eUsIin$4DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I am working on a problem, have not yet found 'the solution',
> > but am not stuck either. (So the work is still in progress).
> >
> > I am trying to solve the problem with 'set' operations.
> > (No cursors, no control statements, 'to keep it simple').
> >
> > 'THINGY' a set of rows spanning several tables. Each row
> > has an order (an identity field) and a status field.
> > I am looking for when the status field changed the last time
> > to 50 or above.
> > (Find the last time that 'THINGY' was authorized (status>=50))
> >
> > Examples :
> > Order of values in 'THINGY' (Order from left to right, status is the
> value)
> > A. 10 20 50(<ok) 60 70
> > B. 10 20 50(<ok) 50 50 60 70
> > C. ... 10 50 60 40 45 55(<ok) 60 70
> > D. ... 10 50 60 40 45 60(<ok) 70 20 30
> >
> > A and B a simple. (First value equal or above 50).
> > C (First value equal or above 50 not followed by a lower value).
> > D (Question Mark).
> >
> > As said I am not stuck yet, but the code is not getting prettier.
> > (Keep it simple is my motto).
> > Anybody an elegant set solution ? Or tips ?
> > Some DDL and data follows this message. (Thingy table and example data).
> >
> > Thanks for your time,
> > ben brugman.
> >
> > CREATE TABLE [dbo].[THINGY_table] (
> > [setname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ordernr] [int] IDENTITY (1, 1) NOT NULL ,
> > [authorised] [int] NULL ,
> > [comment] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> > --INSERTs generated by 'sp_generate_inserts' stored procedure written by
> > Vyas
> > --Build number: 22
> > --Problems/Suggestions? Contact Vyas @. vyaskn@.hotmail.com
> > --http://vyaskn.tripod.com
> >
> > SET NOCOUNT ON
> >
> > SET IDENTITY_INSERT [dbo].[THINGY_table] ON
> > GO
> >
> >
> > PRINT 'Inserting values into [THINGY_table]'
> >
> >
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',1,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',2,20,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',3,50,'Last
> authorised
> > A')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',4,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',5,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',6,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',7,20,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',8,50,'Last
> authorised
> > B')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',9,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',10,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',11,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',12,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',13,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',14,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',15,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',16,40,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',17,45,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',18,55,'Last
> > authorised C')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',19,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',20,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',21,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',22,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',23,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',24,40,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',25,45,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',26,60,'Last
> > authorised D')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',27,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',28,20,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',29,30,NULL)
> >
> > PRINT 'Done'
> >
> >
> > SET IDENTITY_INSERT [dbo].[THINGY_table] OFF
> > GO
> > SET NOCOUNT OFF
> >
> >
> >
> >
> >
>|||You haven't defined a primary key but I'll assume that (setname, ordernr) is
unique.
SELECT P.setname, MAX(P.ordernr) AS ordernr
FROM
(SELECT T1.setname, T1.ordernr, MAX(T2.ordernr) AS prev_ordernr
FROM Thingy_table AS T1
LEFT JOIN Thingy_table AS T2
ON T1.setname = T2.setname AND T1.ordernr > T2.ordernr
GROUP BY T1.setname, T1.ordernr
HAVING MAX(T1.authorised)>=50) AS P
JOIN Thingy_table AS T
ON T.ordernr = P.prev_ordernr AND T.authorised<50
GROUP BY P.setname
You may want to consider using a DATETIME column to define the sequence or
generate the sequential number in your application. It's generally not a
good idea to use IDENTITY as if it's a meaningful attribute because it could
cause you problems if you ever need to migrate data between tables or
otherwise insert multiple rows. IDENTITY should be used only for an
arbitrary surrogate key.
--
David Portas
SQL Server MVP
--|||Tried your solution.
I ommitted an example where the THINGY has
been authorised from the start.
But with your solution as a starting point,
that should not be a problem.
Thanks again,
ben brugman
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:#YbUk3$4DHA.880@.TK2MSFTNGP10.phx.gbl...
> SELECT t1.setname, MAX(t1.ordernr)
> FROM THINGY_table t1
> WHERE t1.authorised >= 50
> AND (SELECT TOP 1 t2.authorised FROM THINGY_table t2
> WHERE t2.setname = t1.setname AND t2.ordernr < t1.ordernr
> ORDER BY t2.ordernr DESC) < 50
> GROUP BY t1.setname
> Thanks for the scripts btw, made life a lot easier.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eUsIin$4DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I am working on a problem, have not yet found 'the solution',
> > but am not stuck either. (So the work is still in progress).
> >
> > I am trying to solve the problem with 'set' operations.
> > (No cursors, no control statements, 'to keep it simple').
> >
> > 'THINGY' a set of rows spanning several tables. Each row
> > has an order (an identity field) and a status field.
> > I am looking for when the status field changed the last time
> > to 50 or above.
> > (Find the last time that 'THINGY' was authorized (status>=50))
> >
> > Examples :
> > Order of values in 'THINGY' (Order from left to right, status is the
> value)
> > A. 10 20 50(<ok) 60 70
> > B. 10 20 50(<ok) 50 50 60 70
> > C. ... 10 50 60 40 45 55(<ok) 60 70
> > D. ... 10 50 60 40 45 60(<ok) 70 20 30
> >
> > A and B a simple. (First value equal or above 50).
> > C (First value equal or above 50 not followed by a lower value).
> > D (Question Mark).
> >
> > As said I am not stuck yet, but the code is not getting prettier.
> > (Keep it simple is my motto).
> > Anybody an elegant set solution ? Or tips ?
> > Some DDL and data follows this message. (Thingy table and example data).
> >
> > Thanks for your time,
> > ben brugman.
> >
> > CREATE TABLE [dbo].[THINGY_table] (
> > [setname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ordernr] [int] IDENTITY (1, 1) NOT NULL ,
> > [authorised] [int] NULL ,
> > [comment] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> > --INSERTs generated by 'sp_generate_inserts' stored procedure written by
> > Vyas
> > --Build number: 22
> > --Problems/Suggestions? Contact Vyas @. vyaskn@.hotmail.com
> > --http://vyaskn.tripod.com
> >
> > SET NOCOUNT ON
> >
> > SET IDENTITY_INSERT [dbo].[THINGY_table] ON
> > GO
> >
> >
> > PRINT 'Inserting values into [THINGY_table]'
> >
> >
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',1,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',2,20,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',3,50,'Last
> authorised
> > A')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',4,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('A',5,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',6,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',7,20,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',8,50,'Last
> authorised
> > B')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',9,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',10,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',11,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('B',12,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',13,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',14,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',15,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',16,40,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',17,45,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',18,55,'Last
> > authorised C')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',19,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('C',20,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',21,10,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',22,50,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',23,60,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',24,40,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',25,45,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',26,60,'Last
> > authorised D')
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',27,70,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',28,20,NULL)
> > INSERT INTO [THINGY_table]
> > ([setname],[ordernr],[authorised],[comment])VALUES('D',29,30,NULL)
> >
> > PRINT 'Done'
> >
> >
> > SET IDENTITY_INSERT [dbo].[THINGY_table] OFF
> > GO
> > SET NOCOUNT OFF
> >
> >
> >
> >
> >
>|||Thanks for your participation,
Thanks for the solution, and thanks for the thoughts
behind the design, this is greatly appriciated.
The table was constructed as an example and keys and
indexes where ommitted. The real tables have PK and
the identity is the PK or part of the PK.
As for datetime fields, there are several datatime fields, but
they are under control of the application. Although there is
a create datetime and some more datetime fields. The
identity in our situation is the most trustworthy field to
see in what order the rows where inserted.
(For business reasons date's can be modified. The tables actualy
keep a history of a registration.).
But I strongly agree with you that when using a 'meaningless'
field as an identity field to 'define' order in rows, that the
term 'meaningless' is wrong or something else is wrong.
Defining an identity field as an ordered field is something I
think should be avoided.
In the logical model it was not specified as ordered.
Now the model is changing, we are doing a conversion,
and use (not totaly appropriate) the identity field as an
ordered field during the conversion.
I will raise the question if the ordering is only used for
this one time conversion or that it is part of the model.
If it is part of the model, I think it should be written
'IN LARGE FRIENDLY LETTERS' in the logical model
description.
And within the implementation (databasemodel) we have
to take some precautions to keep it ordered if this
is required.
Thanks for your anwser,
as the other mail stated I did not supply an example
for if the states was allready authorised from the
moment the set was created. That was an ommision of
my part. But With your anwsers, I must be able to
include that.
Thanks again,
ben brugman
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9IKdnXcCjoqmYIndRVn-sA@.giganews.com...
> You haven't defined a primary key but I'll assume that (setname, ordernr)
is
> unique.
> SELECT P.setname, MAX(P.ordernr) AS ordernr
> FROM
> (SELECT T1.setname, T1.ordernr, MAX(T2.ordernr) AS prev_ordernr
> FROM Thingy_table AS T1
> LEFT JOIN Thingy_table AS T2
> ON T1.setname = T2.setname AND T1.ordernr > T2.ordernr
> GROUP BY T1.setname, T1.ordernr
> HAVING MAX(T1.authorised)>=50) AS P
> JOIN Thingy_table AS T
> ON T.ordernr = P.prev_ordernr AND T.authorised<50
> GROUP BY P.setname
> You may want to consider using a DATETIME column to define the sequence or
> generate the sequential number in your application. It's generally not a
> good idea to use IDENTITY as if it's a meaningful attribute because it
could
> cause you problems if you ever need to migrate data between tables or
> otherwise insert multiple rows. IDENTITY should be used only for an
> arbitrary surrogate key.
> --
> David Portas
> SQL Server MVP
> --
>