help

Can anybody please help me with this problem
1. I have a table with the following column :
code Date Time vol exptime elapse
4 20010424 104232 945 40165
My problem is none of these column can be my primary key so my question is
How can I assign another column say 'transact_id' which will have an incremental and unique number? In other word my objective are:
a. I would like to have the data sorted according to code, date, time
b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Yes I think I can add another column which is the combination of date and time so each column will look like this yyyymmddhhmmss--> e.g. 20010101102010 which is 1 Jan 2001 10:20:10|||It would really help if you could post the DDL (probably the CREATE TABLE statement) for your table. Otherwise we need to guess at too much. Yes, it is quite possible to create a query like what you want.

-PatP|||I have attached the sample file
for you convenience I have selected random sample of less than 100 observation
thanks in advance for your help|||a. I would like to have the data sorted according to code, date, time you don't need a primary key to do that

b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on you could add an IDENTITY column to the table, but you don't need to

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?yes, i can

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

clarifying exactly what you want is important to the eventual sql

by the way, if you can, you should replace the date and time columns with one datetime column|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachment

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

I'm not really sure whether i understand your question however, here's my explanation: The code is a stock code so I arrange the data to be sorted according to code, date and time and I would like to calculate the elapse
as the difference between exptime for the current row and the previous row using macro in excel the langguange will look like this
R1C1 = "=RC[-1]-R[-1]C[-1]"

please tell me if you think you need more explanation|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachmentif you meant to attach a new description, you forgot ;)

i will use your first description:select t1.code
, t1.[Date]
, t1.[Time]
, t1.vol
, t1.exptime
, t1.exptime
-t2.exptime as diff
from currprev as t1
left outer
join currprev as t2
on t1.code = t2.code
and cast(t2.[Date] as char(8))
+cast(t2.[Time] as char(6))
= (
select max(
cast([Date] as char(8))
+cast([Time] as char(6))
)
from currprev
where (
[Date] < t1.[Date]
or [Date] = t1.[Date]
and [Time] < t1.[Time]
)
)
order by 1,2,3
the results of this are: code Date Time exptime elapse
5 20010102 100921 36561
5 20010102 104046 38446 1885
5 20010102 132221 48141 9695
5 20010102 132518 48318 177
5 20010103 102123 37283 -11035
5 20010103 120312 43392 6109
5 20010103 122434 44674 1282
5 20010103 150953 54593 9919
5 20010103 150953 54593 9919
5 20010103 151918 55158 565
5 20010103 151918 55158 565
5 20010104 101123 36683 -18475
5 20010104 121213 43933 7250
5 20010104 144338 53018 9085
5 20010104 145634 53794 776
5 20010104 153809 56289 2495
5 20010105 123717 45437 -10852
5 20010105 132814 48494 3057
5 20010125 112752 41272 -7222
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 114443 42283 470
5 20010125 114550 42350 67
5 20010125 114756 42476 126
5 20010125 114756 42476 126
5 20010125 114905 42545 69
5 20010125 114905 42545 69
5 20010125 115235 42755 210
5 20010125 121430 44070 1315
5 20010125 123000 45000 930
329 20010424 104232 38552
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 110925 40165 1185
329 20010424 110925 40165 1185
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120627 43587 4
329 20010424 121024 43824 237
329 20010521 104239 38559 -5265
et cetera
having an IDENTITY primary key would have helped immensely

your data has dupes in it, and consequently the output reflects this, because the dupes are cross-joined within groups of the same code/[Date]/[Time]

i strongly urge you to clean up the data before proceeding|||Thanks very much, (sorry about the attachment)
However I can not delete the duplicates since it is a vaild observations
Do you have any suggestion about creating an IDENTITY primary key ?