Solved

Help with SQL Query

Posted on 2013-05-20
11
470 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
  • 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 69

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
 

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 69

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

OpenVPN is a great open source VPN server that is capable of providing quick and easy VPN access to your network on the cheap.  By default the software is configured to allow open access to your network.  But what if you want to restrict users to on…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now