SQL 2008 - Update Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL 2008 - Update Query Help

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2012-12-28 : 11:32:12

First and foremost, the SQL is handled dynamically by the server, therefore some items in my WHERE clause may look odd to you, please disregard these as they are not an issue.

Per my clients request, they needed to UNION in two other conditions to my update report (/*Patients without a Patient Visit*/) and (/*Patients without a Appointment*/). I need help adding in the patients of these two subsets into my final update query. In its present state, its only adding in the #Temp patients and I need to incorporate the additional patients.

Any help is GREATLY appreciated.


DECLARE @Inactive INT
DECLARE @Active INT
DECLARE @PatientProfileId INT

SELECT
    @Inactive = MedlistsId
FROM
    Medlists
WHERE
    TableName = 'PatientProfileStatus'
    AND Code = 'I'
    
SELECT
    @Active = MedlistsId
FROM
    Medlists
WHERE
    TableName = 'PatientProfileStatus'
    AND Code = 'A'


CREATE TABLE #Temp
    (
      PatientName VARCHAR(120) ,
      PatientProfileId INT ,
      RecentId INT ,
      Recent DATETIME
    )
	
INSERT  INTO #Temp
        SELECT
            dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS Name ,
            pp.PatientProfileId ,
            MAX(pv.PatientVisitId) AS RecentId ,
            MAX(pv.Visit) AS Recent
        FROM
            PatientVisit pv
            INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                                            AND pp.PatientStatusMId = @Active
        WHERE
            pp.PatientProfileId IN ( SELECT
                                        a.OwnerId
                                     FROM
                                        Appointments a
                                        INNER JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
                                                                        AND a.ApptKind = 1
                                                                        AND pp.PatientStatusMId = @Active
                                     GROUP BY
                                        a.OwnerId ,
                                        a.ApptKind
                                     HAVING
                                        MAX(a.ApptStart) < '07/30/2005' )
        GROUP BY
            dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) ,
            pp.PatientProfileId
        HAVING
            MAX(pv.Visit) < '07/30/2005' 
    	

/*Patients without a Appointment*/

IF 1 = 1 
    INSERT  INTO #Temp
            SELECT
                dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS Name ,
                pp.PatientProfileId ,
                NULL AS RecentId ,
                NULL AS Recent
            FROM
                PatientProfile pp
                LEFT JOIN ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' ) ml1 ON pp.PatientStatusMId = ml1.MedlistsId
                LEFT JOIN Appointments a ON a.Ownerid = pp.PatientProfileId
                                            AND a.ApptKind = 1
                LEFT JOIN PatientVisit pv ON a.PatientVisitId = pv.PatientVisitId
            WHERE
                ml1.Code = 'A'
                AND a.ownerid IS NULL
                AND --Filter on Age
                (
                  ((
                     '-1' = '-1'
                     AND '40' = '125'
                   )
                  OR ( CAST(( DATEDIFF(DAY , pp.Birthdate , GETDATE()) / 365.25 ) AS INT) BETWEEN ( '-1' ) AND ( '40' ) ))
                )

/*Patients without a Patient Visit*/

IF 0 = 1 
    INSERT  INTO #Temp
            SELECT
                dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS Name ,
                pp.PatientProfileId ,
                NULL AS RecentId ,
                NULL AS Recent
            FROM
                PatientProfile pp
                LEFT JOIN PatientVisit pv ON pv.PatientProfileid = pp.PatientProfileid
                LEFT JOIN ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' ) ml1 ON pp.PatientStatusMId = ml1.MedlistsId
            WHERE
                ml1.Code = 'A'
                AND pv.patientprofileid IS NULL
                AND --Filter on Age
                (
                  ((
                     '-1' = '-1'
                     AND '40' = '125'
                   )
                  OR ( CAST(( DATEDIFF(DAY , pp.Birthdate , GETDATE()) / 365.25 ) AS INT) BETWEEN ( '-1' ) AND ( '40' ) ))
                )	

    
DECLARE curPatient CURSOR FORWARD_ONLY READ_ONLY LOCAL
FOR
    SELECT
        t.PatientProfileId
    FROM
        #Temp t
        JOIN PatientProfile pp ON t.PatientProfileId = pp.PatientProfileId
        JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId
                                AND pv.PatientVisitId = t.RecentId
    WHERE
        --Filter on Age
        (
          ((
             '-1' = '-1'
             AND '40' = '125'
           )
          OR ( CAST(( DATEDIFF(DAY , pp.Birthdate , GETDATE()) / 365.25 ) AS INT) BETWEEN ( '-1' ) AND ( '40' ) ))
        )
    
OPEN curPatient
FETCH NEXT FROM curPatient INTO @PatientProfileId

WHILE @@FETCH_STATUS = 0 
    BEGIN  
	
        UPDATE
            PatientProfile
        SET 
            PatientStatusMId = @Inactive ,
            pstatus = 'I'
        FROM
            PatientProfile P
            INNER JOIN #Temp t ON t.PatientProfileID = P.PatientProfileID
        WHERE
            p.PatientProfileId = @PatientProfileId 
	   
    
        FETCH NEXT FROM curPatient INTO @PatientProfileId 
    END 
CLOSE curPatient
DEALLOCATE curPatient   

DROP TABLE #Temp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 12:22:25

Why you need cursor? Can't it be done below SET Based Approach after you have insert into #temp table?

UPDATE
            PatientProfile
        SET 
            PatientStatusMId = @Inactive ,
            pstatus = 'I'
        FROM
            PatientProfile P
            INNER JOIN (Select distinct PatientProfileId from #Temp)t ON t.PatientProfileID = P.PatientProfileID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources