Finding mins and max time from records with different start time other that 12:00 midnight

I have a series of start and end time records. The problem is to select the min and max time from the series of records.

The following contraint applies. The start of broadcast time is 6:00 am. That is, minimum for start time is 6:00 am and maximum for end time is 5:59 am. So the following is illegal for start and end time, for example, 4:00 am - 6:30 am because it crosses the broadcast start time of 6:00 am.

Start End

10:00 pm -- 2:00 am

6:30 am -- 8:30 am

2:00 am - 3:45 am

11:00 am - 4:00pm

12:00 am - 3:40 am

You might be tempted to used -> Select MIN(Start), Max(End), but that will return 12:00 am - 4:00 pm, which is wrong, because sql server uses 12 midnight at the start time.

Can' t seem to come up with the tsql, please help

In my opinion it's not totally clear exactly what you're looking for.

In the example that you supplied, what results would you expect to be returned by the query? Also, are you storing the data as DATETIME values?

Thanks
Chris

|||Sorry. Since the min begin time is 6:00 am and the maximum end time time is 5:59 am, the expected result should be 6:30 am - 3:45am.|||

Thanks for providing the extra info.

Please could you also clarify what datatype you are using to store the times as this will affect the solution.

Thanks
Chris

|||

job:

I think that I also am not sure exactly what you are trying to do. Maybe a starting point is to substract six hours from your "startTime" to establish a "work day"; something like:

declare @.timeStuff table
( shiftId integer,
startTime datetime,
endTime datetime
)

insert into @.timeStuff values ( 1, '3/4/7 22:00', '3/5/7 2:00' )
insert into @.timeStuff values ( 2, '3/5/7 06:30', '3/5/7 8:30' )
insert into @.timeStuff values ( 3, '3/5/7 02:00', '3/5/7 3:45' )
insert into @.timeStuff values ( 4, '3/5/7 11:00', '3/5/7 16:00')
insert into @.timeStuff values ( 5, '3/6/7 00:00', '3/6/7 3:40')

declare @.offset datetime set @.offset = '06:00:00.000'

select convert (varchar(8), startTime - @.offset, 101) as [workDate ],
left(convert (varchar(8), startTime, 108), 5) as startTime,
left(convert (varchar(8), endTime, 108), 5) as endTime
from @.timestuff
order by startTime

-- workDate startTime endTime
-- -
-- 03/05/20 00:00 03:40
-- 03/04/20 02:00 03:45
-- 03/05/20 06:30 08:30
-- 03/05/20 11:00 16:00
-- 03/04/20 22:00 02:00

|||

If 4 am is not legal how did it get into your db in the first place, going by what you want there shoudl be nothing less than 6.00 am in your Start column. Get the place of entry sorted out and you should fine.

for the bad data u posted above you could still use

Select min(start), Max(End)

from tablename

where

convert(use conversion to convert time to last digits in time in start)>5.59 and

and convert(use conversion to convert time to last digits in time)<

--you can figure out the where condtion

point is it can be done

finding minimum value

How to find the minimum value from time Timestamp column ?

I want get the eralier timestamp vlaues from the avaliable list

when iam using Aggregate transformation ..its again giving all the list of vlaues

Thanks
Niru

If you have only one minimum per data set I would use the Script component to get it manually.

Thanks.

|||no i have lot of set of duplicate values with in the same column....I have to get the Minimum ones in those sets ..like

5
5
3
3

Desired result:

5
5

|||

I still do not understand your scenario.

If you have a data like this:

A B

1 1

1 2

2 7

2 8

You can use the Aggregate component to group on column A and find a minimum on B, so it would produce:

A B

1 1

2 7

Is that what you are looking for?

Finding jobs that wont run due to Daylight Savings Time?

Since this weekend is Daylight Savings, we want to make sure of any
jobs that won't run due to the "missing hour".

I have written a small query against sysjobschedules that tells me
which ones have a schedule that runs between 2 and 3. However, it
doesn't include any jobs that run every X minutes/hours, and so might
be run during that time. (i.e. a job that starts at midnight and runs
until 6, running every 2 hours)

Is there any (easy) way to determine this? I might be able to build a
function that uses the other fields in sysjobschedules to give a list
of times that the job will run, but I haven't gotten to that point yet.
Figured someone might have something already, rather than reinvent the
wheel.

Thanks,
MichaelI don't believe there's any easy way, but sysjobhistory might be a good
starting point - if a job ran between 2 and 3 in the past, it will
probably do so again. You could join on sysjobschedules to narrow it
down to daily schedules, schedules on a certain weekday or date etc.
And you can check the next_run_date column as well, of course.

Simon

finding first record ... !?

I have a table as follows:

- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)

An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:

- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)

- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)

For example:

- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL

SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)

- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE

Any ideas!?
Thanks!
Richard

richard @. rcbuchanan . comRichard,

Try something like

update Richard set
user_sent_date = getdate()
where user_id = (
select top 1 user_id from Richard R
where R.user_sent_date is null
order by user_request_date
)

Your narrative and your example don't agree on whether
you want the most recent (youngest), or least recent (oldest)
user_request_date, so you may need to add DESC to the
order by clause.

Steve Kass
Drew University

Richard C Buchanan wrote:

>I have a table as follows:
>- user_id (key)
>- user_email_address (text)
>- user_request_date (text)
>- user_sent_date (time/date)
>An automated macro will run every 5 minutes and needs to run a SQL UPDATE
>that:
>- finds the FIRST (ONE ROW ONLY) (youngest) record where
> - user_request_date is the most recent
> - AND user_sent_date is NULL (i.e. blank)
> - THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
>current system date/time)
>
>For example:
>- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
>- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
>- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
>- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
>- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL
>SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)
>- update record 33 - with NOW() time and date
>THEN record 34, then 35, then 36, then 37
>ONE ROW at a time per each SQL UPDATE
>
>Any ideas!?
>Thanks!
>Richard
>richard @. rcbuchanan . com
>

Finding date using PATINDEX in varchar

Hi:
One of the columns in my table is a notes column i.e everytime a user
updates it the date,time, users name and his/her note gets appended in
front. The notes column has text in following format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
I am supposed to filter out the data by each user,its date and time i.e
8/4/2006 Linda King Per MDCR no clm on file,reblld.
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
Any suggestions are welcomed.Hi
DECLARE @.st VARCHAR(100)
SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
SELECT
SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
<sfazili@.gmail.com> wrote in message
news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> Hi:
> One of the columns in my table is a notes column i.e everytime a user
> updates it the date,time, users name and his/her note gets appended in
> front. The notes column has text in following format:
> 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> I am supposed to filter out the data by each user,its date and time i.e
> 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> Any suggestions are welcomed.
>|||Hi Uri:
Thanks for ur reply. Actually the notes field has text in the follwoing
format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
Each update to the notes field just get appended to the field in front.
Firstly, I need to separate each individual update..cant use space as
an identifier... need help here. The thing is that after each
indiviaudal note is identified it should be in follwoing format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
After this need to separate each individaul note into thre columns:
8/4/2006 Linda King Per MDCR/ no clm on file,reblld
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22
3/30/2006 Maria James Patient asks to follow -MEDICARE
Any suggestions appreciated
Uri Dimant wrote:
> Hi
> DECLARE @.st VARCHAR(100)
> SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
> SELECT
> SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
> CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
> SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
>
>
> <sfazili@.gmail.com> wrote in message
> news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> > Hi:
> >
> > One of the columns in my table is a notes column i.e everytime a user
> > updates it the date,time, users name and his/her note gets appended in
> > front. The notes column has text in following format:
> >
> > 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> > 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> >
> > I am supposed to filter out the data by each user,its date and time i.e
> > 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> > 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> >
> > Any suggestions are welcomed.
> >

finding and removing duplicates rows

Say I had a table called 'RAS', with 4 columns:
Date, Time, UserName, GroupName
How can I check for any duplicate rows based on all those columns
combined.
And once they are found, can the duplicates be deleted?
It would be nice to see 2 solutions for learning purposes... one
statement just to show the duplicates, and another separate statement
to find and delete the duplicates.
Thanks in advance!
StephenCREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Stephen" <facetoface72@.hotmail.com> wrote in message
news:d6e21d67.0403160535.5ac3afa7@.posting.google.com...
> Say I had a table called 'RAS', with 4 columns:
> Date, Time, UserName, GroupName
> How can I check for any duplicate rows based on all those columns
> combined.
> And once they are found, can the duplicates be deleted?
> It would be nice to see 2 solutions for learning purposes... one
> statement just to show the duplicates, and another separate statement
> to find and delete the duplicates.
> Thanks in advance!
> Stephen|||Thanks for the message, it definately helped solve a lot of issues.
Is there a way to do everything mentioned, but without having a unique
key for each record?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#6uUEw1CEHA.1452@.TK2MSFTNGP09.phx.gb
l>...
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Stephen" <facetoface72@.hotmail.com> wrote in message
> news:d6e21d67.0403160535.5ac3afa7@.posting.google.com...|||On 17 Mar 2004 05:24:49 -0800, Stephen wrote:

>Thanks for the message, it definately helped solve a lot of issues.
>Is there a way to do everything mentioned, but without having a unique
>key for each record?
No. If all columns in two rows are the same, there's no way to tell
them apart. Any where clause will either select both rows or none of
them. If you want to eliminate duplicates, you have to add an IDENTITY
or GUID column, or else you will delete both rows instead of one of
the duplicates.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||DELETE *
FROM table3
WHERE sno not in (SELECT min(sno)
FROM table3
GROUP BY fd1, fd2, fd3);

Finding a user that has multiple log-ins at the same time

I have a table that contains the following

UserName - Which is unique to each user

TimeStart - Which is the time and date that the user logs in

TimeEnd - Which is the time and date that the user logs out

Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.

Try this query....

Select
MainQ.*
From
UserLog MainQ
Join
(
Select

Main.UserName,
Main.TimeStart,
Main.TimeEnd
From
UserLog Main
Join UserLog Started On Main.UserName = Started.UserName
And Main.TimeStart <= Started.TimeStart
And Main.TimeEnd >= Started.TimeStart

Group BY
Main.UserName,
Main.TimeStart,
Main.TimeEnd
Having Count(*) <> 1
) as Data
On MainQ.UserName = Data.UserName
And MainQ.TimeStart >= Data.TimeStart
And MainQ.TimeStart <= Data.TimeEnd

Find time interval between dates

Hi, all. I've got a classic ASP app that I've been tasked with modifying to
track users' IP addresses. If they fail to login, we want to record their IP
and the date/time when they failed. If they fail more than 5 times within 10
minutes, we want to lock them out for 5 minutes. Below are the two SQL
Server 2000 tables I've created.
The problem I'm having is this: How do you figure out if user has failed 5
times within the last 10 minutes. If you believe there's a better way to
design the tables for this task, please let me know. Thank you :)
CREATE TABLE [tblHits] (
[hitID] [int] IDENTITY (1, 1) NOT NULL ,
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
(
[hitID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
(
[IP]
) REFERENCES [tblIPs] (
[IP]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblIPs] (
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
(
[IP]
) ON [PRIMARY]
) ON [PRIMARY]
GOHi
smalldatetime data type has only the accuracy of 1 minute, so you might be
working between 9 and 11 minutes. The data type datetime might be more
appropriate.
Unless this is an intranet application, there is no guarantee that the IP
address could not be the source for multiple users. Corporates have large
proxy servers that do not expose the internal source IP. You might make a
lot of users unhappy if one of them fails to login correctly.
That aside. The following will return the number of hits in the last 10
minutes:
SELECT
COUNT(*)
FROM
tblHits
WHERE
dateAdded > DATEADD(mi, -10, GETDATE())
AND IP = @.YourCurrentIPAddress
Regards
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Hi
. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
>
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
you get the idea and an modify the query.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Thank you, Michael and Uri. You've got some very creative solutions. By the
way, the point about public computers or proxy servers is a valid one; we
discussed it, but the powers-that-be wanted to go ahead and do this anyway.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhYgKlbIGHA.3144@.TK2MSFTNGP10.phx.gbl...
> Hi
> . If you believe there's a better way to
>
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
> you get the idea and an modify the query.
>
>
> "curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
> news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
>

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 phonecall records based on user at the time of call

i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:

PhoneUsers table

PhoneUserID Name StartDate PhoneID

1 john 1/08/2006 1

2 bob 20/08/2006 1

3 fred 2/08/2006 2

etc

Phones table:

PhoneID

1

2

etc

PhoneCalls table:

PhoneCallID PhoneID CallDate Duration

1 1 10/08/2006 25

2 1 23/082006 20

3 2 23/08/2006 20

i want the following result set:

user calldate duration

john 10/08/2006 25

bob 23/08/2006 20

fred 23/08/2006 20

essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:

the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date

thanks

This should be close:

select

U1.Name as [user],

C.CallDate as calldate,

C.Duration as duration

from PhoneCalls as C

left outer join

PhoneUsers as U1

on U1.PhoneID = C.PhoneID

and U1.StartDate <= C.CallDate

and not exists (

select * from PhoneUsers as U2

where U2.PhoneID = U1.PhoneID

and U2.StartDate > U1.StartDate

and U2.StartDate <= C.CallDate

)

Steve Kass

Drew University

http://www.stevekass.com

milo68@.discussions.microsoft.com wrote:

> i have a table of phonecall records detailing amongst other things the

> time and duration of the call. there is a relationship linking this

> table to a table of handsets and from there to a table of users. this

> users table lists the start date the user had the phone. as ever sample

> data might make this easier to show:

>

> PhoneUsers table

>

> PhoneUserID Name StartDate PhoneID

>

> 1 john 1/08/2006 1

>

> 2 bob 20/08/2006 1

>

> 3 fred 2/08/2006 2

>

> etc

>

> Phones table:

>

> PhoneID

>

> 1

>

> 2

>

> etc

>

> PhoneCalls table:

>

> PhoneCallID PhoneID CallDate Duration

>

> 1 1 10/08/2006 25

>

> 2 1 23/082006 20

>

> 3 2 23/08/2006 20

>

> i want the following result set:

>

> user calldate duration

>

> john 10/08/2006 25

>

> bob 23/08/2006 20

>

> fred 23/08/2006 20

>

> essentially i am trying to link the phonecalls to the user of the phone

> at the time of the call. i make the following assumptions regarding the

> data:

>

> the user is responsible for all calls for a given handset from the

> 'startdate' until the next 'startdate' for a different user, if such a

> record exists, if not then for all calls after that date

>

>

>

> thanks

>

>

>

>

>

>

Find People Names in Long Text

Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:

Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles inarticle table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)

Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...

Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?

Thanks once again for taking the time.

Cheers,

Tyro

I think that the best way is stored procedure on SQL server side. The question is

Would you like to do it for single article or for all articles in your database at one run?

If for all names in all articles you will kill you server.

Thanks

|||

Thanks for your reply Jpazgier,

Killing the server is what I am worried about, plus the fact that the people's table is highly likely to grow in the future. I was thinking SQL too. doing some sort of lookup and compare, but think that would be very resource intensive.

I was thinking along the lines of probably being able to compare two XML files ... ?, writing the XML files overnight and then comparing with article text before saving the article in db ... just an idea ... what do you think? please feel free to correct me ..

Tyro

|||

I would create stored procedure which will grabperson namefrom your peopletable and will look for each components of this person name in article field, if it will find all components in article I would just report this person as existing inside article and report its ID. The question is you would like to report the person exists in article if one component of his name is in it or all elements should exists in article to report person as connected to it.

I would run it one time for all articles and next every time you add new person or new article, so maybe you need two or three procedures one for searching multiple article with one person , second to search single article with multiple persons and maybe 3rd for startup and maintenance to search all articles with all persons which are currently in database.

I would store results in table with 3 columns ID, articleID and personID so will be easy to insert results and manipulate it in the future.

Thanks

|||

Thanks for all your replies and helping me out with this Jpazgier, Appreciate it. Hopefully what you said should work well.

Tyro

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!
Maybe these will help:
http://msdn.microsoft.com/library/de...tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!Maybe these will help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!Maybe these will help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

Find number of business days in a month

Hi,

I need to find out the number of business days in a month. I have a column 'IsWorkingDay' in my [Time] table. Can anyone please let me know how to find out number of business days in a given month using MDX query.

Thanks.

The best way to solve this problem is to create a calculated column in DSV which will compute number of working days in a month, and then create attribute related to Month called WorkingDaysCount.

Then in MDX it will look like

WITH MEMBER Measures.X AS Date.WorkingDaysCount.CurrentMember.MemberValue

SELECT X ON 0

, Date.Month.MEMBERS ON 1

From cube

find last record message

I have a part software package and I would like to get a message from SQL
sent to the user. I want the SQL message to be sent every time the user
creates a new part. The message will say the last part number is "X", X
beiing the last partnumber added in the part table, there is a field in the
table for date created.
Any idea on how to do that ?, is trigger be used ?Throwing a message back to the user from a trigger is an ugly hack. If the
part is being inserted via a stored procedure and the part number is an auto
incremented identity value, then just return SCOPE_IDENTITY() using an
output parameter.
"FRED" <FRED@.discussions.microsoft.com> wrote in message
news:FFDA08E6-8031-4099-9903-75515AE4AC7A@.microsoft.com...
> I have a part software package and I would like to get a message from SQL
> sent to the user. I want the SQL message to be sent every time the user
> creates a new part. The message will say the last part number is "X", X
> beiing the last partnumber added in the part table, there is a field in
the
> table for date created.
> Any idea on how to do that ?, is trigger be used ?
>

Find expiration date for Evaluation Edition

Is there any way I can check the remaining time before I must upgrade
an SQL Server 2000 Evaluation Edition? I thought I had made a note about
which day it was.
Can I rely upon the file date for uninstallation file in Programs/etc ?You can be sure that the installation is NEVER later than that date, and it is rarely earlier either. I wouldn't cut it that close though... Once you know that you've got a "go" on the purchase, I'd buy it and install it just so I didn't need to think about it anymore.

-PatP|||Thanks.

I'll probably start with 2005 Express Edition soon.

Find Current Date Time Span SQL

Any body have an Idea on this... Last problem of my Calendar Application.

To keep my calendar loading fast and efficent I have a SP that loads data to a datatable to read from c#.

My sp reads this

(
@.ID int,
@.DateStart datetime,
@.DateEnd datetime
)
AS

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND JobDateStart Between @.DateStart AND @.DateEnd + 1
OR JobDateEnd Between @.DateStart AND @.DateEnd + 1)

Now My problem is that the DateStart and DateEnd values are used to pull information from a DB. So lets say that I have a job that starts on November 30th and ends on January 3rd.
My calendar will display the record on the 30th day of Nov and on the 1st through 3rd fo Jan, however when I look at December there is NO DATA there. which is the expected result because the dates do not fall within the start and ends dates.

My question is how query the database to pull back my month spanding record without pulling back all the data in the database for performace?

My thought is something like the date span falls into the range between the start and end, but I cannot find anything????? Please help, I know it is probaly simple right??Found the answer, just took THINKING LESS rather than more..

add and OR statement like this, it will get the 1 day events and current month event and the spanning events.

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND
/*FIX SPANNING MONTHS / YEAR JOBS*/
@.DateStart BETWEEN JobDateStart AND JobDateEnd +1
/*SELECT 1 DAY OR 1 MONTH JOBS*/
OR JobDateStart Between @.DateStart AND @.DateEnd + 1)

It really was that easy. Less load on the db so I can loop through the DataTable and do my sorting with no effect on the DB.

Find blocking process

Hi,
we're having a problem with SQL 2000 and Opta 2000 JDBC driver
where there is large update running and at the same time,
read is blocked for a while.
We're looking for a way to catch this blocking process
and if it last more than 10 minutes, then email or send out a message.
I know sp_lock returns all current locks
but how do you know which one is blocking other processes?

Thanks for your help in advance.neo (second714@.hotmail.com) writes:
> we're having a problem with SQL 2000 and Opta 2000 JDBC driver
> where there is large update running and at the same time,
> read is blocked for a while.
> We're looking for a way to catch this blocking process
> and if it last more than 10 minutes, then email or send out a message.
> I know sp_lock returns all current locks
> but how do you know which one is blocking other processes?

The simplest way is to use sp_who. If a process is blocked, you will
see a non-zero value in the Blk column. This is the spid of the blocker.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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