Solved

Help with SQL Query

Posted on 2013-05-20
11
474 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 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