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

    Unanswered: SQL for counting consecutive scores

    In one table I have hundreds of dated records for lots of different staff there can also be multiple instances of the same member of staff, cataloguing scoring and performance. Each record has a final percentage. I need to create a query somehow to extract data to show which staff are scoring consecutive 100% scores, and ultimately count the consecutive 100% scores
    (e.g. Adam Smith scored 13 consecutive 100%'s between DATE1 and DATE2)

    The Overall Goal:
    Extract a list of advisors and percentages (sorted by date), then identify and count the 100%'s in a row


    At the moment I can produce a list of staff that have achieved 100% within the given date range, this is no good however because it ONLY displays 100%'s (so i no longer know if there were non-100%'s in between- so it's not actually possible to count legitimate consecutive 100%'s) and the other problem is it ONLY shows the results from within the given date range (so if a staff member has a streak of 100%'s that spilled over from the prior month, i can't see that data either):

    Code:
    SELECT 
[FTP Marking Sheet_Q].[Outsource Company], 
[FTP Marking Sheet_Q].[Monitoring Record ID], 
[FTP Marking Sheet_Q].[Advisor Name], 
[FTP Marking Sheet_Q].[User ID], 
[FTP Marking Sheet_Q].Percentage, 
[FTP Marking Sheet_Q].[Date of Recorded Call]
    FROM [FTP Marking Sheet_Q]
    WHERE 
((([FTP Marking Sheet_Q].[Outsource Company])=[Forms]![Main Menu]![OutsourceCentre]) 
AND 
(([FTP Marking Sheet_Q].Percentage)=100) 
AND 
(([FTP Marking Sheet_Q].[Date of Recorded Call]) Between [Forms]![Main Menu]![FromDate] And [Forms]![Main Menu]![ToDate]))
    ORDER BY [FTP Marking Sheet_Q].[Advisor Name];
    Alternatively I have a separate query that will produce a full list of ALL staff (grouped), and list ALL of their sequential Percentages, without any kind of Date range:

    Code:
    SELECT 
[FTP Marking Sheet_Q].[Outsource Company], 
[T_Outsource Staff].[Advisor Name], 
[T_Outsource Staff].[User Deactivated], 
[FTP Marking Sheet_Q].[Date of Recorded Call], 
[FTP Marking Sheet_Q].[Monitoring Record ID], 
[FTP Marking Sheet_Q].Percentage
    FROM [FTP Marking Sheet_Q] INNER JOIN [T_Outsource Staff] ON [FTP Marking Sheet_Q].[User ID] = [T_Outsource Staff].[User ID]
    GROUP BY 
[FTP Marking Sheet_Q].[Outsource Company], 
[T_Outsource Staff].[Advisor Name], 
[T_Outsource Staff].[User Deactivated], 
[FTP Marking Sheet_Q].[Date of Recorded Call], 
[FTP Marking Sheet_Q].[Monitoring Record ID], 
[FTP Marking Sheet_Q].Percentage
    HAVING ((([T_Outsource Staff].[User Deactivated])=0))
    ORDER BY [FTP Marking Sheet_Q].[Date of Recorded Call] DESC , [FTP Marking Sheet_Q].[Monitoring Record ID] DESC;
    The problem with this is there are far too many members of staff, which makes the whole process long-winded and very time consuming. So I want to somehow filter the results to only give me results for staff that have scored 'a' 100% score within the date range (thus not listing those members of staff which didn't score any 100% within the date range) HOWEVER, if an advisor did in fact score a 100% within the date range it NEEDS to show their full Percentage history (the non-100%'s and also the results prior to the date range - so if someone scored 3x 100%'s in a row last month and then the next calendar month continued their consecutive scores I don't want them to have been cut off.

    Is it possible to achieve something like this?

    Example Results Data:
    Advisor Name - Date of Call - Percentage
    Adam Smith - 01/03/2013 - 98%
    Adam Smith - 05/03/2013 - 100%
    Adam Smith - 07/03/2013 - 91%
    Adam Smith - 28/03/2013 - 100%
    Adam Smith - 30/03/2013 - 100%
    Adam Smith - 31/03/2013 - 100%
    Adam Smith - 02/04/2013 - 100% - x4 consecutive 100%'s (28/3/2013 to 2/4/2013)
    Adam Smith - 07/04/2013 - 86%
    Adam Smith - 08/04/2013 - 100%
    Adam Smith - 15/04/2013 - 98%

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I would probably write an export to Excel, and then include within the worksheet a formula column to carry out the analysis. Excel is far better at that kind of analysis than Access.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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