Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Filter out Appts

Hi All,

I would like some help with this query:

I would like to show only any cases that did not have appointments in 14 business days

SELECT  Patient.FirstName AS Pt_FirstName, Patient.LastName AS Pt_LastName, [Case].ServiceEndDate, CaseStatus.Name AS [case Status], Appointment.ActualDateTime,
               Appointment.Id AS ApptID, Appointment.FinanciallyCleared, Appointment.Status AS Appt_status, Appointment.RecvdDate, Appointment.ReplyDate, Appointment.ApptRequestFromDate,
               Appointment.ApptRequestToDate, [Case].AvailabilityFrom, [Case].AvailabilityTo, [Case].ServiceStartDate, [Case].Id, [Case].CaseNbr
FROM     [Case] INNER JOIN
               Patient ON [Case].PatientId = Patient.Id INNER JOIN
               CaseStatus ON [Case].Status = CaseStatus.Id INNER JOIN
               Appointment ON [Case].Id = Appointment.CaseId
WHERE  (Appointment.RecvdDate > CONVERT(DATETIME, '2012-01-01 00:00:00', 102))
0
real9555
Asked:
real9555
1 Solution
 
Paul_Harris_FusionCommented:
So the principle would be to use a NOT EXISTS clause.  Try adapting the following...

e.g.

Select * From Case
where NOT EXISTS
( Select 1 from Appointment where CaseId = Case.Id
  and RecvdDate > GetDate() - 14
)
0
 
real9555Author Commented:
yes, that worked but the filter would not work well:
                   (SELECT  1 AS Expr1
                    FROM     Appointment AS Appointment_1
                    WHERE  (CaseId = [Case].Id) AND (RecvdDate > GETDATE() - 14))) AND (CaseStatus.Name <> 'ended') AND (Appointment.Status <> 'Cancelled') AND
               (Workgroup.Name <> 'Finane') OR
               (CaseStatus.Name <> 'complete')
0
 
Ephraim WangoyaCommented:
Your filter does not seem to be correct
(Workgroup.Name <> 'Finane') OR  (CaseStatus.Name <> 'complete')

You can put it together this way
SELECT  
	Patient.FirstName AS Pt_FirstName, 
	Patient.LastName AS Pt_LastName, 
	[Case].ServiceEndDate, 
	CaseStatus.Name AS [case Status], 
	Appointment.ActualDateTime, 
    Appointment.Id AS ApptID, 
    Appointment.FinanciallyCleared, 
    Appointment.Status AS Appt_status, 
    Appointment.RecvdDate, 
    Appointment.ReplyDate, 
    Appointment.ApptRequestFromDate, 
    Appointment.ApptRequestToDate, 
    [Case].AvailabilityFrom, 
    [Case].AvailabilityTo, 
    [Case].ServiceStartDate, 
    [Case].Id, 
    [Case].CaseNbr
FROM [Case] 
INNER JOIN Patient ON [Case].PatientId = Patient.Id 
INNER JOIN CaseStatus ON [Case].Status = CaseStatus.Id 
INNER JOIN Appointment ON [Case].Id = Appointment.CaseId
where (CaseStatus.Name <> 'ended') 
AND (Appointment.Status <> 'Cancelled') 
AND ((Workgroup.Name <> 'Finane') OR (CaseStatus.Name <> 'complete')) --check this filter
AND NOT EXISTS (SELECT null
                FROM Appointment AS Appointment_1
                WHERE (Appointment_1.CaseId = [Case].Id) 
                AND (Appointment_1.RecvdDate > GETDATE() - 14)) 

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now