INSERT UPDATE SQL Help Needed - 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 2005 Forums

Transact-SQL (2005)

INSERT UPDATE SQL Help Needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2011-05-28 : 14:54:47

Not quite sure how to construct this update / insert SQL but I thought some experts could assist me.

Essentially I need to insert into the AlertNotes a message for each distinct patient found (based on patientprofileid) a message that would take the value in 'GlobalStartDate' and the value in 'GlobalEndDate' and construct a message something like this:

'Global Period 05/09/2011 through 08/07/2011'

The only issue I can see is if the patient already has a note in the AlertNotes field. Can I have it add on to the end of the message thats already in that field.


SELECT   'PatientName' = dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
         'PatientId' = pp.PatientId,
	'PatientProfileId' = pp.PatientProfileId,
         'AlertNotes' = CONVERT(VARCHAR(MAX),pp.AlertNotes),
         'Ticket#' = pv.TicketNumber, 
	'Doctor' = doc.ListName, 
	'Facility' = fac.ListName, 
         MAXDateOfServiceFrom,
        'GlobalStartDate' = pvp.DateOfServiceFrom,
        'GlobalEndDate' = DATEADD(DAY, pvp.GPDays, pvp.DateOfServiceFrom),
        'GlobalLength' = pvp.GPDays
FROM    PatientVisitProcs pvp
        JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
		JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId 
		JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
        JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        JOIN ( SELECT 'PatientId' = pp.PatientId,
                      'MAXDateOfServiceFrom' = MAX(pvp.DateOfServiceFrom)
               FROM   PatientVisitProcs pvp
                      JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
                      JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
               GROUP BY pp.PatientId ) Z ON Z.PatientID = pp.PatientId
                                            AND Z.MAXDateOfServiceFrom = pvp.DateOfServiceFrom
WHERE   ISNULL(GPDays, 0) > 0
        AND DATEADD(DAY, pvp.GPDays, pvp.DateOfServiceFrom) > GETDATE()
        AND ISNULL(pvp.Voided, 0) = 0
GROUP BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
        pp.PatientId,
        pp.PatientProfileId,
        CONVERT(VARCHAR(MAX),pp.AlertNotes),
		pv.TicketNumber,
		doc.ListName, 
		fac.ListName, 
        pvp.DateOfServiceFrom,
        pvp.GPDays,
		Z.MAXDateOfServiceFrom
ORDER BY MAXDateOfServiceFrom DESC, GlobalEndDate DESC ;

--UPDATE dbo.PatientProfile SET AlertNotes = 'Global Period 05/09/2011 through 08/07/2011' WHERE PatientProfileId = 29

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-28 : 15:05:42

Have a look at the merge statement

Use a common table expression to construct a resultset then use the merge to insert/update

;with cte as
(
select PatientProfileid ,
AlertNotes
from ...
)
merge PatientProfile t
using cte t2
on t.PatientProfileid = t2.PatientProfileid
when matched by target then update
set AlertNotes = t.AlertNotes + t2.AlertNotes
when not matched by target then insert
(
PatientProfileid ,
AlertNotes
)
values
(
t2.PatientProfileid ,
t2.AlertNotes
);

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources