Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    24

    Unanswered: Query Help - Show results from ALL dates, but only if scored 100% within date range

    Hi,

    I have a database that is used for keeping scores for advisors. Ideally I now need a query that will list their Percentage history; these are the relevant fields:

    Advisor
    UserID
    DateOfCall
    CallID
    Percentage

    I can quite easily get it to show the list of Percentages for each and every advisor; however what I actually want to to do is include an additional rule somehow to -'Ignore' any advisor that has NOT scored 100% between StartDate and EndDate

    So essentially, if an Advisor DID score 100% in that date range I still want to see their full record history including the prior dates; but I only want the Advisor to be listed at all if they achieved 1 or more 100% score during the date range (thus getting rid of a big pile of irrelevant data).

    If I could get this working, this would essentially be used to identify people scoring 100%'s in a row and reviewed on a monthly basis

    Is this possible?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    essentially its down to your where clause

    So there's various things required
    all employees who scored 100% in a specific time period
    the time period is :_
    DateOfCall Between #adatelieteralinISOformat# and #anotherdatelieteralinISOformat#
    ..where
    #adatelieteralinISOformat# could be #2013/01/01#
    #anotherdatelieteralinISOformat#2013/01/31#

    the # symbols tell the QL engine the enclosed text is a date literal and not a formula ie 01 Jan 2013 NOT 2013 divided by 1 divided by. howevr that depends on how you have stored your date values. if your user entered them as dtesd ONLY or you used the date() function then you should be OK if you used now() then you will have stored the time alongside the date and you need to change the date clause
    DateOfCall >= #adatelieteralinISOformat# and DateOfCall < #anotherdatelieteralinISOformat#
    #adatelieteralinISOformat# reamins the same (#2013/01/01#)
    #anotherdatelieteralinISOformat# becomes #2013/02/02#

    to find JUST a single row per employee
    use the SELECT TOP clause OR a SELECT DISTINCT
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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