Solved

Help with SQL Query

Posted on 2013-05-20
11
475 Views
Last Modified: 2013-05-21
Hi

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

SELECT DISTINCT
Contact.xfpt
Contact.FullName,
Contact.xfAge as [Age],
Contact.Source,
xmoWizardCancellation.xfLostReason,
xmoWizardCancellation.xf1stConsultDr,
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
0
Comment
Question by:ahmzb1990
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:BlueYonder
ID: 39180936
It looks like a pivot is needed.  Here are instructions, http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.105).aspx
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39180957
you want to relate your sub-query to another field of your main query/join?

(SELECT COUNT(*) From Activity WHERE Activity.ActivityType = 'Appointment' and YourActivityDateField <= xmoWizardCancellation.CreatedDateTime ) as [Total]
0
 

Author Comment

by:ahmzb1990
ID: 39180998
Thanks

Running the below in my query gave me an accurate number but only when i put the
"AND Activity.ParentLink_RecID = '8E3976615FB146949E0A98D6919E13A5'"
which is only looking at the RECID for my test record - how do i get it to automatically pass in the REC ID so it can do a count on each record?

(SELECT COUNT(*) From Activity
WHERE Activity.ActivityType = 'Appointment'
AND Activity.ParentLink_RecID = '8E3976615FB146949E0A98D6919E13A5'
GROUP BY Activity.ActivityType) AS [Number of C/R],
0
Are You Ransomware's Next Victim?

Worried about ransomware attacks hitting your organization?  The good news is that these attacks are predicable and therefore preventable. Learn more about how you can  stop a ransomware attacks before encryption takes place with WatchGuard Total Security!

 

Author Comment

by:ahmzb1990
ID: 39181016
Thanks emoreau for your response - this is doing a count but the count is invalid - its like its doing a count of all appointments in the whole database and not in the query record by record
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39181017
you need to add aliases in your query:

SELECT DISTINCT
C.xfpt
C.FullName,
C.xfAge as [Age],
C.Source,
WC.xfLostReason,
WC.xf1stConsultDr,
WC.xfInitalSalesPerson as [Initial Sales Person],
C.Owner as [Owner],
WC.xfLastCRAssistant as [Last C/R Assistant],
WC.xfLastPaymentDate as [Last Payment Date],
WC.xfLastPaymentAmount as [Last Payment Amount],
WC.xfWasOnDDR as [DDR?],
WC.xfCurrentMedication as [Current Meds],
WC.CreatedBy as [Cancelled By],
WC.CreatedDateTime as [Cancelled Date],
C.xfPatientStatus as [Status],
(SELECT COUNT(*) From Activity WHERE Activity.ActivityType = 'Appointment' AND Activity.ParentLink_RecID = C.RecId) as [Total]


FROM  dbo.Contact AS C
INNER JOIN dbo.xmoWizardCancellation AS WC
ON C.RecId = WC.xfContactRecID
INNER JOIN dbo.Activity AS A
ON C.RecId = A.ParentLink_RecID
     
WHERE C.xfPatientStatus = 'Test'
AND WC.CreatedDateTime > '2013-05-19 23:59:59.000'
AND WC.CreatedDateTime < '2013-05-20 23:59:59.000'

ORDER BY WC.CreatedDateTime desc
0
 

Author Comment

by:ahmzb1990
ID: 39181030
Thanks emoreau
Worked like a charm :)

Now the next question:)

How do i get this part of my query to a a count for the last 6 months of appointments?

(SELECT COUNT(*) From Activity WHERE Activity.ActivityType = 'Appointment' AND Activity.ParentLink_RecID = C.RecId) as [Total]
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 39181049
(SELECT COUNT(*) From Activity WHERE Activity.ActivityType = 'Appointment' AND Activity.ParentLink_RecID = C.RecId and YourActivityDateField > DATEADD(m, -6, GETDATE()) ) as [Total]
0
 

Author Closing Comment

by:ahmzb1990
ID: 39181086
Thank you - WORKED LIKE A CHARM:D
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39183276
2 suggestions
a. that new subquery would probably perform better inside the from clause
b. the date range filtering could be improved.

as is:
...
    /* this subquery would perform better inside the from clause mot probably */
    , (
        SELECT COUNT(*)
        FROM Activity
        WHERE Activity.ActivityType = 'Appointment'
            AND Activity.ParentLink_RecID = C.RecId
        ) AS [Total]
FROM dbo.Contact AS C
INNER JOIN dbo.xmoWizardCancellation AS WC ON C.RecId = WC.xfContactRecID
INNER JOIN dbo.Activity AS A ON C.RecId = A.ParentLink_RecID
WHERE C.xfPatientStatus = 'Test'
    AND WC.CreatedDateTime > '2013-05-19 23:59:59.000' /* not the best method of date filtering */
    AND WC.CreatedDateTime < '2013-05-20 23:59:59.000'
ORDER BY WC.CreatedDateTime DESC

Open in new window

suggestions
...
    , Y.[Total]
FROM dbo.Contact AS C
INNER JOIN dbo.xmoWizardCancellation AS WC ON C.RecId = WC.xfContactRecID
INNER JOIN dbo.Activity AS A ON C.RecId = A.ParentLink_RecID
LEFT JOIN (
        SELECT ParentLink_RecID, COUNT(*) as [Total]
        FROM Activity
        WHERE Activity.ActivityType = 'Appointment'
        GROUP BY ParentLink_RecID
          ) AS Y ON C.RecId = Y.ParentLink_RecID
WHERE C.xfPatientStatus = 'Test'
    AND WC.CreatedDateTime >= '2013-05-20' /* >= the start of this day */
    AND WC.CreatedDateTime < '2013-05-21'  /* < the next day */
ORDER BY WC.CreatedDateTime DESC

Open in new window

0
 

Author Comment

by:ahmzb1990
ID: 39184263
Thanks mate
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39184438
no problem, hope it helps
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question