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. I’d like the output to look like the following:
select ClientNumber, Convert(Datetime,CONVERT(varchar(10),DateCalled,10 1))) DateCalled, count(*)
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 )
RETURN (Convert(Datetime,CONVERT(varchar(10),@RawDateTime ,101)))
Your statement could then be written more simply like this:
Group by ClientNumber,