Solved

Help with SQL Query

Posted on 2013-05-20
11
476 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 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