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

    Unanswered: Assigning count to based upon date?

    Hi there!

    not really sure how to explain what i want, hence the possibly misleading title.
    I need to assign a number to account numbers based upon the date that it was contacted, see below for example.

    Data sample

    _Account _Atime1 _Status
    11235 23/05/2012 08:16 No Answer
    11235 25/05/2012 16:15 No Answer
    11235 26/05/2012 08:50 Not Interested
    21567 15/05/2012 19:24 No Answer
    114687 23/05/2012 08:05 Not Interested
    21567 22/05/2012 17:35 Not Interested

    this is what i would like the query to return;

    _Account _Atime1 _Status CCount
    11235 23/05/2012 08:16 No Answer 3
    11235 25/05/2012 16:15 No Answer 2
    11235 26/05/2012 08:50 Not Interested 1
    21567 15/05/2012 19:24 No Answer 2
    114687 23/05/2012 08:05 Not Interested 1
    21567 22/05/2012 17:35 Not Interested 1

    Thanks in advance for any help that you can give!

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try something like that:

    Code:
    select
        t.*,
        ROW_NUMBER() OVER(PARTITION BY Account ORDER BY ATime DESC) AS CCount
    from MyTable as t
    Hope this helps.

Posting Permissions

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