stored procedure changes and business logic review

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

General SQL Server Forums

New to SQL Server Programming

stored procedure changes and business logic review

Author

Topic

huum
Starting Member

9 Posts

Posted - 2013-10-02 : 09:21:58

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-02 : 10:07:50

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

huum
Starting Member

9 Posts

Posted - 2013-10-02 : 10:19:38

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]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-02 : 10:28:15

except for this line the two versions are identical ???

--USE []

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-02 : 11:46:13

[code]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))[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

huum
Starting Member

9 Posts

Posted - 2013-10-02 : 15:11:45

HI swepeso

what is this script for?

[k Shaz---Torontonian]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources