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 Programming
 stored procedure changes and business logic review
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

huum
Starting Member

Canada
9 Posts

Posted - 10/02/2013 :  09:21:58  Show Profile  Reply with Quote
Attached are the test results and the codes. I created a test stored procedure called NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2. I executed the test stored procedure and compare it with the result of the original stored procedure (NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS) and both returned the same results. But the revised stored procedure finishes 0 seconds while the old one runs for 2 minutes.



I did a traced and found that the slowness is caused by the following where clause:
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@locallPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@localnRecordStatus
and Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)


So as a solution, which was borrowed from the code for case 50418, was to get the Date Collected first before doing a full database search to get the maximum date for every test.



Please check the code and see if the business logic was affected,



New Stored Procedure


--USE []
--GO

--/****** Object: StoredProcedure [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2] Script Date: 10/01/2013 20:37:25 ******/
--IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2]') AND type in (N'P', N'PC'))
--DROP PROCEDURE [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2]
--GO

USE [myNIC_PROD]
GO

/****** Object: StoredProcedure [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2] Script Date: 10/01/2013 20:37:25 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2]

@lPatient int,
@nRecordStatus int,
@nClassLevel int=5,
@nSecurityLevel int=0,
@lMap_UserToOffice int=0

AS

DECLARE @locallPatient int,
@localnRecordStatus int,
@localnClassLevel int,
@localnSecurityLevel int,
@locallMap_UserToOffice int

SELECT @locallPatient=@lPatient,
@localnRecordStatus=@nRecordStatus,
@localnClassLevel=@nClassLevel,
@localnSecurityLevel=@nSecurityLevel,
@locallMap_UserToOffice=@lMap_UserToOffice




SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN

DECLARE @CollectDates TABLE
( dDateCollected datetime,
lPhysicianLabTest int,
bSignedOff bit,
bAdd2CPP bit
)

INSERT INTO @CollectDates (dDateCollected,lPhysicianLabTest,bSignedOff,bAdd2CPP)
SELECT y.dDateCollected,z.lPhysicianLabTest, y.bSignedOff,Z.bAdd2CPP
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@locallPatient
AND z.nRecordStatus=@localnRecordStatus




Declare @lUser int,@lUserActionMappingTable int
select @luser=lUser from map_userToOffice where lid=@locallMap_UserToOffice
set @lUserActionMappingTable=32


--Disable the AOS SECURITY FOR CPP
if @localnSecurityLevel=2 set @localnSecurityLevel=1


if @locallMap_UserToOffice>0 and @localnSecurityLevel>=2 -- if there is a user and masking is on(bit 1=enabled masking)
begin
-------------
declare @dCurDate datetime, @lOffset int
--set @dCurDate=getDate()-- may need to adjust this for users timezone
exec SESSION_GetUserDate @lUser =@luser ,@dUserDate =@dCurDate OUTPUT,@lOffset=@lOffset OUTPUT--This gets the date in the users timezone


/*
Get the permission ID for this section....
*/
declare @lCustodianPermission int
select @lCustodianPermission =lid from Custodian_Permissions where szcode='CPP'

declare @PhysiciansAccess table (lPhysician int primary key)

insert into @PhysiciansAccess (lPhysician)
select m.lPhysician from map_UserToCustodian m
inner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian=m.lid and cp.lCustodianpermission=@lCustodianPermission
where m.lUser=@luser and m.nRecordStatus=1 and m.bAllPatient=1 and isNull(m.dStartDate,'1900-01-01')<=@dCurDate and isnull(m.dEndDate,'9000-01-01')>=@dCurDate
Union
select lPhysician from map_UserToCustodian m
inner join map_UserToCustodian_Per_patient p on p.lmap_UserToCustodian=m.lid
inner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian_Per_Patient=p.lid and lCustodianpermission=@lCustodianPermission
where lUser=@luser and m.nRecordStatus=1 and p.nRecordStatus=1 and bAllPatient=0 and p.lPatient=@locallPatient
and isNull(p.dStartDate,'1900-01-01')<=@dCurDate and isNull(p.dEndDate,'9000-01-01')>=@dCurDate--only get if the expiry dates are valid
--select * from @PhysiciansAccess
declare @OfficeAccess table (lOffice int primary key)
/*
go get all offices that the patient is a member of
filter out offices that this patient has a custodain and that custodain did not allow access to this user

Note: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess list

NOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.

*/
insert into @OfficeAccess (lOffice) values(0)
insert into @OfficeAccess (lOffice)
select map.loffice from
map_PatientToOffice map
inner join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice
inner join @PhysiciansAccess PA on PA.lphysician=mapc.lPhysician
where map.lpatient=@locallPatient
--and PA.lPhysician is not null
union
select map.loffice from
map_PatientToOffice map
left join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice
where map.lpatient=@locallPatient
and isNull(mapc.lPhysician,0) <=0

-----------


SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected
,Case when exists (select * from
mask_role
inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRole
inner join map_userTOoffice map on map.lid=mapRole.lMap_userToOffice
where mask_role.nMappingTable=@lUserActionMappingTable
and map.lUser=@luser
and mask_role.lRowID=p.lid
and mask_role.nMaskStatus=1
)THEN 1
WHen Mu.lid is not null
THEN 1
else 0
END
as bMask

FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
left Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1
left join @PhysiciansAccess PA on (PA.lPhysician=p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physician
left join @OfficeAccess O on O.loffice=p.loffice--LEFT Join for all the offices the user has access to ALL patient data in that office



WHERE (p.lPatient=@locallPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bSignedOff=1)) = 0
)

or
(p.lPatient=@locallPatient
-- AND p.bSignedOff=1
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bAdd2CPP = 1
)) = 0

)
and isnull(PA.lPhysician,O.lOffice) is not null--Only return records if EITHER of the left joins returned a matching record
ORDER BY b.szDescription

end



else if @locallMap_UserToOffice>0 and @localnSecurityLevel>=1 -- if there is a user and masking is on(bit 1=enabled masking)
begin
SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected
,Case when exists (select * from
mask_role
inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRole
inner join map_userTOoffice map on map.lid=mapRole.lMap_userToOffice
where mask_role.nMappingTable=@lUserActionMappingTable
and map.lUser=@luser
and mask_role.lRowID=p.lid
and mask_role.nMaskStatus=1
)THEN 1
WHen Mu.lid is not null
THEN 1
else 0
END
as bMask

FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
left Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1
WHERE (p.lPatient=@locallPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@locallPatient
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bAdd2CPP = 1
)) = 0
)
ORDER BY b.szDescription

end

else
begin

SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory,0 as bMask, p.dDateCollected
FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
WHERE (p.lPatient=@locallPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@localnRecordStatus

AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@locallPatient
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@localnRecordStatus
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bAdd2CPP = 1
)) = 0
)

ORDER BY b.szDescription
end
END


GO


[k Shaz---Torontonian]

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/02/2013 :  10:07:50  Show Profile  Reply with Quote
the best way to confirm the business logic is consistent old vs. new is to perform regression testing. Preferably on the entire data set.

EDIT:
quote:
Please check the code and see if the business logic was affected
Did you really expect someone to be able to find logic differences when you only posted the new version of the SP?

Be One with the Optimizer
TG

Edited by - TG on 10/02/2013 10:15:06
Go to Top of Page

huum
Starting Member

Canada
9 Posts

Posted - 10/02/2013 :  10:19:38  Show Profile  Reply with Quote
Here is the old version
use[]
--GO

--/****** Object: StoredProcedure [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2] Script Date: 10/01/2013 20:37:25 ******/
--IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2]') AND type in (N'P', N'PC'))
--DROP PROCEDURE [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2]
--GO

USE [myNIC_PROD]
GO

/****** Object: StoredProcedure [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2] Script Date: 10/01/2013 20:37:25 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS_Debug2]

@lPatient int,
@nRecordStatus int,
@nClassLevel int=5,
@nSecurityLevel int=0,
@lMap_UserToOffice int=0

AS

DECLARE @locallPatient int,
@localnRecordStatus int,
@localnClassLevel int,
@localnSecurityLevel int,
@locallMap_UserToOffice int

SELECT @locallPatient=@lPatient,
@localnRecordStatus=@nRecordStatus,
@localnClassLevel=@nClassLevel,
@localnSecurityLevel=@nSecurityLevel,
@locallMap_UserToOffice=@lMap_UserToOffice




SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN

DECLARE @CollectDates TABLE
( dDateCollected datetime,
lPhysicianLabTest int,
bSignedOff bit,
bAdd2CPP bit
)

INSERT INTO @CollectDates (dDateCollected,lPhysicianLabTest,bSignedOff,bAdd2CPP)
SELECT y.dDateCollected,z.lPhysicianLabTest, y.bSignedOff,Z.bAdd2CPP
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@locallPatient
AND z.nRecordStatus=@localnRecordStatus




Declare @lUser int,@lUserActionMappingTable int
select @luser=lUser from map_userToOffice where lid=@locallMap_UserToOffice
set @lUserActionMappingTable=32


--Disable the AOS SECURITY FOR CPP
if @localnSecurityLevel=2 set @localnSecurityLevel=1


if @locallMap_UserToOffice>0 and @localnSecurityLevel>=2 -- if there is a user and masking is on(bit 1=enabled masking)
begin
-------------
declare @dCurDate datetime, @lOffset int
--set @dCurDate=getDate()-- may need to adjust this for users timezone
exec SESSION_GetUserDate @lUser =@luser ,@dUserDate =@dCurDate OUTPUT,@lOffset=@lOffset OUTPUT--This gets the date in the users timezone


/*
Get the permission ID for this section....
*/
declare @lCustodianPermission int
select @lCustodianPermission =lid from Custodian_Permissions where szcode='CPP'

declare @PhysiciansAccess table (lPhysician int primary key)

insert into @PhysiciansAccess (lPhysician)
select m.lPhysician from map_UserToCustodian m
inner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian=m.lid and cp.lCustodianpermission=@lCustodianPermission
where m.lUser=@luser and m.nRecordStatus=1 and m.bAllPatient=1 and isNull(m.dStartDate,'1900-01-01')<=@dCurDate and isnull(m.dEndDate,'9000-01-01')>=@dCurDate
Union
select lPhysician from map_UserToCustodian m
inner join map_UserToCustodian_Per_patient p on p.lmap_UserToCustodian=m.lid
inner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian_Per_Patient=p.lid and lCustodianpermission=@lCustodianPermission
where lUser=@luser and m.nRecordStatus=1 and p.nRecordStatus=1 and bAllPatient=0 and p.lPatient=@locallPatient
and isNull(p.dStartDate,'1900-01-01')<=@dCurDate and isNull(p.dEndDate,'9000-01-01')>=@dCurDate--only get if the expiry dates are valid
--select * from @PhysiciansAccess
declare @OfficeAccess table (lOffice int primary key)
/*
go get all offices that the patient is a member of
filter out offices that this patient has a custodain and that custodain did not allow access to this user

Note: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess list

NOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.

*/
insert into @OfficeAccess (lOffice) values(0)
insert into @OfficeAccess (lOffice)
select map.loffice from
map_PatientToOffice map
inner join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice
inner join @PhysiciansAccess PA on PA.lphysician=mapc.lPhysician
where map.lpatient=@locallPatient
--and PA.lPhysician is not null
union
select map.loffice from
map_PatientToOffice map
left join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice
where map.lpatient=@locallPatient
and isNull(mapc.lPhysician,0) <=0

-----------


SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected
,Case when exists (select * from
mask_role
inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRole
inner join map_userTOoffice map on map.lid=mapRole.lMap_userToOffice
where mask_role.nMappingTable=@lUserActionMappingTable
and map.lUser=@luser
and mask_role.lRowID=p.lid
and mask_role.nMaskStatus=1
)THEN 1
WHen Mu.lid is not null
THEN 1
else 0
END
as bMask

FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
left Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1
left join @PhysiciansAccess PA on (PA.lPhysician=p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physician
left join @OfficeAccess O on O.loffice=p.loffice--LEFT Join for all the offices the user has access to ALL patient data in that office



WHERE (p.lPatient=@locallPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bSignedOff=1)) = 0
)

or
(p.lPatient=@locallPatient
-- AND p.bSignedOff=1
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bAdd2CPP = 1
)) = 0

)
and isnull(PA.lPhysician,O.lOffice) is not null--Only return records if EITHER of the left joins returned a matching record
ORDER BY b.szDescription

end



else if @locallMap_UserToOffice>0 and @localnSecurityLevel>=1 -- if there is a user and masking is on(bit 1=enabled masking)
begin
SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected
,Case when exists (select * from
mask_role
inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRole
inner join map_userTOoffice map on map.lid=mapRole.lMap_userToOffice
where mask_role.nMappingTable=@lUserActionMappingTable
and map.lUser=@luser
and mask_role.lRowID=p.lid
and mask_role.nMaskStatus=1
)THEN 1
WHen Mu.lid is not null
THEN 1
else 0
END
as bMask

FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
left Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1
WHERE (p.lPatient=@locallPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@locallPatient
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@localnRecordStatus
and p.nclassificationlevel>=@localnClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bAdd2CPP = 1
)) = 0
)
ORDER BY b.szDescription

end

else
begin

SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory,0 as bMask, p.dDateCollected
FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
WHERE (p.lPatient=@locallPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@localnRecordStatus

AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@locallPatient
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@localnRecordStatus
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM @CollectDates y
WHERE y.lPhysicianLabTest=a.lPhysicianLabTest
AND y.bAdd2CPP = 1
)) = 0
)

ORDER BY b.szDescription
end
END




GO



[k Shaz---Torontonian]
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/02/2013 :  10:28:15  Show Profile  Reply with Quote
except for this line the two versions are identical ???

--USE []


Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 10/02/2013 :  11:46:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
FROM		dbo.[PhysicianLabReportsTests] AS a 
INNER JOIN	(
			SELECT		lPhysicianLabTest,
					MAX(CASE WHEN bSignedOff = 1 THEN dDateCollected ELSE '19000101' END) AS a,
					MAX(CASE WHEN bAdd2CPP = 1 THEN dDateCollected ELSE '19000101' END) AS b
			FROM		@CollectDates
			GROUP BY	lPhysicianLabTest
		) AS y ON y.lPhysicianLabTest = a.lPhysicianLabTest
INNER JOIN	dbo.[PhysicianLabReports] AS p ON p.lID = a.lLabReport
			AND p.lPatient = @locallPatient
			AND p.nclassificationlevel >= @localnClassLevel
			AND 1 =	CASE
					WHEN p.bSignedOff = 1 AND p.szFiledBy <> 'auto' THEN 1
					WHEN p.bAdd2CPP = 1 AND p.szFiledBy = 'auto' THEN 1
					ELSE 0
				END
INNER JOIN	dbo.[LaboratoryTest] AS b ON b.lID = a.lPhysicianLabTest
LEFT JOIN	dbo.Mask_User AS MU ON MU.lRowID = p.lid 
			AND MU.lUser = @luser
			AND MU.nMappingTable = @lUserActionMappingTable 
			AND MU.nMaskStatus = 1
LEFT JOIN	@PhysiciansAccess AS PA on PA.lPhysician = p.lPhysician
LEFT JOIN	@OfficeAccess AS O on O.loffice = p.loffice
WHERE		a.nRecordStatus = @localnRecordStatus
		AND 0 IN (DATEDIFF(DAY, p.dDateCollected, y.a), DATEDIFF(DAY, p.dDateCollected, y.b))



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

huum
Starting Member

Canada
9 Posts

Posted - 10/02/2013 :  15:11:45  Show Profile  Reply with Quote
HI swepeso

what is this script for?

[k Shaz---Torontonian]
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.12 seconds. Powered By: Snitz Forums 2000