finding hidden chars in sql query

I am working on a login system in flex and asp. I am encrypting the password before it is inserted it into the SQL database. But then when i do SELECT statement with username and encrypted password it returns 0 users found.
I outputted the sql statement and they passwords look excatly the same. But the SQL Select count(*) returns a 0.

link to the encrypting is used:
http://www.4guysfromrolla.com/webtech/110599-1.2.shtml

other information:
script language: asp + flex
database: sql server 2005

So i am thinking that their are hidden chars in the password. Is their a way to check this or even convert/exclude them ?
Any links or tips would be very helpfull

Use the sample to solve your issue...

Alter Function dbo.En_De_Crypt(@.Input varchar(max), @.Key int ) Returns Varchar(Max) as

Begin

Declare @.Len as Int;

Declare @.I as Int;

Declare @.Output as Varchar(max)

Select @.Len = Len(@.Input), @.I =1, @.Output=''

Declare @.Number Table (N int);

While(@.I<=@.Len)

Begin

Insert into @.Number Values(@.I);

Set @.I = @.I + 1;

End

Select @.Output = @.Output + Char(Ascii(Substring(@.Input,N,1)) ^ @.Key)

From @.Number

return @.Output

End

Go

Create Table #Passwords(

Password varchar(20)

);

Insert Into #Passwords Values(dbo.En_De_Crypt('One1234$$',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('M1cr0$0ft',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('Or@.c1e',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('@.pp1e',100));

Select dbo.En_De_Crypt(Password,100) ,Password From #Passwords

/*

Orginal value Decrypted Value

One1234$$ + _VWP@.@.

M1cr0$0ft )Up'16T@.T '10

Or@.c1e +_$p_par

@.pp1e $__U_par */

--None of the query will return the data here..

Select Count(*) From #Passwords Where Password = '+

_VWP@.@.'

Select Count(*) From #Passwords Where Password = ')Up'16T@.T '10'

Select Count(*) From #Passwords Where Password = '?_p$'

Select Count(*) From #Passwords Where Password = '$__U_par'

--Use the following query to get the result..

Select Count(*) From #Passwords Where dbo.En_De_Crypt(Password,100)='M1cr0$0ft'

|||thnx Manivannan for the reply.
i will try it out.

Finding Books for SQL SERVER Replication

Hi,
I am new in this dbforums. i working on SQL SERVER REPLICATION. I need some books related to only SQL SERVER REPLICATION. If any body konwn then reply meHave you alredy checked out BOL, 'cause there is lots of information to be found here!sql

Find/Replace Not Working in Management Console

I have the 2005 Standard Edition of Sql Server, and the managment console tonight stopped responding to quick find/replace requests either via menu or (ctrl+f, ctrl+h).

The UI bars above just grey out a bit and nothing happens. I saw a MSFT dev for VB (visual studio) respond with a fix that was to run "devenv.exe /resetsettings" that restored the defaults and fixed the problem for those affected.

Anyone know how to do that for management studio, since going to customize (restore) each of the individual bars didn't do it.

Thanks in advance,

Greg

Greg,

Please make sure that Find & Replace window is not docked (and possibly hidden) somewhere along the edges of Management Studio. It sometimes happens that a tool window gets docked and then slides to a hidden position so only it's caption bar is visible.

Regards,

|||

Maciek,

Thanks for the reply. I read this and said 'bah, no way'....

Well, there it was, the caption hiding like 1 pixel above the status bar...exposed if i made it temporarily auto-hide.

Thanks for this, again. One comment, though. I could select the window with ctrl+H...a nice way out of this would be an addition in the window menu bar to force it as floating center screen to get ahold of it again.

Greg

|||

Greg,

Glad I could help. We've seen this happening to number of people for some reason. I guess you are right - there should be an easy way to find that window. I'll open a suggestion for that.

Regards,

|||Did an easy way ever get implemented? I have the same problem and I can't find the tool window after moving everything around. I've noticed that the main menu changes when I attempt ctrl-F, the 'Query' between 'View' and 'Project' disappears.|||

I am not sure if a shortcut to center the windows was ever added. However, while the problem keeps happening to me I've become much more adept at finding that edge of the window hiding at a corner.

Drop the status bar, peer at the edges of the screen, and I find it every time...just drag it back to the middle.

Greg M

|||

I found it thanks to your hint though not quite in the way you describe. In fact I found a way of making the Find dialogue pop up centered. What I did was go to the "Task bar and Start Menu" properties window (right click on start menu). Then uncheck "autohide the task bar" and popup appeared in all its glory. Amazing. I had uninstalled and reinstalled and the problem persisted - cost hours! One thing it taught me though was how important find/replace functionality is to my development practice. I turn old code into new code using it.

|||

manay thanks

i had the same porblem

and i found the pop up under the task bar

many many thanks

Find/Replace Not Working in Management Console

I have the 2005 Standard Edition of Sql Server, and the managment console tonight stopped responding to quick find/replace requests either via menu or (ctrl+f, ctrl+h).

The UI bars above just grey out a bit and nothing happens. I saw a MSFT dev for VB (visual studio) respond with a fix that was to run "devenv.exe /resetsettings" that restored the defaults and fixed the problem for those affected.

Anyone know how to do that for management studio, since going to customize (restore) each of the individual bars didn't do it.

Thanks in advance,

Greg

Greg,

Please make sure that Find & Replace window is not docked (and possibly hidden) somewhere along the edges of Management Studio. It sometimes happens that a tool window gets docked and then slides to a hidden position so only it's caption bar is visible.

Regards,

|||

Maciek,

Thanks for the reply. I read this and said 'bah, no way'....

Well, there it was, the caption hiding like 1 pixel above the status bar...exposed if i made it temporarily auto-hide.

Thanks for this, again. One comment, though. I could select the window with ctrl+H...a nice way out of this would be an addition in the window menu bar to force it as floating center screen to get ahold of it again.

Greg

|||

Greg,

Glad I could help. We've seen this happening to number of people for some reason. I guess you are right - there should be an easy way to find that window. I'll open a suggestion for that.

Regards,

|||Did an easy way ever get implemented? I have the same problem and I can't find the tool window after moving everything around. I've noticed that the main menu changes when I attempt ctrl-F, the 'Query' between 'View' and 'Project' disappears.|||

I am not sure if a shortcut to center the windows was ever added. However, while the problem keeps happening to me I've become much more adept at finding that edge of the window hiding at a corner.

Drop the status bar, peer at the edges of the screen, and I find it every time...just drag it back to the middle.

Greg M

|||

I found it thanks to your hint though not quite in the way you describe. In fact I found a way of making the Find dialogue pop up centered. What I did was go to the "Task bar and Start Menu" properties window (right click on start menu). Then uncheck "autohide the task bar" and popup appeared in all its glory. Amazing. I had uninstalled and reinstalled and the problem persisted - cost hours! One thing it taught me though was how important find/replace functionality is to my development practice. I turn old code into new code using it.

|||

manay thanks

i had the same porblem

and i found the pop up under the task bar

many many thanks

Find uniques on part of a multi-column PK (with where clause)

Hello all,
Follows a simplified DDL of the table where i'm working on:
CREATE TABLE Test
(
TestID bigint NOT NULL ,
SubTestID bigint NOT NULL ,
Name varchar (50),
Master bigint NOT NULL ,
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestID,
SubTestID
)
)
What I'm trying to achieve is to collect and retrieve all rows of which
Master differs from the value 2 and TestID is unique.
So far, the best I achieved was:
SELECT
counter.TestID
FROM
(
SELECT
TestID,
COUNT(TestID) AS Total
FROM
Test
WHERE Master <> 2
GROUP BY
TestID
) as counter
WHERE counter.Total = 1
However this strategy doesn't suit me since I need the TestID/SubTestID
pair to be returned. Needless to say, introducing SubTestID in the
above query will invalidate de aggregate function.
Can someone please help?Mario
Needless to say, that posting sample data + expected result will help to
bring a right solution. No offence.
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Lazy solution:
SELECT TestID, MAX(SubTestID ) AS SubTestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1
More structured solution:
SELECT t1.TestID, t1.SubTestID
FROM Test t1
INNER JOIN (
SELECT TestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1) a
ON t1.TestID = a.TestID
Jacco Schalkwijk
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Try,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
COUNT(distinct TestID) = 1
) as a
on t.TestID = a.TestID
go
AMB
"Mario F." wrote:

> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||One method:
SELECT
a.TestID,
a.SubTestID
FROM Test a
JOIN
(
SELECT
TestID
FROM Test
WHERE Master <> 2
GROUP BY
TestID
HAVING COUNT(*) = 1
) AS counter ON counter.TestID = a.TestID
Hope this helps.
Dan Guzman
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Correction,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
min(SubTestID) = max(SubTestID)
) as a
on t.TestID = a.TestID
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> t.*
> from
> test as t
> inner join
> (
> SELECT
> TestID
> FROM
> Test
> WHERE
> Master <> 2
> GROUP BY
> TestID
> having
> COUNT(distinct TestID) = 1
> ) as a
> on t.TestID = a.TestID
> go
>
> AMB
> "Mario F." wrote:
>|||The Having clause. Of course. Thank you everyone.
Mario F.
*** Sent via Developersdex http://www.examnotes.net ***sql

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

---

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

find out the instanceName

If after installing and working with MSDE a long while ago I forget the
instance Name in my local machine, what could I do to find it out again?
hi,
ykffc wrote:
> If after installing and working with MSDE a long while ago I forget
> the instance Name in my local machine, what could I do to find it out
> again?
for a quick view, you can rune oSql.exe with the -L parameter (case
sensitive), to list all available instances like
c:\...>oslq.exe -L
but you can inspect the file system as well.. in the \Program
Files\Microsoft SQL Server\ folder you will have all the installed one, if
you did not provide an alternate location at install time.. you'll have
\MSSQL\ for a default instance, and \MSSQL$InstanceName\ for a named
instance..
another one is inspecting the Windows registry... in the
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\
InstalledInstaces = ...
key you'll find all locally installed instances..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply

Find 2nd Working Day

Hi.

I utilize the Calendar table, and I'm able to find how many working
days between 2 dates, but does anyone use this table to find the 2nd or
5th working date?

if 11/30/06 then 12/4/06

I'm sure it's not too difficult but i can't seem to get it to work

(select caldate from calendar where...?...and workingday = 'Y')

thanks,
PaulOn 30 Nov 2006 05:34:29 -0800, paulmac106@.gmail.com wrote:

Quote:

Originally Posted by

>Hi.
>
>I utilize the Calendar table, and I'm able to find how many working
>days between 2 dates, but does anyone use this table to find the 2nd or
>5th working date?
>
>if 11/30/06 then 12/4/06
>
>I'm sure it's not too difficult but i can't seem to get it to work
>
>(select caldate from calendar where...?...and workingday = 'Y')

Hi Paul,

Basics first: here's a query to get the next working day after @.StartDt.

SELECT TOP (1) TheDate
FROM Calendar
WHERE TheDate @.StartDt
AND WorkingDay = 'Y'
ORDER BY TheDate;

Unfortunately, we need to add a bit more complexity for the second
business day: first, we get the TWO next business days, then pick the
last of them:

SELECT TOP (1) TheDate
FROM (SELECT TOP (2) TheDate
FROM Calendar
WHERE TheDate @.StartDt
AND WorkingDay = 'Y'
ORDER BY TheDate) AS d
ORDER BY TheDate DESC;

This can be easily adapted to get the third, fourth, etc. working day:
just replace TOP (2) with TOP (3), TOP (4), etc.

Note 1: If on SQL Server 2000, replace TOP (1) and TOP (2) with TOP 1
and TOP 2.

Note 2: If on SQL Server 2005, you may also use TOP (@.NumOfDays) to make
the number of business days to go forward variable.

--
Hugo Kornelis, SQL Server MVP|||Worked perfectly!

thanks Hugo, I really appreciate it.|||Worked perfectly!

thanks Hugo, I really appreciate it.|||(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

I utilize the Calendar table, and I'm able to find how many working
days between 2 dates, but does anyone use this table to find the 2nd or
5th working date?
>
if 11/30/06 then 12/4/06
>
I'm sure it's not too difficult but i can't seem to get it to work
>
(select caldate from calendar where...?...and workingday = 'Y')

If this is a common operation, adding a business-dayno column to the
table can be a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Add a julianized business column to your table and the math is very
simple. Look at the current day's Julianized number, subtract and
return the MIN(cal_date) with that julian_business_day value.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
julian_business_day INTEGER NOT NULL,
..);

etc.
INSERT INTO Calendar VALUES ('2006-12-01', 10); -- fri
INSERT INTO Calendar VALUES ('2006-12-02, 10); -- sat
INSERT INTO Calendar VALUES ('2006-12-01', 10); -- sun
INSERT INTO Calendar VALUES ('2006-12-01', 11); -- mon
INSERT INTO Calendar VALUES ('2006-12-01', 12); -- tue
etc.

Finally Got it to Work

I finally got it to work. Simple when you get it right. The working query is
below. I had been trying to use "NOT EXISTS" and I never did get that to
work? What is the difference between "Exists" and "IN"?
SELECT Distinct b.MemberName, b.MemberID
FROM Members AS b
WHERE b.MemberId NOT In
(SELECT Distinct MemberID
FROM EventRegs AS e)
Order By b.MemberName
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I am using SQLServer 2000. I have one table (Members) which includes a
>MemberID (int) field. I have another table (EventRegs) with a unique
>EventID(int) and a foreign key for MemberID. A given MemberID can occur in
>more than one row in the EventRegs table. I want to find out which MemberID
>values that exist in the Members table that do not occur in any of the rows
>in the EventRegs table. Basically, which members are not attending any
>events.
> I always get tangled up when I try to do this type of query! Can anyone
> give me an example to work from?
> Wayne
>Wayne
> SELECT Distinct b.MemberName, b.MemberID
> FROM Members AS b
> WHERE b.MemberId NOT In
> (SELECT Distinct MemberID
> FROM EventRegs AS e)
> Order By b.MemberName
Using the above SELECT you can get a wrong output. What is a MemberID in
EventRegs is NULL ( at least one row)
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OOYG4ANzFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I finally got it to work. Simple when you get it right. The working query
>is below. I had been trying to use "NOT EXISTS" and I never did get that to
>work? What is the difference between "Exists" and "IN"?
> SELECT Distinct b.MemberName, b.MemberID
> FROM Members AS b
> WHERE b.MemberId NOT In
> (SELECT Distinct MemberID
> FROM EventRegs AS e)
> Order By b.MemberName
> Wayne
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>|||Good point Uri but in this particular case, the MemberID can never be null.
Wayne
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23m86yHNzFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Wayne
> Using the above SELECT you can get a wrong output. What is a MemberID in
> EventRegs is NULL ( at least one row)
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:OOYG4ANzFHA.1252@.TK2MSFTNGP09.phx.gbl...
>