question on counts in DDMMYYY HHMMSS format 1

[psimon88]

Hello

I run this SQL statement:

SELECT TOP 100 PERCENT dbo.VM_Leads.dtmTimeStamp AS
BornDate, COUNT(dbo.Dispositions.contact) AS CONTACTS
FROM dbo.VM_Leads INNER JOIN
dbo.ED_Caller_VM_Leads ON dbo.VM_Leads.lApplicationID = dbo.ED_Caller_VM_Leads.lApplicationID INNER JOIN
dbo.Dispositions ON dbo.ED_Caller_VM_Leads.lCallResult = dbo.Dispositions.lDispositionId
WHERE (dbo.VM_Leads.bApplicantEligible = 1)
GROUP BY dbo.VM_Leads.dtmTimeStamp
HAVING (dbo.VM_Leads.dtmTimeStamp > CONVERT(DATETIME, '2005-07-01 00:00:00', 102)) AND (dbo.VM_Leads.dtmTimeStamp < CONVERT(DATETIME,
'2005-07-02 00:00:00', 102))

and get multiple lines per date of 7/1 because the date is stored like this: '2005-07-02 00:00:00'. I would just like one line with a count of CONTACTS by date.

Any ideas?

Thanks.

ps

 

[cmmrfrds]

WHERE (dbo.VM_Leads.bApplicantEligible = 1)
AND (dbo.VM_Leads.dtmTimeStamp > CONVERT(DATETIME, '2005-07-01 00:00:00', 102)) AND (dbo.VM_Leads.dtmTimeStamp < CONVERT(DATETIME,'2005-07-02 00:00:00', 102))
GROUP BY Convert(datetime, dbo.VM_Leads.dtmTimeStamp,102)

Apply a format to the date when you group. I assume style 102 formats to just the date without the time. If not use a style that does this.

I don't see a reason to put the select criteria in the having clause which is not efficient. Usually criteria in the having clause is on summary data, such as, count(*) > 2 or something like that.