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
 General SQL Server Forums
 New to SQL Server Administration
 Suggestions on tuning this stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kshahzad
Starting Member

45 Posts

Posted - 07/18/2013 :  16:56:28  Show Profile  Reply with Quote
USE [myNIC_PROD]
GO
/****** Object:  StoredProcedure [dbo].[NIC_REPORTS_GetReferal]    Script Date: 07/18/2013 20:51:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NIC_REPORTS_GetReferal]
@lPhysician	int,
@lOffice	int,
@SDate	datetime,
@EDate	datetime

AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @lEnterprise int
SELECT @lEnterprise=lEnterprise FROM Office WHERE lID=@lOffice

/*
select b.szlast + ', ' + b.szfirst as szPhysicianName, c.szlast + ', ' + c.szfirst as szPatientName, 
a.dDateCreated, a.dCheckForReport, a.nPriority 
from consultants a, physician b, patient c 
where 
a.lphysician = b.lid 
and a.lphysician in (select lPhysician from map_physiciantooffice where loffice = @lOffice) 
and c.lid in (select lpatient from address where lid = a.lpatientaddress)
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
order by a.dDateCreated, szPatientName, a.nPriority desc
*/

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ap.szfirst + ' ' + ap.szlast as szphysicianname, ap.szorganization as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a  
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN alliedHealthProviders AP ON a.lAlliedProvider=AP.lid
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where a.nRecordStatus=1
	AND DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	AND DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	r.szfirst + ' ' + r.szlast as szphysicianname, r.szdepartment as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToRolodex d ON a.lID = d.lConsultants
	JOIN Rolodex r ON r.lID = d.lRolodex
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a 
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
	JOIN state s on s.lid = d.lDirectoryState 
	JOIN directoryAB ds on ds.lid = d.lDirectory
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directoryab'
	and a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a 
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
	JOIN state s on s.lid = d.lDirectoryState 
	JOIN directorybc ds on ds.lid = d.lDirectory
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directorybc'
	and a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a 
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
	JOIN state s on s.lid = d.lDirectoryState 
	JOIN directorynb ds on ds.lid = d.lDirectory
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directorynb'
	and a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a 
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
	JOIN state s on s.lid = d.lDirectoryState 
	JOIN directoryns ds on ds.lid = d.lDirectory
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directoryns'
	and a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a 
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
	JOIN state s on s.lid = d.lDirectoryState 
	JOIN directoryon ds on ds.lid = d.lDirectory
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directoryon'
	and a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL

UNION

select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, 
	ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedBy
from consultants a 
	INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant
	INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID
	INNER JOIN Office o ON PN.lOffice=o.lID
	Join patient p on p.lid=PN.lPatient
	JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants
	JOIN state s on s.lid = d.lDirectoryState 
	JOIN directorysk ds on ds.lid = d.lDirectory
	LEFT JOIN Physician phys ON a.lPhysician=phys.lID
where s.szDirectoryTableName = 'directorysk'
	and a.nRecordStatus=1
	and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 
	and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 
	AND o.lID=@lOffice
	AND a.dLastPrinted IS NOT NULL
order by szPatientName, a.dDateCreated, szfacility, a.nPriority desc

Edited by - kshahzad on 07/18/2013 17:01:28

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 07/18/2013 :  17:51:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
Same questions that I asked in your other thread...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 07/19/2013 :  16:09:52  Show Profile  Reply with Quote
i can see the execution plan ,

but i am not sure how to pull stats for particular stored procedure?

Thanks
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.06 seconds. Powered By: Snitz Forums 2000