Professional Web Applications Themes

MAX but one value ? - Microsoft SQL / MS SQL Server

You've described two different requirements. The latest date which is earlier than the parameter date is give by: DECLARE dt DATETIME SET dt = '20030601' SELECT id, MAX(datex), DATEDIFF(MINUTE,MAX(datex),dt) FROM Sometable WHERE datex < dt GROUP BY id The latest but one date is: SELECT id, MAX(datex), DATEDIFF(MINUTE,MAX(datex),dt) FROM Sometable AS S WHERE datex < (SELECT MAX(datex) FROM Sometable WHERE id = S.id) GROUP BY id -- David Portas ------------ Please reply only to the newsgroup -- "Stuart Crow" <stuart.crowscisys.co.uk> wrote in message news:ouq2gv8gp58rjqlj061752m3adc2ikk9nr4ax.com... > I have a table with records containing an ID (not primary key) and a > ...

  1. #1

    Default Re: MAX but one value ?

    You've described two different requirements.

    The latest date which is earlier than the parameter date is give by:

    DECLARE dt DATETIME

    SET dt = '20030601'

    SELECT id, MAX(datex), DATEDIFF(MINUTE,MAX(datex),dt)
    FROM Sometable
    WHERE datex < dt
    GROUP BY id

    The latest but one date is:

    SELECT id, MAX(datex), DATEDIFF(MINUTE,MAX(datex),dt)
    FROM Sometable AS S
    WHERE datex <
    (SELECT MAX(datex)
    FROM Sometable
    WHERE id = S.id)
    GROUP BY id

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Stuart Crow" <stuart.crowscisys.co.uk> wrote in message
    news:ouq2gv8gp58rjqlj061752m3adc2ikk9nr4ax.com...
    > I have a table with records containing an ID (not primary key) and a
    > date. The dates are a record of when changes were made and are
    > populated by triggers on update, insert, delete, so for a particular
    > ID the table will contain a range of dates up to and including the
    > date of the last change.
    > I need to write a stored procedure which will contain code to return
    > the highest but one date for an ID, ie. I pass in the latest date, and
    > need to obtain the last date before this with the difference in
    > minutes. I have tried
    > select max(date) from table
    > where datediff(minute, date, date passeed in) <= 1
    > but it always returns the last date, not the last but one.
    > Can anyone help ?
    > TIA
    > Stuart

    David Portas Guest

  2. #2

    Default Re: MAX but one value ?

    On Tue, 01 Jul 2003 12:19:42 +0100, Stuart Crow
    <stuart.crowscisys.co.uk> wrote:
    >I have a table with records containing an ID (not primary key) and a
    >date. The dates are a record of when changes were made and are
    >populated by triggers on update, insert, delete, so for a particular
    >ID the table will contain a range of dates up to and including the
    >date of the last change.
    >I need to write a stored procedure which will contain code to return
    >the highest but one date for an ID, ie. I pass in the latest date, and
    >need to obtain the last date before this with the difference in
    >minutes. I have tried
    >select max(date) from table
    >where datediff(minute, date, date passeed in) <= 1
    >but it always returns the last date, not the last but one.
    >Can anyone help ?
    >TIA
    >Stuart
    Sorry, I should have stated my problem more clearly.
    This is the code in the stored procedure

    DECLARE temp_Date datetime
    DECLARE temp_RoomBook integer
    DECLARE temp_shDate datetime

    CREATE TABLE temp_rb
    (i_RoomBook int,
    ChangeDate DateTime,
    shd_date datetime)

    -- this works fine
    INSERT INTO
    temp_rb (ChangeDate, i_RoomBook)
    SELECT DISTINCT
    MAX(saa_RoomBookEv.ChangeDate),
    saa_RoomBookEv.i_RoomBook
    FROM saa_RoomBookEv
    WHERE
    datediff(minute,ChangeDate, StartDate) >= 1
    AND datediff(minute,ChangeDate, EndDate) <= 1
    GROUP BY
    saa_RoomBookEv.i_RoomBook
    DECLARE aaa CURSOR FOR
    SELECT changedate, i_roombook
    FROM temp_rb
    OPEN aaa
    FETCH NEXT FROM aaa
    INTO temp_date, temp_roombook
    WHILE (fetch_status = 0)
    BEGIN
    UPDATE temp_rb
    SET shd_date =
    -- this always returns the latest date for the
    -- i_roombook, whereas I need the latest but one
    -- date for this i_roombook
    (SELECT MAX(shd_saa_RoomBookEv.ChangeDate)
    FROM
    shd_saa_roombookev
    WHERE
    datediff (minute, shd_saa_roombookev.changedate,
    temp_date) <= 1
    AND shd_saa_roombookev.i_roombook = temp_roombook)

    WHERE CURRENT OF aaa
    FETCH NEXT
    FROM aaa
    INTO temp_date, temp_roombook
    END

    CLOSE aaa
    DEALLOCATE aaa
    again apologies
    Stuart

    Stuart Crow Guest

  3. #3

    Default Re: MAX but one value ?

    Please include DDL as CREATE TABLE statements and sample data as INSERT
    statements with future posts. Here's my guess at what your data looks like:

    CREATE TABLE saa_RoomBookEv (i_roombook INTEGER NOT NULL, changedate
    DATETIME NOT NULL, PRIMARY KEY (i_roombook,changedate))
    INSERT INTO saa_RoomBookEv VALUES (1,'20030104')
    INSERT INTO saa_RoomBookEv VALUES (2,'20030510')
    INSERT INTO saa_RoomBookEv VALUES (3,'20030510')

    CREATE TABLE shd_saa_RoomBookEv (i_roombook INTEGER NOT NULL, changedate
    DATETIME NOT NULL, PRIMARY KEY (i_roombook,changedate))
    INSERT INTO shd_saa_RoomBookEv VALUES (1,'20030101')
    INSERT INTO shd_saa_RoomBookEv VALUES (1,'20030102')
    INSERT INTO shd_saa_RoomBookEv VALUES (1,'20030104')
    INSERT INTO shd_saa_RoomBookEv VALUES (1,'20030105')
    INSERT INTO shd_saa_RoomBookEv VALUES (2,'20030501')
    INSERT INTO shd_saa_RoomBookEv VALUES (2,'20030502')
    INSERT INTO shd_saa_RoomBookEv VALUES (2,'20030503')
    INSERT INTO shd_saa_RoomBookEv VALUES (3,'20030503')

    CREATE TABLE temp_rb (i_roombook INTEGER PRIMARY KEY, changedate DATETIME
    NOT NULL, shd_date DATETIME NULL)

    And here's my attempt at your query. You don't need the cursor or the
    UPDATE, you can do the whole thing as an INSERT...SELECT. For simplicity
    I've commented out the startdate, enddate parameters.

    INSERT INTO temp_rb (i_roombook, changedate, shd_date)
    SELECT S.i_roombook, MAX(S.changedate), MAX(R.changedate)
    FROM saa_RoomBookEv AS S
    JOIN
    (SELECT R1.i_roombook, MAX(R2.changedate) AS mdate
    FROM saa_RoomBookEv AS R1
    JOIN shd_saa_roombookev AS R2
    ON R1.i_roombook = R2.i_roombook AND R2.changedate <= R1.changedate
    GROUP BY R1.i_roombook, r1.changedate) AS M
    ON S.i_roombook = M.i_roombook
    JOIN shd_saa_RoomBookEv AS R
    ON M.i_roombook = R.i_roombook AND R.changedate < M.mdate
    /* AND DATEDIFF(MINUTE,S.changedate, StartDate) >= 1 AND
    DATEDIFF(MINUTE,S.changedate, EndDate) <= 1 */
    GROUP BY S.i_roombook

    Note that there is no result for i_roombook = 3 which has only one row and
    therefore no last-but-one date.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: MAX but one value ?

    On Tue, 01 Jul 2003 12:19:42 +0100, Stuart Crow
    <stuart.crowscisys.co.uk> wrote:

    Many thanks for all of the help and advice.
    I did not include anything about the tables besides the temp_rb since
    they are pre-existing and contain approx 400,000 records with about 50
    fields.
    Also the data contained within the tables could be sensitive.
    My sp is only to retrieve from these pre-existing tables.
    Thanks again
    Stuart

    Stuart Crow Guest

  5. #5

    Default Re: MAX but one value ?

    Primary and Foreign keys, nullability, check constraints and data types can
    all make a big difference to possible solutions. If you don't post the DDL
    then people just have to guess these things and the answer may be less
    useful to you.
    > Also the data contained within the tables could be sensitive.
    It's very difficult to get queries right first time without tables and data
    to test against. Invent some test data.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Stuart Crow" <stuart.crowscisys.co.uk> wrote in message
    news:bl43gvsfrv0amb8ep1bp4j12vklnbgh98c4ax.com...
    > On Tue, 01 Jul 2003 12:19:42 +0100, Stuart Crow
    > <stuart.crowscisys.co.uk> wrote:
    >
    > Many thanks for all of the help and advice.
    > I did not include anything about the tables besides the temp_rb since
    > they are pre-existing and contain approx 400,000 records with about 50
    > fields.
    > Also the data contained within the tables could be sensitive.
    > My sp is only to retrieve from these pre-existing tables.
    > Thanks again
    > Stuart
    >

    David Portas Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139