Solved

SQL 2005 SubQuery Help Needed

Posted on 2011-02-28
7
207 Views
Last Modified: 2012-05-11
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
Comment
Question by:Jeff S
7 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 250 total points
ID: 34998956
'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
 
LVL 10

Accepted Solution

by:
dwe761 earned 250 total points
ID: 34998967
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 34999056
Split points to be fair. Thanks to both for the help.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 34999108
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34999122
A little quick on the trigger there. ;-)

You still might awant to try my solution.  Expecially, if you compare the query plans. ;-)
0
 
LVL 7

Author Comment

by:Jeff S
ID: 34999142
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34999481
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now