SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL 2008 - Update Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JeffS23
Posting Yak Master

210 Posts

Posted - 12/28/2012 :  11:32:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  12:22:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000