Results 1 to 10 of 10

Thread: help

  1. #1
    Join Date
    May 2004
    Posts
    13

    Unanswered: 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    May 2004
    Posts
    13
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    May 2004
    Posts
    13
    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
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by csuj2
    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
    Last edited by r937; 09-11-04 at 09:00.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2004
    Posts
    13
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by csuj2
    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
    if you meant to attach a new description, you forgot

    i will use your first description:
    Code:
    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:
     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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2004
    Posts
    13
    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 ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •