Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2013
    Posts
    16

    Unanswered: COUNT() & Filtering Out Data

    Hello all,

    I have two tables: one is a list of days as datetime, and the second is a movie request log with 5 columns of data and 5 different customer requests on 01/01/14 for Joe, Bob, Mark, Dan, & Sarah.

    • Movie ID (nvarchar(255))
    • Start Time (datetime)
    • End Time (datetime)
    • Customer Name (nvarchar(255))
    • Movie Playback Status (nvarchar(255))


    Joe attempted to watch the same movie twice, once at 8:00AM and again at 11:00AM (highlighted in blue). All of his sessions failed and the attempts that are highlighted in red are system automatic retries. Everyone else had one successful movie stream request.


    I'd like to create a SELECT statement that lists the count of unique Movie Status results (successful & failed movie requests) between each customer per day, by counting all retries from the same customer and movie within a 5 minute (STARTTIME) window from the last attempt as ONE request.


    TABLE_A:

    DATE
    ==========
    01/01/2014

    TABLE_B:

    MOVIE | STARTTIME | ENDTIME | CUSTOMER | STATUS
    ========================================
    Movie 1 | 01/01/14 8:00:00AM | 01/01/14 8:01:00AM | Joe | FAILED
    Movie 1 | 01/01/14 8:01:05AM | 01/01/14 8:02:05AM | Joe | FAILED
    Movie 1 | 01/01/14 8:02:10AM | 01/01/14 8:03:10AM | Joe | FAILED
    Movie 1 | 01/01/14 8:04:10AM | 01/01/14 8:05:15AM | Joe | FAILED

    Movie 2 | 01/01/14 8:30:00AM | 01/01/14 9:00:00AM | Bob | SUCCESS
    Movie 3 | 01/01/14 9:00:00AM | 01/01/14 9:30:00AM | Mark | SUCCESS
    Movie 4 | 01/01/14 9:30:00AM | 01/01/14 10:00:00AM | Dan | SUCCESS
    Movie 5 | 01/01/14 10:00:00AM | 01/01/14 10:30:00AM | Sarah | SUCCESS
    Movie 1 | 01/01/14 11:00:00AM | 01/01/14 11:01:00AM | Joe | FAILED
    Movie 1 | 01/01/14 11:01:05AM | 01/01/14 11:02:05AM | Joe | FAILED
    Movie 1 | 01/01/14 11:02:10AM | 01/01/14 11:03:10AM | Joe | FAILED
    Movie 1 | 01/01/14 11:04:10AM | 01/01/14 11:05:15AM | Joe | FAILED

    My original code:

    Code:
    SELECT DISTINCT
    A.DATE,
    B.STATUS,
    COUNT(DISTINCT CHECKSUM(B.MOVIE, B.CUSTOMER, B.STATUS) AS COUNT
    FROM TABLE_A AS A, TABLE_B AS B
    WHERE B.STARTTIME >= A.DATE
    AND B.STARTTIME < DATEADD(DAY, 1, A.DATE)
    Resulted in:

    DATE | STATUS | COUNT
    ====================
    01/01/14 | FAILED | 1
    01/01/14 | SUCCESS | 4

    This counts Joe's 2 attempts + 6 retries as 1 error for the day. However, I'd like to count every movie that Joe attempted within a 5 minute STARTTIME window from the last attempt as one failed movie.

    The following rows should show up as one failure. The retries are less than 5 minutes later:

    Movie 1 | 01/01/14 8:00:00AM | 01/01/14 8:01:00AM | Joe | FAILED
    Movie 1 | 01/01/14 8:01:05AM | 01/01/14 8:02:05AM | Joe | FAILED
    Movie 1 | 01/01/14 8:02:10AM | 01/01/14 8:03:10AM | Joe | FAILED
    Movie 1 | 01/01/14 8:04:10AM | 01/01/14 8:05:15AM | Joe | FAILED



    Here's Joe's 2nd attempt for the same movie at 11:00AM, which is ~3 hours later from the last retry and should count as a separate request. Again, the retries are less than 5 minutes later:

    Movie 1 | 01/01/14 11:00:00AM | 01/01/14 11:01:00AM | Joe | FAILED
    Movie 1 | 01/01/14 11:01:05AM | 01/01/14 11:02:05AM | Joe | FAILED
    Movie 1 | 01/01/14 11:02:10AM | 01/01/14 11:03:10AM | Joe | FAILED
    Movie 1 | 01/01/14 11:04:10AM | 01/01/14 11:05:15AM | Joe | FAILED



    The end result should be:

    DATE | STATUS | COUNT
    ====================
    01/01/14 | FAILED | 2
    01/01/14 | SUCCESS | 4

    Lemme know if any of that makes sense and I'll try to elaborate more.

    Thanks!
    Nick

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Does this get you closer to what you need? Test data is not exact, but looks to be somewhat representative:
    Code:
    -- start test data setup
    create table test1
    (id int identity (1, 1),
     trydate datetime,
     reqstatus varchar(10))
    
    insert into test1 (trydate, reqstatus)
    values ('1/1/2014 08:01', 'fail'),
    	('1/1/2014 08:02', 'fail'),
    	('1/1/2014 08:03', 'fail'),
    	('1/1/2014 08:04', 'fail'),
    	('1/1/2014 08:05', 'success'),
    	('1/1/2014 11:01', 'success'),
    	('1/2/2014 08:01', 'fail'),
    	('1/2/2014 08:02', 'fail')
    -- end test data setup
    
    select count(distinct reqstatus), reqstatus, dateadd (mi, ((datepart (hh, trydate) * 60 + datepart (mi, trydate))/5) * 5, convert(datetime, convert(date, trydate)))
    from test1
    group by reqstatus, dateadd (mi, ((datepart (hh, trydate) * 60 + datepart (mi, trydate))/5) * 5, convert(datetime, convert(date, trydate)))

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This "smells" suspiciously like an assignment I helped write for a friend teaching a SQL class. The datatype for the Movie Playback Status is different, but the rest of the problem (including the sample data) matches what I gave her.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hey MCrowley,

    I'm not a datetime functions whiz, so I grab all opportunities I get to learn them better. It took me a few minutes to figure out what
    Code:
    dateadd (mi, ((datepart (hh, trydate) * 60 + datepart (mi, trydate))/5) * 5, convert(datetime, convert(date, trydate)))
    does.

    Nice!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Thanks, Wim.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I was trying this out on an LUW machine, v9.7, guess I have to go to v10 though. But just to be sure I got the gist of your solution MCrowley. The mi in the datepart function is minutes, correct?
    Dave

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That is correct, dav1mo. The whole list is here.

  8. #8
    Join Date
    Sep 2013
    Posts
    16
    Thanks folks, I figured it out.

    Quote Originally Posted by Pat Phelan View Post
    This "smells" suspiciously like an assignment I helped write for a friend teaching a SQL class. The datatype for the Movie Playback Status is different, but the rest of the problem (including the sample data) matches what I gave her.

    -PatP
    PatP, I'm an Engineer and this is part of software I'm writing for work.

    SELECT 0 AS ErrorRetries, STARTTIME, ENDTIME, MOVIE, CUSTOMER, STATUS
    INTO NEW_TABLE
    FROM ORIGINAL_TABLE

    UPDATE A SET A.ErrorRetries = 1 FROM NEW_TABLE A
    INNER JOIN (SELECT STARTTIME, ENDTIME, MOVIE, CUSTOMER, STATUS FROM NEW_TABLE A WHERE STATUS NOT IN ('ACTIVE', 'SUCCESS')) B
    ON A.MOVIE = B.MOVIE
    AND A.CUSTOMER = B.CUSTOMER
    AND A.STATUS = B.STATUS
    AND A.STARTTIME >= DATEADD(SECOND,1,B.ENDTIME) AND A.STARTTIME <= DATEADD(MINUTE,1,B.ENDTIME)

    DELETE FROM NEW_TABLE WHERE ErrorRetries = 1

Posting Permissions

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