Select Statement

[mkal]

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 when the next scheduled call should take place.

What I need to find is the total number of times a sales person called a client for a given day. 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. I’d 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

I’m sure there is a simple answer to this but it keeps eluding me. Any help would be greatly appreciated.

 

[marsh]

select 
  ClientNumber
  , convert(varchar(10), DateCalled ,120) as DateCalled 
  , count(*) as TimesCalled
from tablename
  where convert(varchar(10),DateCalled,112) = 
  convert(varchar(10),dtDateToFind,112)
group by ClientNumber, convert(varchar(10), DateCalled ,120)
order by ClientNumber, convert(varchar(10), DateCalled ,120)

Notes:
-- dtDateToFind is the date searched for
-- I like using ISO standard date(112) century format for date comparisons where time is irrelevant
-- you need to group/order the date formatting the same way that you want to display the date....


Marsha

 

[mkal]

Marsha,

It worked with a minor tweak. I realized I did not need to compare the results to a specific date but it was a nice touch.

Thanks for the help it was just one of those days!

Mike