I am using Crystal Reports 8.5 My SQL:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.RWJ_PhysicianGroupMembers_SP
(
@JobID int
)
AS
BEGIN
Declare @ReportUserGUID numeric(16,0)
SET NOCOUNT ON
Select @ReportUserGUID = HVCRequestedReport.UserGUID
from HVCRequestedReport
where HVCRequestedReport.Jobid = @jobid
Select sg.Code as SecurityGroupCode,
sg.Description SecurityGroupDesc,
u.LastName as UserLastName,
u.FirstName as UserFirstName
from CV3UserSecurityGroup usg
join CV3SecurityGroup sg on
sg.GUID = usg.SecurityGroupGUID
and sg.active = 1
join CV3User u on
u.GUID = usg.UserGUID
and u.active = 1
where sg.code in
( Select sg2.Code
from CV3UserSecurityGroup usg2
join CV3SecurityGroup sg2
on sg2.GUID = usg2.SecurityGroupGUID
and sg2.Code like 'NB-PG%'
and sg2.active = 1
where usg2.UserGUID = @ReportUserGUID
and (usg2.ExpiryDtm is null or usg2.ExpiryDtm > getdate())
and usg2.active = 1
)
and (usg.ExpiryDtm is null or usg.ExpiryDtm > getdate())
order by sg.code, u.LastName, u.FirstName
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO