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

    Unanswered: Help ASAP 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
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Help ASAP on SQL!!!!!!!!!!!!!!!!!!!!!!

    Your Un-unique sql is truly unique. there are diferences in availability and time frams that make each row unique. If you need to colaps the records more try filtering, like where cust id = 1
    and try using datepart to get the date down to day/month and take the time out of it. Those are the things that are realy not needed that can help colaps your records
    Jim


    Originally posted by Wango
    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

  3. #3
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    I used the following query and it seems to be doing the job.

    select cstunitid, billetid, cast(availability as int) as availability,
    datetimestamp as datetimestamp
    FROM DM_Bllet_Assignment_Details_view
    where datetimestamp is not null and
    datetimestamp=(select max(datetimestamp) from DM_Bllet_Assignment_Details_view)
    group by datetimestamp,cstunitid, billetid,cast(availability as int)
    order by datetimestamp,cstunitid,billetid

    If you find any holes in that query, please point them out to me. I do not want to be under the illusion that the query is right in all circumstances.


    Thanks for your response.

Posting Permissions

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