Professional Web Applications Themes

MaxDate - Microsoft SQL / MS SQL Server

I have 3 tables: table1: DateMod (smalldatetime) StId Eid table2: DateMod (smalldatetime) StId Wid table3: stid I am selecting the max(Wid) and max(eid) based onstid: select max(Wid),date from table2 a,table 3 where a.stid=b.stid group by a.stid union select max(eid),date from table1 a,table 3 where a.stid=b.stid group by a.stid At this point I need to compare the dates between the results (if sid listed in both results) and select the most resent value from one of the tables for each stid. How do I do this?...

  1. #1

    Default MaxDate

    I have 3 tables:
    table1:
    DateMod (smalldatetime)
    StId
    Eid

    table2:
    DateMod (smalldatetime)
    StId
    Wid

    table3:
    stid

    I am selecting the max(Wid) and max(eid) based onstid:
    select max(Wid),date
    from table2 a,table 3
    where a.stid=b.stid
    group by a.stid
    union
    select max(eid),date
    from table1 a,table 3
    where a.stid=b.stid
    group by a.stid


    At this point I need to compare the dates between the
    results
    (if sid listed in both results) and select the most resent
    value
    from one of the tables for each stid.
    How do I do this?
    dennis Guest

  2. #2

    Default Re: MaxDate

    Please show some sample data and the results you wish to obtain from the
    sample data.

    Bob Barrows

    dennis wrote:
    > I have 3 tables:
    > table1:
    > DateMod (smalldatetime)
    > StId
    > Eid
    >
    > table2:
    > DateMod (smalldatetime)
    > StId
    > Wid
    >
    > table3:
    > stid
    >
    > I am selecting the max(Wid) and max(eid) based onstid:
    > select max(Wid),date
    > from table2 a,table 3
    > where a.stid=b.stid
    > group by a.stid
    > union
    > select max(eid),date
    > from table1 a,table 3
    > where a.stid=b.stid
    > group by a.stid
    >
    >
    > At this point I need to compare the dates between the
    > results
    > (if sid listed in both results) and select the most resent
    > value
    > from one of the tables for each stid.
    > How do I do this?

    Bob Barrows Guest

  3. #3

    Default Re: MaxDate

    For StID='12345' I have records in both tables.
    I am selecting max Wid for stid(group by).
    Now I need select the max DateMod from both table if it
    exists
    in both tables (stid) if not just use record with max
    (Wid) or (EID).

    Table1:
    DateMod StId Wid
    12/13/2002 12345 9052
    12/03/2002 24566 9004
    12/16/2002 12345 9072

    table2:
    DateMod StId Eid
    12/12/2002 12345 8001
    12/03/2002 24566 8005
    12/16/2002 12345 7900

    table3:
    stid
    12345
    24566
    12345
    dennis Guest

  4. #4

    Default Re: MaxDate

    I'm sorry, i still don't understand what the output data is supposed to look
    like. Please show me instead of describing it to me.
    dennis wrote:
    > For StID='12345' I have records in both tables.
    > I am selecting max Wid for stid(group by).
    > Now I need select the max DateMod from both table if it
    > exists
    > in both tables (stid) if not just use record with max
    > (Wid) or (EID).
    >
    > Table1:
    > DateMod StId Wid
    > 12/13/2002 12345 9052
    > 12/03/2002 24566 9004
    > 12/16/2002 12345 9072
    >
    > table2:
    > DateMod StId Eid
    > 12/12/2002 12345 8001
    > 12/03/2002 24566 8005
    > 12/16/2002 12345 7900
    >
    > table3:
    > stid
    > 12345
    > 24566
    > 12345

    Bob Barrows Guest

  5. #5

    Default Re: MaxDate


    Table1:
    DateMod StId Wid
    12/13/2002 12345 9052
    12/02/2002 24566 9004
    12/16/2002 12345 9072
    12/16/2002 22349 7907

    table2:
    DateMod StId Eid
    12/12/2002 12345 8001
    12/03/2002 24566 8005
    12/16/2002 12345 7900
    12/16/2002 22345 7905

    table3:
    stid
    12345
    24566
    12345
    22345

    Result:

    12345 12/16/2002 7900
    24566 12/03/2002 8005
    22345 12/16/2002 7900
    22349 12/16/2002 7907
    dennis Guest

  6. #6

    Default Re: MaxDate


    1. I'm not clear about what table3 has to do with anything.
    In table3 I have details about demographic for spesific
    stid.
    I didn't listed.

    StID 22349 exists in Table1, but not in table3,
    yet you have a row for it (22349) in your desired
    results.
    Sorry I foget to add in table 3.
    All stid have to exist in table3.


    2. A record for StID 12345, 12/16/2002 exists in both
    Table1 and Table2.
    Your desired result uses the Eid from Table2 (7900)
    instead of the Wid
    (9072) from Table1. Why?
    Because for stid 12345 the latest transaction date is
    12/16/2002.
    This is my problem. If I have a record for stid in both
    tables select
    the latest record based on datemod.

    Do you realize that you have a non-normalized database
    design?
    In each table I have different information and I need to
    collect
    this info into 1 result.

    Thanks
    Dennis

    dennis Guest

  7. #7

    Default Re: MaxDate

    dennis wrote:
    > 1. I'm not clear about what table3 has to do with anything.
    > In table3 I have details about demographic for spesific
    > stid.
    > I didn't listed.
    >
    > StID 22349 exists in Table1, but not in table3,
    > yet you have a row for it (22349) in your desired
    > results.
    > Sorry I foget to add in table 3.
    > All stid have to exist in table3.
    OK
    >
    >
    > 2. A record for StID 12345, 12/16/2002 exists in both
    > Table1 and Table2.
    > Your desired result uses the Eid from Table2 (7900)
    > instead of the Wid
    > (9072) from Table1. Why?
    > Because for stid 12345 the latest transaction date is
    > 12/16/2002.
    > This is my problem. If I have a record for stid in both
    > tables select
    > the latest record based on datemod.
    >
    But they are BOTH the latest record: they both have DateMod = 12/16/2003.
    Here's the record from Table1:
    12/16/2002 12345 9072

    And here's the record from Table2:
    12/16/2002 12345 7900

    See? Neither qualifies as latest. So we have to choose whether to use 9072
    or 7900. How do you want this choice to be made?

    Bob Barrows


    Bob Barrows Guest

  8. #8

    Default Re: MaxDate

    My Date is set to smalldatetime, so it should take the
    correct time if max is used.
    Here's the record from Table1:
    12/16/2002 12345 9072

    And here's the record from Table2:
    12/16/2002 12345 7900
    dennis Guest

  9. #9

    Default Re: MaxDate

    dennis wrote:
    > My Date is set to smalldatetime, so it should take the
    > correct time if max is used.
    > Here's the record from Table1:
    > 12/16/2002 12345 9072
    >
    > And here's the record from Table2:
    > 12/16/2002 12345 7900
    You mean the fields contain times as well as dates and you're just not
    showing them to me? So what you're telling me is that if you were showing me
    the times that are in the fields, one would be more recent than the other?
    Well, OK, but it would have been helpful if you had shown me the times to
    begin with - I could have had a solution for you a few hours ago ...

    I'm going to start by updating the date in my copy of Table2 so it contains
    a time making it more recent than the corresponding 12345 record in Table1.

    Here is the script I used to create my test tables and data:
    CREATE TABLE dbo.Table1
    (
    DateMod datetime NOT NULL,
    StID int NOT NULL,
    Wid int NOT NULL
    )
    go
    CREATE TABLE dbo.Table2
    (
    DateMod datetime NOT NULL,
    StID int NOT NULL,
    Eid int NOT NULL
    )
    INSERT INTO Table1 ( DateMod, StID, Wid )
    VALUES ( '12/13/2002 12:00:00.000 AM', 12345, 9052 )
    go
    INSERT INTO Table1 ( DateMod, StID, Wid )
    VALUES ( '12/02/2002 12:00:00.000 AM', 24566, 9004 )
    go
    INSERT INTO Table1 ( DateMod, StID, Wid )
    VALUES ( '12/16/2002 12:00:00.000 AM', 12345, 9072 )
    go
    INSERT INTO Table1 ( DateMod, StID, Wid )
    VALUES ( '12/16/2002 12:00:00.000 AM', 22349, 7907 )
    go
    INSERT INTO Table2 ( DateMod, StID, Eid )
    VALUES ( '12/12/2002 12:00:00.000 AM', 12345, 8001 )
    go
    INSERT INTO Table2 ( DateMod, StID, Eid )
    VALUES ( '12/03/2002 12:00:00.000 AM', 24566, 8005 )
    go
    INSERT INTO Table2 ( DateMod, StID, Eid )
    VALUES ( '12/16/2002 12:00:00.000 AM', 22345, 7905 )
    go
    INSERT INTO Table2 ( DateMod, StID, Eid )
    VALUES ( '12/16/2002 02:00:00.000 AM', 12345, 7900 )
    go
    CREATE TABLE dbo.table3
    (
    stid int NOT NULL
    )
    go
    INSERT INTO table3 ( stid )
    VALUES ( 12345 )
    go
    INSERT INTO table3 ( stid )
    VALUES ( 22345 )
    go
    INSERT INTO table3 ( stid )
    VALUES ( 22349 )
    go
    INSERT INTO table3 ( stid )
    VALUES ( 24566 )
    go


    Then I'm going to create a couple views to make the final query easier to
    read:

    create view NormalizedData as
    Select DateMod, t1.StID, Wid Value
    FROM table1 t1 inner join table3 t3
    ON t1.stid = t3.stid
    UNION
    Select DateMod, t2.StID, Eid
    FROM table2 t2 inner join table3 t3
    ON t2.StID= t3.StID
    go
    create view MaxDateMods as
    select StID, max(DateMod) maxdate
    from NormalizedData
    group by StID
    go

    And here's the final query:

    select n.StID,DateMod,[Value]
    from NormalizedData n inner join MaxDateMods m
    on n.StID= m.StIDand n.DateMod=m.maxdate

    This yields these results:
    12345 12/16/2002 2:00:00.000 AM 7900
    22345 12/16/2002 12:00:00.000 AM 7905
    22349 12/16/2002 12:00:00.000 AM 7907
    24566 12/3/2002 12:00:00.000 AM 8005

    HTH,
    Bob Barrows


    Bob Barrows Guest

  10. #10

    Default Re: MaxDate

    You're very welcome.

    dennis wrote:
    > THANK YOU>>> :)

    Bob Barrows 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