Showing posts with label stuck. Show all posts
Showing posts with label stuck. Show all posts

Wednesday, March 28, 2012

Finding Consecutive Values

I'm stuck in trying to find out how to solve this.
Within a record set, I need to know when a id has been a certain value
for x consecutive years. For example,
Create table #Test (
tid int NOT NULL,
yr int NOT NULL,
value int NOT NULL)
insert into #Test values (100, 1998, 0)
insert into #Test values (100, 1999, 0)
insert into #Test values (100, 2000, 0)
insert into #Test values (100, 2001, 0)
insert into #Test values (100, 2002, 1)
insert into #Test values (100, 2003, 0)
insert into #Test values (100, 2004, 0)
insert into #Test values (100, 2005, 0)
insert into #Test values (100, 2006, 0)
insert into #Test values (100, 2007, 0)
insert into #Test values (100, 2008, 1)
insert into #Test values (200, 1999, 0)
insert into #Test values (200, 2001, 0)
insert into #Test values (200, 2002, 0)
insert into #Test values (300, 2001, 0)
insert into #Test values (300, 2002, 0)
insert into #Test values (300, 2003, 0)
insert into #Test values (300, 2004, 0)
insert into #Test values (300, 2005, 0)
FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
months.
RESULTS would yield
tid startdate enddate years
100 1998 2001 4
100 2003 2007 5
300 2001 2005 5
TIAselect t_from.tid, t_from.yr start_year, t_to.yr end_year, t_from.value
from
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr+1))
)t_from
join
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr-1))
)t_to
on t_from.tid=t_to.tid and t_from.value=t_to.value and
(t_from.yr+3)<=t_to.yr
and t_from.yr + (select count(*) from Test t where t_from.tid=t.tid
and t_from.yr<t.yr and t.yr<t_to.yr and t_from.value=t.value) + 1 =
t_to.yr
order by t_from.tid, t_from.yr
tid start_year end_year value
-- -- -- --
100 1998 2001 0
100 2003 2007 0
300 2001 2005 0
(3 row(s) affected)|||select tid, min(yr) as start, maxyr as [end], count(*) as yrs
from (
select *,
isnull(
(select max(yr)
from #test t2
where t2.tid=t1.tid
and t2.yr>=t1.yr
and t2.value=0
and t2.yr<=(select min(yr) from #test where tid=t2.tid and
yr>=t1.yr and value=1)
),
(select max(yr)
from #test t3
where t3.tid=t1.tid
)
) as maxyr
from #test t1
where value=0
) t4
group by tid, maxyr
having count(*)>=4
order by tid, start
carmaboy@.gmail.com wrote:
> I'm stuck in trying to find out how to solve this.
> Within a record set, I need to know when a id has been a certain value
> for x consecutive years. For example,
> Create table #Test (
> tid int NOT NULL,
> yr int NOT NULL,
> value int NOT NULL)
> insert into #Test values (100, 1998, 0)
> insert into #Test values (100, 1999, 0)
> insert into #Test values (100, 2000, 0)
> insert into #Test values (100, 2001, 0)
> insert into #Test values (100, 2002, 1)
> insert into #Test values (100, 2003, 0)
> insert into #Test values (100, 2004, 0)
> insert into #Test values (100, 2005, 0)
> insert into #Test values (100, 2006, 0)
> insert into #Test values (100, 2007, 0)
> insert into #Test values (100, 2008, 1)
> insert into #Test values (200, 1999, 0)
> insert into #Test values (200, 2001, 0)
> insert into #Test values (200, 2002, 0)
> insert into #Test values (300, 2001, 0)
> insert into #Test values (300, 2002, 0)
> insert into #Test values (300, 2003, 0)
> insert into #Test values (300, 2004, 0)
> insert into #Test values (300, 2005, 0)
>
> FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
> months.
> RESULTS would yield
> tid startdate enddate years
> 100 1998 2001 4
> 100 2003 2007 5
> 300 2001 2005 5
> TIA
>|||A different, shorter version:
DROP TABLE Foobar;
CREATE TABLE Foobar
(tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL);
INSERT INTO Foobar VALUES (100, 1998, 0);
INSERT INTO Foobar VALUES (100, 1999, 0);
INSERT INTO Foobar VALUES (100, 2000, 0);
INSERT INTO Foobar VALUES (100, 2001, 0);
INSERT INTO Foobar VALUES (100, 2002, 1);
INSERT INTO Foobar VALUES (100, 2003, 0);
INSERT INTO Foobar VALUES (100, 2004, 0);
INSERT INTO Foobar VALUES (100, 2005, 0);
INSERT INTO Foobar VALUES (100, 2006, 0);
INSERT INTO Foobar VALUES (100, 2007, 0);
INSERT INTO Foobar VALUES (100, 2008, 1);
INSERT INTO Foobar VALUES (200, 1999, 0);
INSERT INTO Foobar VALUES (200, 2001, 0);
INSERT INTO Foobar VALUES (200, 2002, 0);
INSERT INTO Foobar VALUES (300, 2001, 0);
INSERT INTO Foobar VALUES (300, 2002, 0);
INSERT INTO Foobar VALUES (300, 2003, 0);
INSERT INTO Foobar VALUES (300, 2004, 0);
INSERT INTO Foobar VALUES (300, 2005, 0);
SELECT X.tid, MIN(X.yr), X.end_yr, (X.end_yr - MIN(X.yr)) AS duration
FROM
(SELECT F1.tid, F1.yr, MAX(F2.yr) AS end_yr
FROM Foobar AS F1, Foobar AS F2
WHERE F1.yr < F2.yr
AND F1.tid = F2.tid
AND 0
= ALL (SELECT value
FROM Foobar AS F3
WHERE F1.tid = F3.tid
AND F3.yr BETWEEN F1.yr AND F2.yr)
AND (F2.yr - F1.yr +1)
= (SELECT COUNT(*)
FROM Foobar AS F4
WHERE F1.tid = F4.tid
AND F4.yr BETWEEN F1.yr AND F2.yr)
GROUP BY F1.tid, F1.yr) AS X(tid, yr, end_yr)
GROUP BY X.tid, X.end_yr
HAVING (X.end_yr - MIN(X.yr)) > 1;|||A thoughful solution that leaves one to ponder the rationality
of the methodology and of the environment that spawned it :)
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e5k25kf9FHA.2844@.TK2MSFTNGP10.phx.gbl...
> select tid, min(yr) as start, maxyr as [end], count(*) as yrs
> from (
> select *,
> isnull(
> (select max(yr)
> from #test t2
> where t2.tid=t1.tid
> and t2.yr>=t1.yr
> and t2.value=0
> and t2.yr<=(select min(yr) from #test where tid=t2.tid and
> yr>=t1.yr and value=1)
> ),
> (select max(yr)
> from #test t3
> where t3.tid=t1.tid
> )
> ) as maxyr
> from #test t1
> where value=0
> ) t4
> group by tid, maxyr
> having count(*)>=4
> order by tid, start
>
> carmaboy@.gmail.com wrote:|||this problem would be solved like that? ;)
CREATE TABLE Puzzles..YearVals(
tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL,
PRIMARY KEY (tid, yr));
INSERT INTO Puzzles..YearVals VALUES (100, 1998, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 1999, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2000, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2002, 1);
INSERT INTO Puzzles..YearVals VALUES (100, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2005, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2006, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2007, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2008, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 1999, 0);
--INSERT INTO Puzzles..YearVals VALUES (200, 2000, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (200, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2005, 0);
SELECT tid, MIN(yr) AS beg_yr, MAX(yr) AS fin_yr, COUNT(*) AS tally
FROM
(SELECT B.tid,B.yr,B.yr-COUNT(*)
FROM Puzzles..YearVals AS B
JOIN Puzzles..YearVals AS L
ON L.tid = B.tid AND L.yr <= B.yr AND L.value=B.value
WHERE B.value = 0
GROUP BY B.tid,B.yr) AS G(tid,yr,grp)
GROUP BY grp,tid
HAVING COUNT(*) >= 4|||rrr... i did not remove a reference on my local db
sorry|||Thanks to everyone for taking the time to help. I should have all I
need to get my results.
Posted by contentvbny at 12:09 AM 0 comments
Labels: , , , , , , , , , , , ,

Wednesday, March 21, 2012

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],[comme
nt])VALUES('A',1,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('A',2,20,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('A',3,50,'Last authorised
A')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('A',4,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('A',5,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',6,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',7,20,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',8,50,'Last authorised
B')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',9,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',10,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',11,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('B',12,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',13,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',14,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',15,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',16,40,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',17,45,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',18,55,'Last
authorised C')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',19,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('C',20,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',21,10,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',22,50,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',23,60,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',24,40,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',25,45,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',26,60,'Last
authorised D')
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',27,70,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])VALUES('D',28,20,NULL)
INSERT INTO [THINGY_table]
([setname],[ordernr],[authorised],[comme
nt])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...
quote:

> 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)
quote:

> 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],[comme
nt])VALUES('A',1,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('A',2,20,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('A',3,50,'Last

authorised
quote:

> A')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('A',4,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('A',5,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',6,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',7,20,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',8,50,'Last

authorised
quote:

> B')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',9,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',10,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',11,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('B',12,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',13,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',14,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',15,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',16,40,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',17,45,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',18,55,'Last
> authorised C')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',19,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('C',20,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',21,10,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',22,50,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',23,60,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',24,40,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',25,45,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',26,60,'Last
> authorised D')
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',27,70,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])VALUES('D',28,20,NULL)
> INSERT INTO [THINGY_table]
> ([setname],[ordernr],[authorised],[comme
nt])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...
quote:

> 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...
> value)
> authorised
> authorised
>
|||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...
quote:

> 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...
> value)
> authorised
> authorised
>
|||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...
quote:

> You haven't defined a primary key but I'll assume that (setname, ordernr)

is
quote:

> 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
quote:

> 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
> --
>
Posted by contentvbny at 3:46 AM 0 comments
Labels: , , , , , , , , , , ,

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
> --
>
Posted by contentvbny at 3:45 AM 0 comments
Labels: , , , , , , , , , , ,
Subscribe to: Posts (Atom)