Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with SQL Query

Posted on 2013-05-20
11
Medium Priority
?
479 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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 2000 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 49

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 49

Expert Comment

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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

722 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