Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: Hepl on SQL !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    I am trying to get the most recent info on say, billetedid 1, regardless of its availability is “0” of “1”. (In other words, I want to get a unique billet for cstunitd = 69)
    But I also want to know what its availability.

    SQL1 gives me un-unique values but gives me the value of the “availability” that I need.
    SQL2 gives me the uniqueness of values that I want but does not gives me the value of the “availability” that I need.

    How can I get uniqueness and also the corresponding “availability” values?

    SQL1:
    SELECT cstunitid, billetid, cast(availability as int) as availability,
    MAX(datetimestamp) as datetimestamp
    FROM DM_Bllet_Assignment_Details_view
    where datetimestamp is not null and cstunitid = 69
    GROUP BY cstunitid, billetid,cast(availability as int)
    order by cstunitid,billetid


    RESULTS1:
    cstunitid billetid availability datetimestamp
    ----------- ----------- ------------ ---------------------------
    69 1 1 2002-11-18 14:04:55.000
    69 1 0 2002-11-21 00:17:22.000
    69 2 1 2002-11-18 14:04:55.000
    69 2 0 2002-11-21 00:17:22.000
    69 3 1 2002-11-21 00:17:22.000
    69 3 0 2002-11-18 14:04:34.000
    69 4 1 2002-11-21 00:17:22.000
    69 5 1 2002-11-21 00:17:22.000


    SQL2:
    SELECT cstunitid, billetid,
    MAX(datetimestamp) as datetimestamp
    FROM DM_Bllet_Assignment_Details_view
    where datetimestamp is not null and cstunitid = 69
    GROUP BY cstunitid, billetid
    order by cstunitid,billeted


    RESULTS2:
    cstunitid billetid datetimestamp
    ----------- ----------- ---------------------------
    69 1 2002-11-21 00:17:22.000
    69 2 2002-11-21 00:17:22.000
    69 3 2002-11-21 00:17:22.000
    69 4 2002-11-21 00:17:22.000
    69 5 2002-11-21 00:17:22.000

  2. #2
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Re: Hepl on SQL !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Surely it should be sufficient just to do a max(availability):

    SELECT cstunitid, billetid,
    max(availability) as availability
    MAX(datetimestamp) as datetimestamp
    FROM DM_Bllet_Assignment_Details_view
    where datetimestamp is not null and cstunitid = 69
    GROUP BY cstunitid, billetid
    order by cstunitid,billeted

  3. #3
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    If I use that query, I will always end up with a "1" in cases where there are "1" and "0". I do not want that; I want to be able to pick up the status as "0" if the most recent status is "0".

    Thanks for your response.

  4. #4
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    OK, I understand. Then you need a 2 stage process:

    1. identify the latest record for each cstunitid, billetid

    SELECT cstunitid, billetid,
    MAX(datetimestamp) as datetimestamp
    into #t1
    FROM DM_Bllet_Assignment_Details_view
    where datetimestamp is not null and cstunitid = 69
    GROUP BY cstunitid, billetid

    2. Get the availability for that latest record

    SELECT a.cstunitid, a.billetid,a.availability
    FROM DM_Bllet_Assignment_Details_view a, #t1 b
    where a.cstunitid=a.cstunitid
    and a.billetid=b.billetid
    and a.datetimestamp=b.datetimestamp
    order by cstunitid,billeted

    Note: if there's any chance of two records with the same timestamp you might want to create a new time-ordered key using identity(int,1,1) and use this instead of the datetimestamp.

  5. #5
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Thanks a lot andyabel. Before I try what you have just suggested, I want to ask whether I should create the views or table #t1 first. I using views and the database is SQL Server.

  6. #6
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Hi. If you use "select ... into tablename from ..." SQL Server creates a table for you automatically (it works out what fields and types you need). All you have to do is remember to drop it when you've finished.
    However, if you give the table a name beginning with '#' it makes it a temporary table and SQL Server will actually drop the table for you at the end of the batch.

  7. #7
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    When I now run



    SELECT a.cstunitid, a.billetid,a.availability
    FROM DM_Bllet_Assignment_Details_view a, #t1 b
    where a.cstunitid=b.cstunitid
    and a.billetid=b.billetid
    and a.datetimestamp=b.datetimestamp
    order by cstunitid,billetid


    I get an error message? Aaaaargh!!!!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, you need neither a view nor a temp table, one query will do it
    Code:
    select A.cstunitid
         , A.billetid
         , cast(A.availability as int) as availability
         , A.datetimestamp
      from DM_Bllet_Assignment_Details_view    as A
    inner
      join ( select cstunitid
                  , billetid    
                  , max(datetimestamp) as maxdatetime
               from DM_Bllet_Assignment_Details_view
              where datetimestamp is not null 
                and cstunitid = 69 
           group by cstunitid
                  , billetid )                as B
        on A.cstunitid = B.cstunitid
       and A.billetid  = B.billetid
       and A.datetimestamp = B.maxdatetime
    order 
        by A.cstunitid
         , A.billetid
    caution: not tested (obviously)

    rudy

  9. #9
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Well, my script parses now:

    set transaction isolation level read uncommitted
    drop table DM_Bllet_Assignment_Details_view
    drop table #t1

    create table DM_Bllet_Assignment_Details_view
    (
    cstunitid smallint,
    billetid smallint,
    availability smallint,
    datetimestamp datetime
    )
    go

    SELECT cstunitid, billetid,
    MAX(datetimestamp) as datetimestamp
    into #t1
    FROM DM_Bllet_Assignment_Details_view
    where datetimestamp is not null and cstunitid = 69
    GROUP BY cstunitid, billetid
    go

    SELECT a.cstunitid, a.billetid, availability
    FROM DM_Bllet_Assignment_Details_view a inner join #t1 b
    on a.cstunitid=b.cstunitid
    and a.billetid=b.billetid
    and a.datetimestamp=b.datetimestamp
    order by a.cstunitid, a.billetid
    go

Posting Permissions

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