Help with SQL Query
Posted on 2013-05-20
I am trying to build the sql query below - joining 3 tables and am a little stuck with the last line of the select query
"(SELECT COUNT(*) From Activity WHERE Activity.ActivityType = 'Appointment') as [Total]"
The requirement would be that when we run the below sql query it should do a count of all the Appointments for the last 6 months with the below criteria and return a total - At the moment it is doing a count but is returning multiple duplicate rows and unsure how to apply a date range.
ANY HELP WILL BE GREATLY APPRECIATED
Contact.xfAge as [Age],
xmoWizardCancellation.xfInitalSalesPerson as [Initial Sales Person],
Contact.Owner as [Owner],
xmoWizardCancellation.xfLastCRAssistant as [Last C/R Assistant],
xmoWizardCancellation.xfLastPaymentDate as [Last Payment Date],
xmoWizardCancellation.xfLastPaymentAmount as [Last Payment Amount],
xmoWizardCancellation.xfWasOnDDR as [DDR?],
xmoWizardCancellation.xfCurrentMedication as [Current Meds],
xmoWizardCancellation.CreatedBy as [Cancelled By],
xmoWizardCancellation.CreatedDateTime as [Cancelled Date],
Contact.xfPatientStatus as [Status],
(SELECT COUNT(*) From Activity WHERE Activity.ActivityType = 'Appointment') as [Total]
FROM dbo.Contact INNER JOIN
dbo.xmoWizardCancellation ON dbo.Contact.RecId = dbo.xmoWizardCancellation.xfContactRecID INNER JOIN
dbo.Activity ON dbo.Contact.RecId = dbo.Activity.ParentLink_RecID
WHERE Contact.xfPatientStatus = 'Test'
AND xmoWizardCancellation.CreatedDateTime > '2013-05-19 23:59:59.000'
AND xmoWizardCancellation.CreatedDateTime < '2013-05-20 23:59:59.000'
ORDER BY xmoWizardCancellation.CreatedDateTime desc