• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

SQL 2005 SubQuery Help Needed

First and foremost, the SQL is handled dynamically by the Server, therefore some of the coding in my WHERE clauses will look odd to you - please disregard this. What I need help with is this select subquery. I know its wrong as I have listed below, but I need to have it check both instances in order to give me a count of 1 or 0. Any help is appreciated!

 'Seen' =  ( SELECT   CASE WHEN a.AppointmentsId IN (
                                                      SELECT    a.AppointmentsId
                                                      FROM      Appointments a
                                                                LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                      WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                AND ISNULL(a.Canceled, 0) = 0
                                                                AND cr.Description = 'Completed' )
                                                 THEN '1'
                                                 ELSE 0
                                            END
                                 )
                                 WHEN pv.PatientVisitId IN (
                                              SELECT    pv.patientvisitid
                                              FROM      PatientVisit pv
                                                        LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitID = pvp.PatientVisitID
                                                        LEFT JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
                                                        LEFT JOIN MedLists ml ON p.CPTProcedureCodeQualifierMId = ml.MedListsID
                                              WHERE     pv.Visit >= ISNULL('02/03/2011', '1/1/1900')
                                                        AND pv.Visit < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000')) )
                                         THEN '1'
                                         ELSE 0
                                    END
                         ),
SET NOCOUNT ON

CREATE TABLE #Summary
    (
      [Date] DATETIME,
      [ResourceStart] DATETIME,
      [ApptStop] DATETIME,
      [TicketNumber] VARCHAR(30),
      [Resource] VARCHAR(60),
      [ResourceId] INT,
      [Facility] VARCHAR(60),
      [FacilityId] INT,
      [DoctorName] VARCHAR(60),
      [ApptType] VARCHAR(50),
      [Patient Name] VARCHAR(90),
      [AppointmentsID] INT,
      [Canceled] INT,
      [Cancel Status] VARCHAR(60),
      [Column] VARCHAR(20),
      [Created] DATETIME,
      [Scheduled] INT,
      [Same Day] INT,
      [Seen] INT,
      [No Show User] INT,
      [No Show Wait Status] INT,
      [CX By Patient] INT,
      [CX By Clinic] INT,
      [CX Patient Left] INT
    ) 
		 
-- Insert Appts
INSERT  INTO #Summary
        SELECT  CONVERT(DATETIME, CONVERT(CHAR(12), a.ApptStart, 1)) AS Date,
                CONVERT(DATETIME, a.ApptStart) AS ResourceStart,
                CONVERT(DATETIME, a.ApptStop) AS ApptStop,
                ISNULL(a.TicketNumber, 'No Ticket') AS TicketNumber,
                dfr.Listname AS Resource,
                a.ResourceId,
                Facility = dff.Listname,
                a.facilityid AS FacilityId,
                dfd.Listname AS DoctorName,
                ISNULL(at.NAME, 'No Appointment Type') AS Type,
                RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix, '')) + ', '
                      + ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) AS 'Patient Name',
                CASE WHEN a.canceled = '1' THEN NULL
                     ELSE a.appointmentsid
                END AS AppointmentsID,
                a.canceled AS Canceled,
                CASE WHEN a.canceled IS NULL THEN NULL
                     ELSE ml.Description
                END AS [Cancel Status],
                CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked'
                     ELSE CAST(aps.ListOrder AS VARCHAR(10))
                END AS [Column],
                a.Created,
                'Scheduled' = ( SELECT  CASE WHEN a.AppointmentsId IN (
                                                  SELECT    a.AppointmentsId
                                                  FROM      Appointments a
                                                  WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                            AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                            	--- AND ISNULL(a.Canceled, 0) = 0 --- Client needs to count appt for count
                                                            AND CONVERT(DATETIME, CONVERT(CHAR(10), a.Created, 101)) <> CONVERT(DATETIME, CONVERT(CHAR(10), a.ApptStart, 101)) )
                                             THEN '1'
                                             ELSE 0
                                        END
                              ),
                'Same Day' = ( SELECT   CASE WHEN a.AppointmentsId IN (
                                                  SELECT    a.AppointmentsId
                                                  FROM      Appointments a
                                                  WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                            AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
        	                                            	--- AND ISNULL(a.Canceled, 0) = 0  --- Client needs to count appt for count
                                                            AND CONVERT(DATETIME, CONVERT(CHAR(10), Created, 101)) = CONVERT(DATETIME, CONVERT(CHAR(10), a.ApptStart, 101)) )
                                             THEN '1'
                                             ELSE 0
                                        END
                             ),
--                'Seen' = ( SELECT   CASE WHEN pv.PatientVisitId IN (
--                                              SELECT    pv.patientvisitid
--                                              FROM      PatientVisit pv
--                                                        LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitID = pvp.PatientVisitID
--                                                        LEFT JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
--                                                        LEFT JOIN MedLists ml ON p.CPTProcedureCodeQualifierMId = ml.MedListsID
--                                              WHERE     pv.Visit >= ISNULL('02/03/2011', '1/1/1900')
--                                                        AND pv.Visit < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000')) )
--                                         THEN '1'
--                                         ELSE 0
--                                    END
--                         ),
                'Seen' =  ( SELECT   CASE WHEN a.AppointmentsId IN (
                                                      SELECT    a.AppointmentsId
                                                      FROM      Appointments a
                                                                LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                      WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                AND ISNULL(a.Canceled, 0) = 0
                                                                AND cr.Description = 'Completed' )
                                                 THEN '1'
                                                 ELSE 0
                                            END
                                 )
                                 WHEN pv.PatientVisitId IN (
                                              SELECT    pv.patientvisitid
                                              FROM      PatientVisit pv
                                                        LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitID = pvp.PatientVisitID
                                                        LEFT JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
                                                        LEFT JOIN MedLists ml ON p.CPTProcedureCodeQualifierMId = ml.MedListsID
                                              WHERE     pv.Visit >= ISNULL('02/03/2011', '1/1/1900')
                                                        AND pv.Visit < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000')) )
                                         THEN '1'
                                         ELSE 0
                                    END
                         ),
                'No Show User' = ( SELECT   CASE WHEN a.AppointmentsId IN (
                                                      SELECT    a.AppointmentsId
                                                      FROM      Appointments a
                                                                LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                      WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                AND ISNULL(a.Canceled, 0) <> 0
                                                                AND cr.Description = 'No Show' )
                                                 THEN '1'
                                                 ELSE 0
                                            END
                                 ),
                'No Show Wait Status' = ( SELECT    CASE WHEN a.AppointmentsId IN ( SELECT  a.AppointmentsId
                                                                                    FROM    Appointments a
                                                                                            LEFT JOIN MedLists status ON status.MedListsId = a.ApptStatusMId
                                                                                    WHERE   a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                                            AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                                            AND DATEDIFF(mi, apptstart, GETDATE()) > 15
                                                                                            AND status.Description IS NULL )
                                                         THEN '1'
                                                         ELSE 0
                                                    END
                                        ),      
                   -- case when status.Description=''No show'' then ''No Show - Canceled'' when datediff(mi,apptstart,getdate())>15 and status.Description is null then ''No Show'' else status.Description end
                'CX By Patient' = ( SELECT  CASE WHEN a.AppointmentsId IN (
                                                      SELECT    a.AppointmentsId
                                                      FROM      Appointments a
                                                                LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                      WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                AND ISNULL(a.Canceled, 0) <> 0
                                                                AND cr.Description = 'Canceled by Patient' )
                                                 THEN '1'
                                                 ELSE 0
                                            END
                                  ),
                'CX By Clinic' = ( SELECT   CASE WHEN a.AppointmentsId IN (
                                                      SELECT    a.AppointmentsId
                                                      FROM      Appointments a
                                                                LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                      WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                AND ISNULL(a.Canceled, 0) <> 0
                                                                AND cr.Description = 'Canceled by Clinic' )
                                                 THEN '1'
                                                 ELSE 0
                                            END
                                 ),
                'CX Patient Left' = ( SELECT    CASE WHEN a.AppointmentsId IN (
                                                          SELECT    a.AppointmentsId
                                                          FROM      Appointments a
                                                                    LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                          WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                    AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                    AND ISNULL(a.Canceled, 0) <> 0
                                                                    AND cr.Description = 'Patient Left Before Seen' )
                                                     THEN '1'
                                                     ELSE 0
                                                END
                                    )
        FROM    Appointments a
                JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
                JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
                JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
                LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
                LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
                LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
                LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId
                LEFT OUTER JOIN PatientVisit pv ON a.PatientVisitId = pv.PatientVisitId
        WHERE   ApptKind = 1
                AND a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                AND a.ApptStart < DATEADD(d, 1,
                                          ISNULL('02/03/2011', '1/1/3000'))
                AND --- Filter on Resource
                ( ( NULL IS NOT NULL
                    AND a.ResourceId IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND --- Filter on Facility
                ( ( NULL IS NOT NULL
                    AND a.FacilityId IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )

SELECT  *
FROM    #Summary  

DROP TABLE #Summary

Open in new window

0
Jeff S
Asked:
Jeff S
2 Solutions
 
derekkrommCommented:
'Seen' =  ( SELECT   CASE WHEN a.AppointmentsId IN (
                                                      SELECT    a.AppointmentsId
                                                      FROM      Appointments a
                                                                LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                                                      WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                                                AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                                                AND ISNULL(a.Canceled, 0) = 0
                                                                AND cr.Description = 'Completed' )
                                                 THEN '1'
                                                 ELSE
                                 CASE
                                 WHEN pv.PatientVisitId IN (
                                              SELECT    pv.patientvisitid
                                              FROM      PatientVisit pv
                                                        LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitID = pvp.PatientVisitID
                                                        LEFT JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
                                                        LEFT JOIN MedLists ml ON p.CPTProcedureCodeQualifierMId = ml.MedListsID
                                              WHERE     pv.Visit >= ISNULL('02/03/2011', '1/1/1900')
                                                        AND pv.Visit < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000')) )
                                         THEN '1'
                                         ELSE 0
                                    END

                                            END
                                 )
                         ),
0
 
dwe761Software EngineerCommented:
Would this give you what you need?:
'Seen' =  
	( SELECT CASE 
		WHEN a.AppointmentsId IN (
                        SELECT    a.AppointmentsId
                        FROM      Appointments a
                                  LEFT JOIN MedLists cr ON a.ApptStatusMID = cr.MedListsID
                        WHERE     a.ApptStart >= ISNULL('02/03/2011', '1/1/1900')
                                  AND a.ApptStart < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000'))
                                  AND ISNULL(a.Canceled, 0) = 0
                                  AND cr.Description = 'Completed' )

		OR pv.PatientVisitId IN (
                                              SELECT    pv.patientvisitid
                                              FROM      PatientVisit pv
                                                        LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitID = pvp.PatientVisitID
                                                        LEFT JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
                                                        LEFT JOIN MedLists ml ON p.CPTProcedureCodeQualifierMId = ml.MedListsID
                                              WHERE     pv.Visit >= ISNULL('02/03/2011', '1/1/1900')
                                                        AND pv.Visit < DATEADD(d, 1, ISNULL('02/03/2011', '1/1/3000')) )

                   THEN '1'
                   ELSE 0
              END
                                 )

Open in new window

0
 
Jeff SAuthor Commented:
Split points to be fair. Thanks to both for the help.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
8080_DiverCommented:
First, let me congratulate you on writing a fairly clear SQL script that takes advantage of indentations in order to improve clarity.

Of course, then I have to slap your wrists because of you use of subselects within in-line subselects. ;-)

Now, let me take a guess at what you are wanting:
If your query finds a "completed" appointment, then the "Seen" would be a 1;
If your query finds a match in the PatientVisitID, then the "Seen" would be a 1;
Otherwise, "Seen" should be 0.

Now, if you'll append the following INNER JOINs (see SQL_1, attached), you can use the logic shown in SQL_2.

On top of giving you the results you want, it will probably perform better. ;-)
-- SQL_1:

INNER JOIN
(SELECT a.AppointmentsId
  ,1 ApptFlAG
FROM Appointments a
LEFT JOIN MedLists cr
ON  a.ApptStatusMID = cr.MedListsID
WHERE a.ApptStart >= ISNULL('02/03/2011','1/1/1900')
    AND a.ApptStart < DATEADD(d,1,ISNULL('02/03/2011','1/1/3000'))
    AND ISNULL(a.Canceled,0) = 0
    AND cr.Description = 'Completed'
) ApptFlags ON ApptFlags.AppointmentsId = a.AppointmentsId 
INNER JOIN
(SELECT pv.patientvisitid
        1 PatVisitFlag 
FROM PatientVisit pv
LEFT JOIN PatientVisitProcs pvp
ON  pv.PatientVisitID = pvp.PatientVisitID
LEFT JOIN Procedures p
ON  pvp.ProceduresID = p.ProceduresID
LEFT JOIN MedLists ml
ON  p.CPTProcedureCodeQualifierMId = ml.MedListsID
WHERE pv.Visit >= ISNULL('02/03/2011','1/1/1900')
    AND pv.Visit < DATEADD(d,1,ISNULL('02/03/2011','1/1/3000'))
) PatVisitFlags


-- SQL_2:

'Seen' = COALESCE(ApptFlAG, PatVisitFlag, 0)

Open in new window

0
 
8080_DiverCommented:
A little quick on the trigger there. ;-)

You still might awant to try my solution.  Expecially, if you compare the query plans. ;-)
0
 
Jeff SAuthor Commented:
I will try to hold out accepting a response for an hour or two in the future. I try to close out my questions fast. My bad and thank you for your response. I will have to open another question just for you to get you the points. LOL. Sorry again.
0
 
8080_DiverCommented:
Jeff,

No worries.

If you follow a similar pattern with the other subselects, you can continue to improve your query's performance.

Also,, I have to wonder about the following

                                       ISNULL('02/03/2011', '1/1/3000'))
                AND --- Filter on Resource
                ( ( NULL IS NOT NULL
                    AND a.ResourceId IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND --- Filter on Facility
                ( ( NULL IS NOT NULL
                    AND a.FacilityId IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )

Open in new window


I am hoping that some of this is just substitutions for parameters.  (E.g. when is '02/03/2011' ever NULL and when is NULL ever NOT NULL? ;-)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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