Queying dbo.Documents in the Works db

Queying dbo.Documents in the Works db

Postby bbanick » Tue Jun 07, 2011 10:06 am

We are working on an unsigned/unfinalized document report and are struggling with connecting DocumentEvents wit Documents. There are specific events were identify (signing, finalizing, amending etc.) We are able to link them to the document ID at the time of the event but not to a "Master Document ID'.

We do have one option to loop through each original document and create a table of all the NextVersionIDs associated with the original ID but we are hoping there is an easier way to go about it.

Any suggestions are welcome.

Thanks,
Billy Banick
Senior Interface Analyst
ProHealth Physicians
bbanick@prohealthmd.com
Billy Banick
Senior Interface Analyst
ProHealth Physicians
bbanick@prohealthmd.com
bbanick
 
Posts: 2
Joined: Mon Apr 04, 2011 11:31 am

Re: Queying dbo.Documents in the Works db

Postby mike.dow » Tue Jun 07, 2011 10:40 am

Billy,

Take a look at the fnGetLastDocument / fnGetFirstDocument functions, along with the GetDocumentEventsByDocumentID stored procedure.

You could write a query like this that pulls document events, and the current document information.

Code: Select all
SELECT *
FROM
(
   SELECT
      de.*
      ,ded.EntryName
      ,dbo.fnGetLastDocument(de.DocumentID) AS CurrentDocumentID
   FROM DocumentEvent de (NOLOCK)
   INNER JOIN Document_Event_DE ded (NOLOCK) ON de.DocumentEventDE = ded.ID
   WHERE ded.EntryName IN ('Signed', 'Amended', 'Final')
) t
INNER JOIN Document d (NOLOCK) ON d.DocumentID = t.CurrentDocumentID


You'll need to fill in the query quite a bit, but this should get you started.

Hope that helps.

Mike

VP, Technical Services
Galen Healthcare Solutions
Mike Dow
VP, Technical Services
Galen Healthcare Solutions
mike.dow
 
Posts: 16
Joined: Tue Mar 22, 2011 3:49 pm


Return to Tools: Queries, Scripts

Who is online

Users browsing this forum: No registered users and 1 guest