Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    60

    Unanswered: Select Statement

    I've been trying to extract some sales information for our Sales dept. about phone calls placed to our clients. An example of some sample data for the query would look something like this:

    ClientNumber DateCalled NextCall
    327403 2003-06-03 14:45:17.000 2003-06-03 15:22:31.000
    327403 2003-06-03 15:22:31.000 2003-06-10 15:43:34.000
    327403 2003-06-10 15:43:34.000 2003-06-16 15:13:16.000
    327403 2003-06-16 15:13:16.000 2003-06-18 15:23:38.000
    327403 2003-06-18 15:23:38.000 2003-06-20 15:08:41.000
    327403 2003-06-20 15:08:41.000 2003-06-24 15:06:34.000
    327403 2003-06-24 15:06:34.000 2003-06-24 16:08:47.000
    327403 2003-06-24 16:08:47.000 2003-06-24 18:12:25.000
    327403 2003-06-24 18:12:25.000 2003-06-27 10:59:12.000

    DateCalled is the actual day the call was placed and NextCall is the next scheduled time to call back the client.

    What I need to find is the total number of times a sales person called a client for a given day and there is no concern if it is the same sales person or a different one.

    In the above example we can see that client 327403 was called twice on 2003-06-03 once at 14:45:17.000 and again at 15:22:31.000 and on 2003-06-24 the client was called three times once at 15:06:34.000 and once at 16:08:47.000 and once at 18:12:25.000. Id like the output to look like the following:

    ClientNumber DateCalled TimesCalled
    327403 2003-06-03 2
    327403 2003-06-10 1
    327403 2003-06-16 1
    327403 2003-06-18 1
    327403 2003-06-20 1
    327403 2003-06-24 3

    Im sure there is a simple answer to this but it keeps eluding me. Any help would be greatly appreciated.
    Last edited by mkal; 07-08-03 at 16:57.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select ClientNumber, Convert(Datetime,CONVERT(varchar(10),DateCalled,10 1))) DateCalled, count(*)
    from YourTable
    Group by ClientNumber, Convert(Datetime,CONVERT(varchar(10),DateCalled,10 1)))


    The second select field returns just the date value from your table, which you then use in grouping results. I'd recommend creating the following function so that you don't have to remember the syntax every time you want to strip the date out of a field:

    CREATE FUNCTION dbo.DateOnly
    (@RawDateTime datetime )
    RETURNS DateTime
    AS
    BEGIN
    RETURN (Convert(Datetime,CONVERT(varchar(10),@RawDateTime ,101)))
    END

    Your statement could then be written more simply like this:

    select ClientNumber,
    dbo.DateOnly(DateCalled) DateCalled,
    count(*)
    from YourTable
    Group by ClientNumber,
    dbo.DateOnly(DateCalled)

    blindman

  3. #3
    Join Date
    May 2003
    Posts
    60

    It worked!

    Thanks for the help. It does exactly what I was looking for. May be some day I can return the favor.

    Mike

Posting Permissions

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