How to do the following sql query

[Steve]

How to do the following using sql statements?
I would like to be able to have the following table
Everytime I insert a new record the AutoIncrementKey field will increase by one
but I want the PriKey to be in the order of the datetime as shown below.
INSERT TABLE DateTime = '1/1/2001 12:00:00'
INSERT TABLE DateTime = '31/1/2001 8:00:00'
INSERT TABLE DateTime = '18/1/2001 4:00:00'
INSERT TABLE DateTime = '21/1/2001 3:00:00'
............
...........
DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 4
18/1/2001 4:00:00 3 2
21/1/2001 3:00:00 4 3

How to do this?

[David Browne]

"Steve" <ng*****@my-deja.com> wrote in message
news:97**************************@posting.google.c om...

How to do the following using sql statements?
I would like to be able to have the following table
Everytime I insert a new record the AutoIncrementKey field will increase by one but I want the PriKey to be in the order of the datetime as shown below.
INSERT TABLE DateTime = '1/1/2001 12:00:00'
INSERT TABLE DateTime = '31/1/2001 8:00:00'
INSERT TABLE DateTime = '18/1/2001 4:00:00'
INSERT TABLE DateTime = '21/1/2001 3:00:00'
...........
..........
DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 4
18/1/2001 4:00:00 3 2
21/1/2001 3:00:00 4 3

How to do this?
Only with extreme difficulty because inserting a row may change the PriKey
of every other row.

EG
after
INSERT TABLE DateTime = '1/1/2001 12:00:00'
INSERT TABLE DateTime = '31/1/2001 8:00:00'

you have

DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 2
then, after

after INSERT TABLE DateTime = '18/1/2001 4:00:00'

you have

DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 3
18/1/2001 4:00:00 3 2

Very, very ugly stuff.

Do do this you would have to run something like

update my_table t set prikey = (select 1+count(*) from my_table where
my_date < t.my_date)

in an update, insert and delete trigger.

Very ugly stuff, and very slow and 100% guaranteed to scale poorly.

David

[Aaron Bertrand - MVP]

> Everytime I insert a new record the AutoIncrementKey field will increase
by one

but I want the PriKey to be in the order of the datetime as shown below.

Why do you need to STORE this data? I would write a stored procedure or
view that calculated it at select time. As David points out, you would need
triggers to do this and it would certainly kill the performance of your app.

(Also, not sure why this was posted to comp.databases.paradox?)

A

[Bob Barrows]

Aaron Bertrand - MVP wrote:

(Also, not sure why this was posted to comp.databases.paradox?)

Maybe because it's a paradox database and the queston should not have been
crossposted to the sql server groups ...

[Anith Sen]

First of all, the request for additional columns this table is redundant.
There is no information about the nature of the datetime column; is it
unique? If so, declare it as your primary key, there is no need for another
column.

CREATE TABLE tbl (
dt DATETIME NOT NULL PRIMARY KEY ) ;

If you need a numeric identifier, the prikey column will suffice & the
'autoincrementkey' makes little sense. What is the rule for serialization in
case of multi-row inserts? How do you even know the order in which row is
inserted ? A popular workaround used in t-SQL is to use an IDENTITY column
like:

CREATE TABLE tbl (
dt DATETIME NOT NULL PRIMARY KEY,
autoincr INT NOT NULL IDENTITY);

And you can have a view like:

CREATE VIEW (dt, col, incr)
AS
SELECT dt, ( SELECT COUNT(*)
FROM tbl t1
WHERE t1.dt <= tbl.dt) AS "intcol",
autoincr
FROM tbl ;

--
- Anith
( Please reply to newsgroups only )

[Aaron Bertrand - MVP]

> > (Also, not sure why this was posted to comp.databases.paradox?)

Maybe because it's a paradox database and the queston should not have been
crossposted to the sql server groups ...

Well, I figured majority rules. :-)

[Steve]

DateTime Mode
1/1/2001 12:00:00 1
31/1/2001 8:00:00 7
18/1/2001 4:00:00 3
21/1/2001 3:00:00 3
21/1/2001 5:00:00 3
22/1/2001 3:00:00 7
22/1/2001 8:00:00 7
23/1/2001 3:00:00 3
23/1/2001 9:00:00 5

What I actually want to do is just this, the insertion might not be in
any order or time,
for the above table,
get the total time for each mode Example
Mode 1 Duration = 31/1/2001 8:00:00 - 1/1/2001 12:00:00
Mode 7 Duration= 18/1/2001 4:00:00 - 31/1/2001 8:00:00 + 23/1/2001
3:00:00 - 22/1/2001 3:00:00
Mode 3 Duration= 22/1/2001 3:00:00 - 18/1/2001 4:00:00 + 23/1/2001
9:00:00 - 23/1/2001 3:00:00
Mode 5 Duration= CurrentTime Now - 23/1/2001 9:00:00
How should the sql statement be???
I actually wanted to get the starttime using the statement below but
how to get the endtimes???
Select DateTime, AutoIncrementKey Where Mode = 1
Select DateTime, AutoIncrementKey Where Mode = 7
Select DateTime, AutoIncrementKey Where Mode = 3
Select DateTime, AutoIncrementKey Where Mode = 5

[Anith Sen]

Steve,

I think you are not following the implications. The calculation you have
shown as :

Mode 1 Duration =
31/1/2001 8:00:00 - 1/1/2001 12:00:00
Mode 7 Duration =
18/1/2001 4:00:00 - 31/1/2001 8:00:00 +
23/1/2001 3:00:00 - 22/1/2001 3:00:00
Mode 3 Duration =
22/1/2001 3:00:00 - 18/1/2001 4:00:00 +
23/1/2001 9:00:00 - 23/1/2001 3:00:00
Mode 5 Duration =
CurrentTime Now - 23/1/2001 9:00:00 <<

depends on the how the rows are being represented positionally in the table.
In other words, for you to decide which datetime value to be subtracted from
which other one, you have to rely on the position of a row in relative to
another. This cannot be, since the rows in a table are not ordered, it may
return different ordering of rows under various circumstances. You have 3
rows with mode 7 and 4 rows with mode 3. How do you decide which one should
be considered for Mode 7 & Mode 3 calculations in which order?

To do this reliably, you need to have a logical value ( loosely put, another
column which can represent the required sequence of datetime values on which
you do your calculations ) for each datetime value.

--
- Anith
( Please reply to newsgroups only )