Calculating Age in a Stored Procedure

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

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating Age in a Stored Procedure

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-03-03 : 22:17:54

Currently, I'm having an issue with patients under the age of 1 year. I had a patient 9 months old and it calculated 8 weeks. Can anyone see where there may be an error in this?

This is the section pertaining to the "Patients Age" -- Full SP below this.

'308.PatientAge' = CASE WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END

Entire Stored Procedure:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cusMPMSuperbillVail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cusMPMSuperbillVail]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.MPMSuperbill    Script Date: 7/22/2002 1:58:14 PM ******/

CREATE         PROCEDURE cusMPMSuperbillVail @pApptsId int, @pDummy int = NULL AS
BEGIN
SET NOCOUNT ON

/*  	THIS SECTION IS FOR BAR CODE SCANNING WITH THE SUPERBILL 
	TO ENABLE THE BAR CODE FEATURE, SET THE @BarCodeDocumentTypeID TO THE
	PROPER DOCUMENT TYPE ID FOR THE SUPERBILL.
	TO DISABLE THE BAR CODE FEATURE, SET THE @BarCodeDocumentTypeID TO 0 
(ZERO)
*/
DECLARE @BarCodeDocumentTypeID int
SELECT @BarCodeDocumentTypeID = 9


Declare @PatientId varchar(15)
SELECT 	@PatientId = UPPER(PatientId)
FROM 	PatientProfile pp
	JOIN Appointments a on pp.PatientProfileId = a.OwnerID
WHERE 	a.AppointmentsId = @pApptsId 
	and a.ApptKind = 1
	and ISNULL(a.Canceled,0) = 0
	AND PatientId NOT LIKE '%-%'



IF ISNULL(@BarCodeDocumentTypeID,0) <> 0 AND ISNULL(@PatientId,'') <> 
''
	SELECT '510.BarCodeValue' =  '*' + @PatientId + '-' + 
CAST(@BarCodeDocumentTypeID as varchar(15)) + '*'
ELSE
	SELECT '510.BarCodeValue' = NULL


/*	END OF BAR CODE SECTION  */


CREATE TABLE #Contacts_tmp 
(
	id_col		 	Numeric(3,0) identity,
	PatientProfileId 	int 	NULL ,
	First 			varchar (50) NULL ,
	Middle 			varchar (50) NULL ,
	Last 			varchar (50) NULL ,
	Phone1 			varchar (15) NULL ,
	ContactRelToPatient 	varchar (20) NULL,
	Created			datetime NULL
)
	    	
/* Get a ticket number */
DECLARE @ApptSetId int
DECLARE @DoctorId int
DECLARE @CompanyId int
DECLARE @FacilityId int
DECLARE @CasesId int
DECLARE @TicketNumber varchar(30)
DECLARE @AppointmentsId int
DECLARE @ApptStart datetime
DECLARE @ApptStop datetime
DECLARE @PatientProfileId int
DECLARE @ResourceId int

SELECT @ApptSetId = a.ApptSetId, 
			@DoctorId = ISNULL(a.DoctorId, 0),
			@CompanyId = ISNULL(a.CompanyId, 0),
			@FacilityId = ISNULL(a.FacilityId, 0), 
			@CasesId = ISNULL(a.CasesId, 0),
			@TicketNumber = a.TicketNumber,
			@AppointmentsId = a.AppointmentsId,		/* this one is silly?  */
			@ApptStart = a.ApptStart,
			@ApptStop = a.ApptStop, 
			@PatientProfileId = a.OwnerId,
			@ResourceId = a.ResourceId
FROM 		Appointments a 
WHERE	a.AppointmentsId = @pApptsId

IF @TicketNumber IS NULL /* only if we do not already have a ticket 
number */
BEGIN
	EXEC rpGetTicket @FacilityId, @TicketNumber OUTPUT

	/* Write Ticket Number back to all appoinments covered by this 
superbill */
	IF @ApptSetId IS NULL
	BEGIN
		UPDATE Appointments
		SET TicketNumber = @TicketNumber
		WHERE AppointmentsId = @AppointmentsId
	END
	ELSE
	BEGIN
		UPDATE Appointments
		SET TicketNumber = @TicketNumber
		WHERE ApptSetId = @ApptSetId		/* hit entire Billing Set */
		AND (DoctorId = @DoctorId OR DoctorId is null)
		AND (CompanyId = @CompanyId OR CompanyId is null)
		AND (FacilityId = @FacilityId OR FacilityId is null)
		AND (CasesId = @CasesId OR CasesId is null)
		AND TicketNumber IS NULL		/* any member already holding, retains it 
*/
	END
END
IF @CasesId <> 0 exec casPopulateVisitCount @PatientProfileId

/* Check for Attachment Notices */

DECLARE @Attach01Name varchar(200)
DECLARE @Attach01Date datetime
DECLARE @Attach01RenewDays int
DECLARE @Attach01RenewDate datetime
DECLARE @Attach02Name varchar(200)
DECLARE @Attach02Date datetime
DECLARE @Attach02RenewDays int
DECLARE @Attach02RenewDate datetime
DECLARE @Attach03Name varchar(200)
DECLARE @Attach03Date datetime
DECLARE @Attach03RenewDays int
DECLARE @Attach03RenewDate datetime
DECLARE @Attach04Name varchar(200)
DECLARE @Attach04Date datetime
DECLARE @Attach04RenewDays int
DECLARE @Attach04RenewDate datetime
DECLARE @Attach05Name varchar(200)
DECLARE @Attach05Date datetime
DECLARE @Attach05RenewDays int
DECLARE @Attach05RenewDate datetime

SELECT @Attach01Name = IsNull((SELECT Description FROM Medlists WHERE 
TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'A'),'')
SELECT @Attach01Date = (SELECT TOP 1 Created FROM 
PatientProfileAttachment WHERE Name = @Attach01Name And PatientProfileID = @PatientProfileID 
ORDER BY Created DESC)
SELECT @Attach01RenewDays = (SELECT SUBSTRING(DotID,2,10) FROM Medlists 
WHERE TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'A')
SELECT @Attach01RenewDate = IsNull((SELECT 
DATEADD(dd,convert(int,@Attach01RenewDays),@Attach01Date)),'01/01/1980')
if @Attach01Name <> '' AND GETDATE() >= @Attach01RenewDate
	BEGIN
	SELECT @Attach01Name = '1'
	END
SELECT '425.AttachmentA'=IsNull(@Attach01Name,'')

SELECT @Attach02Name = IsNull((SELECT Description FROM Medlists WHERE 
TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'B'),'')
SELECT @Attach02Date = (SELECT TOP 1 Created FROM 
PatientProfileAttachment WHERE Name = @Attach02Name And PatientProfileID = @PatientProfileID 
ORDER BY Created DESC)
SELECT @Attach02RenewDays = (SELECT SUBSTRING(DotID,2,10) FROM Medlists 
WHERE TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'B')
SELECT @Attach02RenewDate = IsNull((SELECT 
DATEADD(dd,convert(int,@Attach02RenewDays),@Attach02Date)),'01/01/1980')
if @Attach02Name <> '' AND GETDATE() >= @Attach02RenewDate
	BEGIN
	SELECT @Attach02Name = '1'
	END
SELECT '426.AttachmentB'=IsNull(@Attach02Name,'')

SELECT @Attach03Name = IsNull((SELECT Description FROM Medlists WHERE 
TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'C'),'')
SELECT @Attach03Date = (SELECT TOP 1 Created FROM 
PatientProfileAttachment WHERE Name = @Attach03Name And PatientProfileID = @PatientProfileID 
ORDER BY Created DESC)
SELECT @Attach03RenewDays = (SELECT SUBSTRING(DotID,2,10) FROM Medlists 
WHERE TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'C')
SELECT @Attach03RenewDate = IsNull((SELECT 
DATEADD(dd,convert(int,@Attach03RenewDays),@Attach03Date)),'01/01/1980')
if @Attach03Name <> '' AND GETDATE() >= @Attach03RenewDate
	BEGIN
	SELECT @Attach03Name = '1'
	END
SELECT '427.AttachmentC'=IsNull(@Attach03Name,'')

SELECT @Attach04Name = IsNull((SELECT Description FROM Medlists WHERE 
TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'D'),'')
SELECT @Attach04Date = (SELECT TOP 1 Created FROM 
PatientProfileAttachment WHERE Name = @Attach04Name And PatientProfileID = @PatientProfileID 
ORDER BY Created DESC)
SELECT @Attach04RenewDays = (SELECT SUBSTRING(DotID,2,10) FROM Medlists 
WHERE TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'D')
SELECT @Attach04RenewDate = IsNull((SELECT 
DATEADD(dd,convert(int,@Attach04RenewDays),@Attach04Date)),'01/01/1980')
if @Attach04Name <> '' AND GETDATE() >= @Attach04RenewDate
	BEGIN
	SELECT @Attach04Name = '1'
	END
SELECT '428.AttachmentD'=IsNull(@Attach04Name,'')

SELECT @Attach05Name = IsNull((SELECT Description FROM Medlists WHERE 
TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'E'),'')
SELECT @Attach05Date = (SELECT TOP 1 Created FROM 
PatientProfileAttachment WHERE Name = @Attach05Name And PatientProfileID = @PatientProfileID 
ORDER BY Created DESC)
SELECT @Attach05RenewDays = (SELECT SUBSTRING(DotID,2,10) FROM Medlists 
WHERE TableName = 'AttachmentNames' AND SUBSTRING(DotID,1,1) = 'E')
SELECT @Attach05RenewDate = IsNull((SELECT 
DATEADD(dd,convert(int,@Attach05RenewDays),@Attach05Date)),'01/01/1980')
if @Attach05Name <> '' AND GETDATE() >= @Attach05RenewDate
	BEGIN
	SELECT @Attach05Name = '1'
	END
SELECT '429.AttachmentE'=IsNull(@Attach05Name,'')

/* Create Problem List */

DECLARE @LastPatientVisitId int
DECLARE @LastVisitDate datetime

SELECT TOP 1	@LastPatientVisitId = pv.PatientVisitId, @LastVisitDate = 
pv.Visit
FROM		PatientVisit pv
WHERE		pv.Visit < DATEADD(day, 1, ISNULL(@ApptStop, '1/1/3000'))
		AND pv.PatientProfileId = @PatientProfileId
--		AND pv.CompanyId = @CompanyId
ORDER BY	pv.Visit DESC

SELECT  '388.Diag1Code'=IsNull(pvd.ICD9Code,''),
        '389.Diag1Description'=IsNull(pvd.Description,'')
FROM 	PatientVisitDiags pvd
WHERE 	pvd.PatientVisitId = @LastPatientVisitId AND
        pvd.ListOrder = 1

SELECT  '390.Diag2Code'=IsNull(pvd.ICD9Code,''),
        '391.Diag2Description'=IsNull(pvd.Description,'')
FROM 	PatientVisitDiags pvd
WHERE 	pvd.PatientVisitId = @LastPatientVisitId AND
        pvd.ListOrder = 2

SELECT  '392.Diag3Code'=IsNull(pvd.ICD9Code,''),
        '393.Diag3Description'=IsNull(pvd.Description,'')
FROM 	PatientVisitDiags pvd
WHERE 	pvd.PatientVisitId = @LastPatientVisitId AND
        pvd.ListOrder = 3

SELECT  '394.Diag4Code'=IsNull(pvd.ICD9Code,''),
        '395.Diag4Description'=IsNull(pvd.Description,'')
FROM 	PatientVisitDiags pvd
WHERE 	pvd.PatientVisitId = @LastPatientVisitId AND
        pvd.ListOrder = 4

SELECT  '396.Diag5Code'=IsNull(pvd.ICD9Code,''),
        '397.Diag5Description'=IsNull(pvd.Description,'')
FROM 	PatientVisitDiags pvd
WHERE 	pvd.PatientVisitId = @LastPatientVisitId AND
        pvd.ListOrder = 5

SELECT  '398.Diag6Code'=IsNull(pvd.ICD9Code,''),
        '399.Diag6Description'=IsNull(pvd.Description,'')
FROM 	PatientVisitDiags pvd
WHERE 	pvd.PatientVisitId = @LastPatientVisitId AND
        pvd.ListOrder = 6


SELECT '400.ReportID'=1

CREATE TABLE #Diagnosis_Freq
(
	Frequency		int	NULL,
	PatientProfileID	int	NULL,
	ListOrder		int	NULL,
	ICD9Code		varchar(50)	NULL,
	Description		varchar(250)	NULL
)

INSERT	#Diagnosis_Freq
SELECT COUNT(*) as 'Freq',pp.PatientProfileid,pvd.ListOrder, 
pvd.ICD9Code,pvd.Description
FROM PatientProfile pp
	LEFT JOIN PatientVisit pv on pp.PatientProfileID = pv.PatientProfileID
	LEFT JOIN PatientVisitDiags pvd on pv.PatientVisitID = 
pvd.PatientVisitID
WHERE	pp.PatientProfileID = @PatientProfileId AND pvd.ICD9Code <> ''
GROUP BY pp.PatientProfileid,pvd.ListOrder, 
pvd.ICD9Code,pvd.Description

CREATE TABLE #Diagnosis_Freq1
(
	id_col		 	Numeric(3,0) identity,
	Frequency		int	NULL,
	PatientProfileID	int	NULL,
	ListOrder		int	NULL,
	ICD9Code		varchar(50)	NULL,
	Description		varchar(250)	NULL
)

INSERT	#Diagnosis_Freq1
SELECT TOP 10 Frequency,PatientProfileID,ListOrder,ICD9Code,Description 
FROM #Diagnosis_Freq ORDER BY PatientProfileID, ListOrder, Frequency 
DESC

SELECT  '460.Diag1FFreq'=IsNull(dfq.Frequency,''),
	'461.Diag1FListOrder'=Isnull(dfq.ListOrder,''),
	'462.Diag1FCode'=IsNull(dfq.ICD9Code,''),
        '463.Diag1FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 1

SELECT  '464.Diag2FFreq'=IsNull(dfq.Frequency,''),
	'465.Diag2FListOrder'=Isnull(dfq.ListOrder,''),
	'466.Diag2FCode'=IsNull(dfq.ICD9Code,''),
        '467.Diag2FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 2

SELECT  '468.Diag3FFreq'=IsNull(dfq.Frequency,''),
	'469.Diag3FListOrder'=Isnull(dfq.ListOrder,''),
	'470.Diag3FCode'=IsNull(dfq.ICD9Code,''),
        '471.Diag3FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 3

SELECT  '472.Diag4FFreq'=IsNull(dfq.Frequency,''),
	'473.Diag4FListOrder'=Isnull(dfq.ListOrder,''),
	'474.Diag4FCode'=IsNull(dfq.ICD9Code,''),
        '475.Diag4FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 4

SELECT  '476.Diag5FFreq'=IsNull(dfq.Frequency,''),
	'477.Diag5FListOrder'=Isnull(dfq.ListOrder,''),
	'478.Diag5FCode'=IsNull(dfq.ICD9Code,''),
        '479.Diag5FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 5

SELECT  '480.Diag6FFreq'=IsNull(dfq.Frequency,''),
	'481.Diag6FListOrder'=Isnull(dfq.ListOrder,''),
	'482.Diag6FCode'=IsNull(dfq.ICD9Code,''),
        '483.Diag6FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 6

SELECT  '484.Diag7FFreq'=IsNull(dfq.Frequency,''),
	'485.Diag7FListOrder'=Isnull(dfq.ListOrder,''),
	'486.Diag7FCode'=IsNull(dfq.ICD9Code,''),
        '487.Diag7FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 7

SELECT  '488.Diag8FFreq'=IsNull(dfq.Frequency,''),
	'489.Diag8FListOrder'=Isnull(dfq.ListOrder,''),
	'490.Diag8FCode'=IsNull(dfq.ICD9Code,''),
        '491.Diag8FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 8

SELECT  '492.Diag9FFreq'=IsNull(dfq.Frequency,''),
	'493.Diag9FListOrder'=Isnull(dfq.ListOrder,''),
	'494.Diag9FCode'=IsNull(dfq.ICD9Code,''),
        '495.Diag9FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 9

SELECT  '496.Diag10FFreq'=IsNull(dfq.Frequency,''),
	'497.Diag10FListOrder'=Isnull(dfq.ListOrder,''),
	'498.Diag10FCode'=IsNull(dfq.ICD9Code,''),
        '499.Diag10FDescription'=IsNull(dfq.Description,'')
FROM 	#Diagnosis_Freq1 dfq
WHERE 	dfq.id_col = 10

DROP TABLE #Diagnosis_Freq
DROP TABLE #Diagnosis_Freq1

/* Create a table to store the cases insurance information and change 
nulls to 0 for speed */
DECLARE @tblCasesInsurance  table
(
	[CasesId] [int] NULL ,
	[PatientProfileId] [int] NULL ,
	[OrderForClaims] [smallint] NULL ,
	[PatientInsuranceId] [int] NULL ,
	[InsuranceCarriersId] [int] NULL 

)

INSERT into @tblCasesInsurance 
	(CasesID, 
	PatientProfileId, 
	OrderForClaims, 
	PatientInsuranceId,
	InsuranceCarriersId)
SELECT
	ISNULL( ci.CasesID,0),
	ci.PatientProfileId, 
	ci.OrderForClaims, 
	ci.PatientInsuranceId, 
	ci.InsuranceCarriersId
FROM
	CasesInsurance ci
WHERE
	ci.PatientProfileId = @PatientProfileId AND
	(ci.Inactive is Null or ci.Inactive = 0) 



SELECT  '10.Carrier1Name'=IsNull(ic.Name,''),
        '11.Carrier1ListName'=IsNull(ic.Listname,''),
        '12.Carrier1Address1'=IsNull(ic.Address1,''),
        '13.Carrier1Address2'=IsNull(ic.Address2,''),
        '14.Carrier1City'=IsNull(ic.City,''),
        '15.Carrier1State'=IsNull(ic.State,''),
        '16.Carrier1Zip'=IsNull(ic.Zip,''),
	'17.Carrier1Country' = ISNULL(ic.Country,''),
        '18.Carrier1Contact'=IsNull(ic.Contact,''),
        '19.Carrier1Phone'=IsNull(ic.Phone1,''),
        '20.Carrier1FinancialClass'=IsNull(ml.Description,''),
        '21.Carrier1AllocationType'=IsNull(al.Name,''),
        '22.Carrier1CarrierType'=IsNull(ml3.Description,''),
        '23.Carrier1PolicyType'=IsNull(ml4.Description,''),
        '24.Carrier1GroupId'=IsNull(ic.GroupId,''),
        '25.Carrier1GroupName'=IsNull(ic.GroupName,''),
	'26.Carrier1InsuranceGroup' = Isnull(ig.Name,''),
        '110.Insured1SameAsPatient'=IsNull(pi.InsuredSameAsPatient,0),
	'111.Insured1SameAsGuarantor'=IsNull(pi.InsuredSameAsGuarantor,0),
        '112.Insured1PatRelToInsured'=IsNull(ml2.Description,''),
        '113.Insured1First'=IsNull(pi.First,''),
        '114.Insured1Last'=IsNull(pi.Last,''),
        '115.Insured1Middle'=IsNull(pi.Middle,''),
        '116.Insured1Address1'=IsNull(pi.Address1,''),
        '117.Insured1Address2'=IsNull(pi.Address2,''),
        '118.Insured1City'=IsNull(pi.City,''),
        '119.Insured1State'=IsNull(pi.State,''),
        '120.Insured1Zip'=IsNull(pi.Zip,''),
	'121.Insured1Country' = IsNull(ic.Country,''),
        '122.Insured1Phone'=IsNull(pi.Phone1,''),
        '123.Insured1ID'=IsNull(pi.InsuredID,''),
	'124.Insured1Name'=IsNull(pi.Prefix+' ','') + Isnull(pi.First,'') + Isnull(' '+pi.Middle,'') + Isnull(' '+pi.Last,'') + Isnull(' '+pi.Suffix,''),
	'125.Insured1Notes'=CAST( IsNull(pi.EligibilityNotes,'') as Varchar(255) ) 
FROM  	@tblCasesInsurance ci
	JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
	INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId=ic.InsuranceCarriersId 
	LEFT JOIN InsuranceGroup ig on ic.InsuranceGroupId = ig.InsuranceGroupId
	LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId 
	LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
 	LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId 
	LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId 
	LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId 
WHERE	ci.CasesId = ISNULL(@CasesId, 0) AND
	ci.PatientProfileId = @PatientProfileId AND
	ci.OrderForClaims = 1

SELECT  '30.Carrier2Name'=IsNull(ic.Name,''),
        '31.Carrier2ListName'=IsNull(ic.ListName,''),
        '32.Carrier2Address1'=IsNull(ic.Address1,''),
        '33.Carrier2Address2'=IsNull(ic.Address2,''),
        '34.Carrier2City'=IsNull(ic.City,''),
        '35.Carrier2State'=IsNull(ic.State,''),
        '36.Carrier2Zip'=IsNull(ic.Zip,''),
	'37.Carrier2Country' = ISNULL(ic.Country,''),
        '38.Carrier2Contact'=IsNull(ic.Contact,''),
        '39.Carrier2Phone'=IsNull(ic.Phone1,''),
        '40.Carrier2FinancialClass'=IsNull(ml.Description,''),
        '41.Carrier2AllocationType'=IsNull(al.Name,''),
        '42.Carrier2CarrierType'=IsNull(ml3.Description,''),
        '43.Carrier2PolicyType'=IsNull(ml4.Description,''),
        '44.Carrier2GroupId'=IsNull(ic.GroupId,''),
        '45.Carrier2GroupName'=IsNull(ic.GroupName,''),
	'46.Carrier2InsuranceGroup' = IsNull(ig.Name,''),
        '130.Insured2SameAsPatient'=IsNull(pi.InsuredSameAsPatient,0),
	'131.Insured2SameAsGuarantor'=IsNull(pi.InsuredSameAsGuarantor,0),
        '132.Insured2PatRelToInsured'=IsNull(ml2.Description,''),
        '133.Insured2First'=IsNull(pi.First,''),
        '134.Insured2Last'=IsNull(pi.Last,''),
        '135.Insured2Middle'=IsNull(pi.Middle,''),
        '136.Insured2Address1'=IsNull(pi.Address1,''),
        '137.Insured2Address2'=IsNull(pi.Address2,''),
        '138.Insured2City'=IsNull(pi.City,''),
        '139.Insured2State'=IsNull(pi.State,''),
        '140.Insured2Zip'=IsNull(pi.Zip,''),
	'141.Insured2Country' = ISNULL(pi.Country, ''),
        '142.Insured2Phone'=IsNull(pi.Phone1,''),
        '143.Insured2ID'=IsNull(pi.InsuredID,''),
	'144.Insured2Name'=IsNull(pi.Prefix+' ','') + Isnull(pi.First,'') + Isnull(' '+pi.Middle,'') + Isnull(' '+pi.Last,'') + Isnull(' '+pi.Suffix,''),
	'145.Insured2Notes'=CAST( IsNull(pi.EligibilityNotes,'') as Varchar(255) ) 
FROM  	@tblCasesInsurance ci
	JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
	INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId=ic.InsuranceCarriersId 
	LEFT JOIN InsuranceGroup ig on ic.InsuranceGroupId = ig.InsuranceGroupId
	LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId 
	LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
 	LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId 
	LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId 
	LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId 
WHERE	ci.CasesId = ISNULL(@CasesId, 0) AND
	ci.PatientProfileId = @PatientProfileId AND
 	ci.OrderForClaims = 2

SELECT  '50.Carrier3Name'=IsNull(ic.Name,''),
        '51.Carrier3ListName'=IsNull(ic.ListName,''),
        '52.Carrier3Address1'=IsNull(ic.Address1,''),
        '53.Carrier3Address2'=IsNull(ic.Address2,''),
        '54.Carrier3City'=IsNull(ic.City,''),
        '55.Carrier3State'=IsNull(ic.State,''),
        '56.Carrier3Zip'=IsNull(ic.Zip,''),
	'57.Carrier3Country' = ISNULL(ic.Country,''),
        '58.Carrier3Contact'=IsNull(ic.Contact,''),
        '59.Carrier3Phone'=IsNull(ic.Phone1,''),
        '60.Carrier3FinancialClass'=IsNull(ml.Description,''),
        '61.Carrier3AllocationType'=IsNull(al.Name,''),
        '62.Carrier3CarrierType'=IsNull(ml3.Description,''),
        '63.Carrier3PolicyType'=IsNull(ml4.Description,''),
        '64.Carrier3GroupId'=IsNull(ic.GroupId,''),
        '65.Carrier3GroupName'=IsNull(ic.GroupName,''),
	'66.Carrier3InsuranceGroup' = Isnull(ig.Name,''),
        '150.Insured3SameAsPatient'=IsNull(pi.InsuredSameAsPatient,0),
	'151.Insured3SameAsGuarantor'=IsNull(pi.InsuredSameAsGuarantor,0),
        '152.Insured3PatRelToInsured'=IsNull(ml2.Description,''),
        '153.Insured3First'=IsNull(pi.First,''),
        '154.Insured3Last'=IsNull(pi.Last,''),
        '155.Insured3Middle'=IsNull(pi.Middle,''),
        '156.Insured3Address1'=IsNull(pi.Address1,''),
        '157.Insured3Address2'=IsNull(pi.Address2,''),
        '158.Insured3City'=IsNull(pi.City,''),
        '159.Insured3State'=IsNull(pi.State,''),
        '160.Insured3Zip'=IsNull(pi.Zip,''),
        '161.Insured3Country' = ISNULL(pi.Country, ''),
        '162.Insured3Phone'=IsNull(pi.Phone1,''),
        '163.Insured3ID'=IsNull(pi.InsuredID,''),
	'164.Insured3Name'=IsNull(pi.Prefix+' ','') + Isnull(pi.First,'') + Isnull(' '+pi.Middle,'') + Isnull(' '+pi.Last,'') + Isnull(' '+pi.Suffix,''),
	'165.Insured3Notes'=CAST( IsNull(pi.EligibilityNotes,'') as Varchar(255) ) 
FROM  	@tblCasesInsurance ci
	JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
	INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId=ic.InsuranceCarriersId 
	LEFT JOIN InsuranceGroup ig on ic.InsuranceGroupId = ig.InsuranceGroupId
	LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId 
	LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
 	LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId 
	LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId 
	LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId 
WHERE	ci.CasesId = ISNULL(@CasesId, 0) AND
	ci.PatientProfileId = @PatientProfileId AND
 	ci.OrderForClaims = 3

SELECT  '70.Carrier4Name'=IsNull(ic.Name,''),
        '71.Carrier4ListName'=IsNull(ic.ListName,''),
        '72.Carrier4Address1'=IsNull(ic.Address1,''),
        '73.Carrier4Address2'=IsNull(ic.Address2,''),
        '74.Carrier4City'=IsNull(ic.City,''),
        '75.Carrier4State'=IsNull(ic.State,''),
        '76.Carrier4Zip'=IsNull(ic.Zip,''),
	'77.Carrier4Country' = ISNULL(ic.Country,''),
        '78.Carrier4Contact'=IsNull(ic.Contact,''),
        '79.Carrier4Phone'=IsNull(ic.Phone1,''),
        '80.Carrier4FinancialClass'=IsNull(ml.Description,''),
        '81.Carrier4AllocationType'=IsNull(al.Name,''),
        '82.Carrier4CarrierType'=IsNull(ml3.Description,''),
        '83.Carrier4PolicyType'=IsNull(ml4.Description,''),
        '84.Carrier4GroupId'=IsNull(ic.GroupId,''),
        '85.Carrier4GroupName'=IsNull(ic.GroupName,''),
	'86.Carrier4InsuranceGroup' = Isnull(ig.Name,''),
        '170.Insured4SameAsPatient'=IsNull(pi.InsuredSameAsPatient,0),
	'171.Insured4SameAsGuarantor'=IsNull(pi.InsuredSameAsGuarantor,0),
        '172.Insured4PatRelToInsured'=IsNull(ml2.Description,''),
        '173.Insured4First'=IsNull(pi.First,''),
        '174.Insured4Last'=IsNull(pi.Last,''),
        '175.Insured4Middle'=IsNull(pi.Middle,''),
        '176.Insured4Address1'=IsNull(pi.Address1,''),
        '177.Insured4Address2'=IsNull(pi.Address2,''),
        '178.Insured4City'=IsNull(pi.City,''),
        '179.Insured4State'=IsNull(pi.State,''),
        '180.Insured4Zip'=IsNull(pi.Zip,''),
	'181.Insured4Country' = ISNULL(pi.Country, ''),
        '182.Insured4Phone'=IsNull(pi.Phone1,''),
        '183.Insured4ID'=IsNull(pi.InsuredID,''),
	'184.Insured4Name'=IsNull(pi.Prefix+' ','') + Isnull(pi.First,'') + Isnull(' '+pi.Middle,'') + Isnull(' '+pi.Last,'') + Isnull(' '+pi.Suffix,''),
	'185.Insured4Notes'=CAST( IsNull(pi.EligibilityNotes,'') as Varchar(255) ) 
FROM  	@tblCasesInsurance ci
	JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
	INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId=ic.InsuranceCarriersId 
	LEFT JOIN InsuranceGroup ig on ic.InsuranceGroupId = ig.InsuranceGroupId
	LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId 
	LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
 	LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId 
	LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId 
	LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId 
WHERE	ci.CasesId = ISNULL(@CasesId, 0) AND
	ci.PatientProfileId = @PatientProfileId AND
 	ci.OrderForClaims = 4

SELECT  '90.Carrier5Name'=IsNull(ic.Name,''),
        '91.Carrier5ListName'=IsNull(ic.ListName,''),
        '92.Carrier5Address1'=IsNull(ic.Address1,''),
        '93.Carrier5Address2'=IsNull(ic.Address2,''),
        '94.Carrier5City'=IsNull(ic.City,''),
        '95.Carrier5State'=IsNull(ic.State,''),
        '96.Carrier5Zip'=IsNull(ic.Zip,''),
	'97.Carrier5Country' = ISNULL(ic.Country,''),
	'98.Carrier5Contact'=IsNull(ic.Contact,''),
        '99.Carrier5Phone'=IsNull(ic.Phone1,''),
        '100.Carrier5FinancialClass'=IsNull(ml.Description,''),
        '101.Carrier5AllocationType'=IsNull(al.Name,''),
        '102.Carrier5CarrierType'=IsNull(ml3.Description,''),
        '103.Carrier5PolicyType'=IsNull(ml4.Description,''),
        '104.Carrier5GroupId'=IsNull(ic.GroupId,''),
        '105.Carrier5GroupName'=IsNull(ic.GroupName,''),
	'106.Carrier5InsuranceGroup' = Isnull(ig.Name,''),
        '190.Insured5SameAsPatient'=IsNull(pi.InsuredSameAsPatient,0),
	'191.Insured5SameAsGuarantor'=IsNull(pi.InsuredSameAsGuarantor,0),
        '192.Insured5PatRelToInsured'=IsNull(ml2.Description,''),
        '193.Insured5First'=IsNull(pi.First,''),
        '194.Insured5Last'=IsNull(pi.Last,''),
        '195.Insured5Middle'=IsNull(pi.Middle,''),
        '196.Insured5Address1'=IsNull(pi.Address1,''),
        '197.Insured5Address2'=IsNull(pi.Address2,''),
        '198.Insured5City'=IsNull(pi.City,''),
        '199.Insured5State'=IsNull(pi.State,''),
        '200.Insured5Zip'=IsNull(pi.Zip,''),
	'201.Insured5Country' = ISNULL(pi.Country, ''),
        '202.Insured5Phone'=IsNull(pi.Phone1,''),
        '203.Insured5ID'=IsNull(pi.InsuredID,''),
	'204.Insured5Name'=IsNull(pi.Prefix+' ','') + Isnull(pi.First,'') + Isnull(' '+pi.Middle,'') + Isnull(' '+pi.Last,'') + Isnull(' '+pi.Suffix,''),
	'205.Insured5Notes'=CAST( IsNull(pi.EligibilityNotes,'') as Varchar(255) ) 
FROM  	@tblCasesInsurance ci
	JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
	INNER JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId=ic.InsuranceCarriersId 
	LEFT JOIN InsuranceGroup ig on ic.InsuranceGroupId = ig.InsuranceGroupId
	LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId 
	LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
 	LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId 
	LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId 
	LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId 
WHERE	ci.CasesId = ISNULL(@CasesId, 0) AND
	ci.PatientProfileId = @PatientProfileId AND
 	ci.OrderForClaims = 5

INSERT 		#Contacts_tmp
SELECT		PatientProfileId, First, Middle, Last, 
		Phone1, ContactRelToPatient, Created
FROM 		PatientContacts pc
WHERE 		pc.PatientProfileId = @PatientProfileId 
ORDER BY 	Created

SELECT	'210.Contact1First' = IsNull(First,''),
	'211.Contact1Middle' = IsNull(Middle,''),
	'212.Contact1Last' = IsNull(Last,''),
	'213.Contact1Phone' = IsNull(Phone1,''),
	'214.Contact1RelToPatient' = IsNull(ContactRelToPatient,'')
FROM 	#Contacts_tmp
WHERE 	id_col = 1

SELECT	'215.Contact2First' = IsNull(First,''),
	'216.Contact2Middle' = IsNull(Middle,''),
	'217.Contact2Last' = IsNull(Last,''),
	'218.Contact2Phone' = IsNull(Phone1,''),
	'219.Contact2RelToPatient' = IsNull(ContactRelToPatient,'')
FROM 	#Contacts_tmp
WHERE 	id_col = 2

SELECT	'220.Contact3First' = IsNull(First,''),
	'221.Contact3Middle' = IsNull(Middle,''),
	'222.Contact3Last' = IsNull(Last,''),
	'223.Contact3Phone' = IsNull(Phone1,''),
	'224.Contact3RelToPatient' = IsNull(ContactRelToPatient,'')
FROM 	#Contacts_tmp
WHERE 	id_col = 3


SELECT  '231.DoctorID'=@DoctorID 

SELECT  '232.Doctor'=IsNull(df.First,'') + ' ' + isnull(df.Middle,'') + ' ' + isnull(df.Last,'') + ' ' + isnull(df.Suffix,''), 
        '233.DoctorFirst'=IsNull(df.First,''),
        '234.DoctorMiddle'=IsNull(df.Middle,''),
        '235.DoctorLast'=IsNull(df.Last,''),
        '236.DoctorSuffix'=IsNull(df.Suffix,''),
        '237.LiscenseNo'=IsNull(df.StateLicenseNo,''),
        '238.UPIN'=IsNull(df.UPIN,''),
        '239.FederalTaxID'=IsNull(df.FederalTaxID,''),
	'247.DoctorTreat'=IsNull(df.Listname,'')
FROM 	DoctorFacility df
WHERE	df.DoctorFacilityId = @DoctorId

SELECT	'248.Resource' = IsNull(df.Listname,'')
FROM 	DoctorFacility df
WHERE	df.DoctorFacilityId = @ResourceId

SELECT  '240.Facility'=IsNull(df2.ListName,''),
        '241.FacilityAddr1'=IsNull(df2.Address1,''),
        '242.FacilityAddr2'=IsNull(df2.Address2,''), 
        '243.FacilityCity'=IsNull(df2.City,''),
        '244.FacilityState'=IsNull(df2.State,''),
        '245.FacilityZip'=IsNull(df2.Zip,''),
        '246.FacilityPhone'=IsNull(dbo.FormatPhone(df2.Phone1,1),'')
FROM    DoctorFacility df2
WHERE	df2.DoctorFacilityID = @FacilityId
		
SELECT  '250.DoctorRecord' = IsNull(d.ListName,'')
FROM	PatientProfile pp INNER JOIN DoctorFacility d ON pp.DoctorId = 
d.DoctorFacilityId
WHERE	pp.PatientProfileId = @PatientProfileId
			

SELECT  '255.AppointmentsID'=a.AppointmentsID, 
        '256.ApptStart' = a.ApptStart, 
        '257.StartHour'=IsNull(convert(varchar(2),datepart(hour,a.ApptStart)),''),
        '258.StartMinute'=IsNull(convert(varchar(2),datepart(minute,a.ApptStart)),''),
        '259.StopHour'=IsNull(convert(varchar(2),datepart(hour,a.ApptStop)),''),
        '260.StopMinute'=IsNull(convert(varchar(2),datepart(minute,a.ApptStop)),''),
        '261.TicketNumber'=IsNull(@TicketNumber,'')

FROM    Appointments a

WHERE   a.AppointmentsId = @AppointmentsId

DECLARE @InsDeposit money, @PatDeposit money, @InsBalance0 money, 
@PatBalance0 money,@InsBalance30 money, @PatBalance30 money,@InsBalance60 
money, @PatBalance60 money,@InsBalance90 money, @PatBalance90 
money,@InsBalance120 money, @PatBalance120 money, @InsBalance money, @PatBalance 
money
DECLARE @DB varchar(128)
SELECT @DB = DB_NAME()

exec master..mbcxp_AgingSummary70 @DB, @PatientProfileId, 1, NULL, 1, 
NULL, NULL, NULL, @InsDeposit OUTPUT, @PatDeposit OUTPUT,@InsBalance0 
OUTPUT, @PatBalance0 OUTPUT, @InsBalance30 OUTPUT, @PatBalance30 
OUTPUT,@InsBalance60 OUTPUT, @PatBalance60 OUTPUT, @InsBalance90 OUTPUT, 
@PatBalance90 OUTPUT, @InsBalance120 OUTPUT, @PatBalance120 OUTPUT, 
@InsBalance OUTPUT,@PatBalance OUTPUT

SELECT  '270.PatientBalance'=IsNull(ppa.PatBalance,0),
        '271.PatientBalance0'=IsNull(@PatBalance0,0),
        '272.PatientBalance30'=IsNull(@PatBalance30,0),
        '273.PatientBalance60'=IsNull(@PatBalance60,0),
        '274.PatientBalance90'=IsNull(@PatBalance90,0),
        '275.PatientBalance120'=IsNull(@PatBalance120,0),
        '276.InsuranceBalance'=IsNull(ppa.InsBalance,0) 
FROM    PatientProfileAgg ppa
WHERE   ppa.PatientProfileID = @PatientProfileID

SELECT  '262.AppointmentType'=IsNull(MAX(isnull(at.Name,'')),'')
FROM 	Appointments a
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE 	a.AppointmentsId = @pApptsId

Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = @AppointmentsId

SELECT  
	'286.PatientProfileID'	= @PatientProfileID,
	'287.PatientID' 	= IsNull(pp.PatientID,''),
	'288.PatientName'	= IsNull(pp.Prefix+' ','')  + IsNull(pp.First,'') + isnull(' '+pp.Middle,'')  + isnull(' '+pp.Last,'') + isnull(' '+pp.Suffix,''), 
	'289.PatientPrefix' 	= IsNull(pp.Prefix,''),
        '290.PatFirst'		= IsNull(pp.First,''),
        '291.PatMiddle'		= IsNull(pp.Middle,''),
	'292.PatLast'		= IsNull(pp.Last,''),
	'293.PatSuffix'		= IsNull(pp.Suffix,''),
        '294.PatientAddr1'	= IsNull(pp.Address1,''),
        '295.PatientAddr2'	= IsNull(pp.Address2,''),
        '296.PatientCity'	= IsNull(pp.City,''),
        '297.PatientState'	= IsNull(pp.State,''),
        '298.PatientZip'	= IsNull(pp.Zip,''),
	'299.PatientCountry' 	= ISNULL(pp.Country,''),
        '300.PatientBirthdate'	= pp.Birthdate,
        '301.PatientSex'	= IsNull(pp.Sex,''), 
        '302.PatientPhone1'	= IsNull(dbo.formatphone(pp.Phone1,1),''),
        '303.PatientSSN'	= IsNull(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),''),
        '304.PatOccupation'	= IsNull(pp.EmpOccup,''),
        '305.PatSchool'=IsNull(pp.SchoolName,''),
        '306.PatBudget'=IsNull(g.Budget,0),
        '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
	'308.PatientAge' = CASE	WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
			WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
			ELSE '' END
FROM    PatientProfile pp LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE	pp.PatientProfileID = @PatientProfileId

SELECT  
	'310.PatEmpStatus'=IsNull(ml3.Description,''),
        '311.PatStudentStatus'=IsNull(ml4.Description,''),
        '312.PatMaritalStatus'=IsNull(ml5.Description,''),
        '313.PatResidence'=IsNull(ml7.Description,''),
        '309.PatientIDNumeric' = CASE WHEN isnumeric(pp.PatientID) = 1 and len(pp.PatientId) <= 9 AND pp.PatientId NOT LIKE '%.%' THEN convert(int, pp.PatientID) ELSE 0 END,
        '315.PatientRelationToGuarantor'=IsNull(ml6.Description,''),
        '316.GuarantorName'=IsNull(g.Prefix+' ','') + IsNull(g.First,'')+isnull(' '+g.Middle,'')+isnull(' '+g.Last,'') + isnull(' '+g.Suffix,''), 
        '317.GuarantorAddr1'=IsNull(g.Address1,''),
        '318.GuarantorAddr2'=IsNull(g.Address2,''),
        '319.GuarantorCity'=IsNull(g.City,''),
        '320.GuarantorState'=IsNull(g.State,''),
        '321.GuarantorZip'=IsNull(g.Zip,''),
	'322.GuarantorCountry' = ISNULL(g.Country, ''),
        '323.GuarantorPhone'=IsNull(g.Phone1,'')
FROM 	PatientProfile pp LEFT JOIN Guarantor g ON pp.GuarantorId = 
g.GuarantorId 
	LEFT JOIN MedLists ml3 ON pp.EmpStatusMID = ml3.MedListsID 
	LEFT JOIN MedLists ml4 ON pp.StudentStatusMID = ml4.MedListsID 
	LEFT JOIN MedLists ml5 ON pp.MaritalStatusMID = ml5.MedListsID 
	LEFT JOIN MedLists ml6 ON pp.PatientRelationToGuarantorMID = 
ml6.MedListsID 
	LEFT JOIN MedLists ml7 ON pp.ResidenceTypeMID = ml7.MedListsID 
	LEFT JOIN Employer emp ON pp.EmployerId = emp.EmployerId
WHERE 	pp.PatientProfileID = @PatientProfileId 

SELECT
        '263.PatientFinancial'	= CASE WHEN ISNULL(@CasesId,0) <> 0 THEN
				IsNull(cml.Description,'')
				ELSE IsNull(ml.Description,'') END,
        '264.Allocation'	= CASE WHEN ISNULL(@CasesId,0) <> 0 THEN
				IsNull(cal.Name,'')
				ELSE IsNull(al.Name,'') END
FROM 	PatientProfile pp 
	LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID 
	LEFT JOIN AllocationSet al ON pp.AllocationSetId = al.AllocationSetid 
	LEFT JOIN Cases c on pp.PatientProfileId = c.PatientProfileId AND 
c.CasesId = @CasesId
	LEFT JOIN MedLists cml ON c.FinancialClassMID = cml.MedListsID 
	LEFT JOIN AllocationSet cal ON c.AllocationSetId = cal.AllocationSetid 

WHERE 	pp.PatientProfileID = @PatientProfileId 


DECLARE @GuarantorID int
SELECT @GuarantorID = (SELECT GuarantorID FROM PatientProfile WHERE 
PatientProfileID = @PatientProfileID)


SELECT	'326.GuarantorInsBalance'=ISNULL(sum(ppa.InsBalance),0),
	'327.GuarantorPatBalance'=ISNULL(sum(ppa.PatBalance),0)
FROM	PatientProfile pp, PatientProfileAgg ppa
WHERE	pp.PatientProfileID = ppa.PatientProfileID and
	pp.GuarantorID = @GuarantorID

SELECT  '330.EmpName'=IsNull(e.Name,''),
        '331.EmpAddr1'=IsNull(e.Address1,''),
        '332.EmpAddr2'=IsNull(e.Address2,''),
        '333.EmpCity'=IsNull(e.City,''),
        '334.EmpState'=IsNull(e.State,''),
        '335.EmpZip'=IsNull(e.Zip,''),
	'336.EmpCountry' = ISNULL(e.Country, ''),
        '337.EmpPhone'=IsNull(e.Phone1,''),
        '338.EmpAllocation'=IsNull(al.Name,''),
        '339.EmpFinancialClass'=IsNull(fc.Description,''),
        '340.EmpCarrierName'=IsNull(ic.Name,''),
        '341.EmpCarrierListname'=IsNull(ic.Listname,''),
        '342.EmpCarrierAddress1'=IsNull(ic.Address1,''),
        '343.EmpCarrierAddress2'=IsNull(ic.Address2,''),
        '344.EmpCarrierCity'=IsNull(ic.City,''),
        '345.EmpCarrierState'=IsNull(ic.State,''),
        '346.EmpCarrierZip'=IsNull(ic.Zip,''),
	'347.EmpCarrierCountry' = ISNULL(ic.Country,''),
        '350.EmpCarrierContact'=IsNull(ic.Contact,''),
        '351.EmpCarrierPhone'=IsNull(ic.Phone1,''),
        '352.EmpCarrierType'=IsNull(ct.Description,''),
        '353.EmpCarrierPolicyType'=IsNull(pt.Description,''),
        '354.EmpCarrierGroupId'=IsNull(ic.GroupId,''),
        '355.EmpCarrierGroupName'=IsNull(ic.GroupName,''),
        '356.PatientEmail'=IsNull(pp.EMailAddress,''),
	'360.PatientNotes'=IsNull(CONVERT(varchar(255),pp.ProfileNotes),''),
	'361.BillingNotes'=IsNull(CONVERT(varchar(255),pp.BillingNotes),''),
        '362.ApptNotes'=IsNull(CONVERT(varchar(255),a.Notes),''),
	'363.AlertNotes'=IsNull(CONVERT(varchar(255),pp.AlertNotes),''),
	'364.PatApptNotes'=IsNull(CONVERT(varchar(255),pp.AppointmentNotes),''),
	'365.MedicalRecordNumber'=IsNull(pp.MedicalRecordNumber,''),
        '370.WCClaimNo'=IsNull(c.ClaimNumber,''),
        '371.WCCID'= CASE WHEN IsNull(c.WorkersComp,0) > 0 THEN a.CasesId ELSE 0 END,
        '372.WCDateOfInjury' = CASE WHEN IsNull(c.WorkersComp,0) > 0 THEN c.DateOfInjury ELSE NULL END,
        '373.WCDescription'=CASE WHEN IsNull(c.WorkersComp,0) > 0 THEN c.Name ELSE '' END,
	'374.WCStatus'=CASE WHEN IsNull(c.WorkersComp,0) > 0 THEN c.Closed ELSE 0 END, 
        '375.LastApptDate' = @LastVisitDate,
	'380.ReferringPhysicianFirst' = IsNull(upper(df.First),''),
	'381.ReferringPhysicianMiddle' = IsNull(upper(df.Middle),''),
	'382.ReferringPhysicianLast' = IsNull(upper(df.Last),''),
	'383.ReferringPhysicianSuffix' = IsNull(upper(df.Suffix),''),
	'384.ReferringPhysicianUPIN' = IsNull(upper(df.UPIN),''),
	'385.DoctorRefer'=IsNull(df.ListName,''),
	'386.DoctorPCP' = IsNull(dpcp.ListName,'')
	FROM  	Appointments a INNER JOIN PatientProfile pp ON 
		pp.PatientProfileID=a.OwnerID 
		LEFT JOIN Cases c ON a.CasesId = c.CasesId AND ISNULL(c.WorkersComp,0) <> 0
		LEFT JOIN DoctorFacility df ON pp.RefDoctorID = df.DoctorFacilityID
		LEFT JOIN DoctorFacility dpcp on pp.PrimaryCareDoctorId = dpcp.DoctorFacilityId
		LEFT JOIN Employer e ON (c.EmployerId= e.EmployerId or (e.EmployerId is null and pp.EmployerId = e.EmployerId))
		LEFT JOIN InsuranceCarriers ic ON (c.PrimaryInsuranceCarriersId=ic.InsuranceCarriersId or (ic.InsuranceCarriersId is null and e.InsuranceCarrierId = ic.InsuranceCarriersId))
		LEFT JOIN MedLists ct ON ic.CarrierTypeMId = ct.MedListsId
		LEFT JOIN MedLists pt ON ic.PolicyTypeMId = pt.MedListsId
		LEFT JOIN MedLists fc ON (c.FinancialClassMId= fc.MedListsId or (c.FinancialClassMId is null and e.FinancialClassMId = fc.MedListsId))
		LEFT JOIN AllocationSet al ON (c.AllocationSetId = al.AllocationSetId or (c.AllocationSetId is null and e.AllocationSetId = al.AllocationSetId))
WHERE  	a.AppointmentsId = @AppointmentsId
END


SELECT	'450.CaseName' = isnull(c.Name,''),
	'451.AuthNumber' = isnull(c.AuthNumber,''),
	'452.AuthNumVisits' = isnull(c.AuthNumVisits,''),
	'453.UsedVisitCount' = isnull(c.UsedVisitCount,0),
	'454.VisitRemaining' = isnull(c.AuthNumVisits - c.UsedVisitCount,0),
	'455.IssuedDate' = ci.IssuedDate,
	'456.AuthExpdDate' = AuthExpdDate

FROM	CASES c
	JOIN CasesInsurance ci on c.CasesId = ci.CasesId
WHERE 	c.CasesId = @CasesId
	and ci.OrderForClaims = 1

CREATE TABLE #Global (
	GlobalDateNumber int IDENTITY,
	GlobalStartDate datetime,
	GlobalEndDate datetime,
	GlobalLength int,
	GlobalCPTCode varchar(10),
	GlobalCPTDesc varchar(255),
	GlobalNotes varchar(255)
	)

INSERT #Global
SELECT	'GlobalStartDate' 	= pvp.DateOfServiceFrom, 
	'GlobalEndDate'		= DateAdd(day,pvp.GPDays,pvp.DateOfServiceFrom),
	'GlobalLength'		= pvp.GPDays,
	'GlobalCPTCode'		= isnull(pvp.CPTCode,pvp.Code),
	'GlobalCPTDesc'		= isnull(pvp.Description,'') ,
	'GlobalNotes'		= CASE WHEN pvp.Code like '%GLOBAL%' THEN 
		cast(isnull(pvp.Notes,'') as varchar(255)) 
				  ELSE '' END
FROM PatientVisitProcs pvp
JOIN PatientVisit pv on pvp.PatientVisitId = pv.PatientVisitId
WHERE	isnull(GPDays,0) > 0
	AND pv.PatientProfileId = @PatientProfileId
	AND DateAdd(day,pvp.GPDays,pvp.DateOfServiceFrom) > @ApptStart
	AND isnull(pvp.Voided,0) = 0
ORDER BY GlobalEndDate desc

SELECT	'430.Global1StartDate' 		= isnull(GlobalStartDate,''),
	'431.Global1EndDate'		= isnull(GlobalEndDate,''),
	'432.Global1Length'		= isnull(GlobalLength,''),
	'433.Global1CPTCode'		= isnull(GlobalCPTCode,''),
	'434.Global1CPTDesc'		= isnull(GlobalCPTDesc,''),
	'435.Global1Notes'		= isnull(GlobalNotes,'')
FROM	#Global
WHERE	GlobalDateNumber = 1

SELECT	'436.Global2StartDate' 		= isnull(GlobalStartDate,''),
	'437.Global2EndDate'		= isnull(GlobalEndDate,''),
	'438.Global2Length'		= isnull(GlobalLength,''),
	'439.Global2CPTCode'		= isnull(GlobalCPTCode,''),
	'440.Global2CPTDesc'		= isnull(GlobalCPTDesc,''),
	'441.Global2Notes'		= isnull(GlobalNotes,'')
FROM	#Global
WHERE	GlobalDateNumber = 2

SELECT	'442.Global3StartDate' 		= isnull(GlobalStartDate,''),
	'443.Global3EndDate'		= isnull(GlobalEndDate,''),
	'444.Global3Length'		= isnull(GlobalLength,''),
	'445.Global3CPTCode'		= isnull(GlobalCPTCode,''),
	'446.Global3CPTDesc'		= isnull(GlobalCPTDesc,''),
	'447.Global3Notes'		= isnull(GlobalNotes,'')
FROM	#Global
WHERE	GlobalDateNumber = 3


DROP TABLE #Global


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-03 : 22:21:41

This might help http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-03-03 : 22:36:15

khtan --

I'm relatively new to SQL ... can you please explain what actions I need to follow? I did not write this Stored Procedure ... just trying to debug it and noticed the age was calculating wrong when the patients age was below 1 year. Do I need to run this function against my DB then somehow link it into my Stored Procedure?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-04 : 21:21:20

run the following script to see if the calc is to your requirement


declare	@ApptDate	datetime

select	@ApptDate	= dateadd(day, datediff(day, 0, getdate()), 0)

select	birthdate, 
	[308.PatientAge]
	=	case 	when datediff(day, birthdate, @ApptDate) <= 6 then
				cast(datediff(day, birthdate, @ApptDate) as varchar(10)) + ' dys'
			when datediff(day, birthdate, @ApptDate) <= 112 then
				cast(datediff(week, birthdate, @ApptDate) as varchar(10)) + ' wks'
			when datediff(month, birthdate, @ApptDate) <= 24 then
				cast(datediff(month, birthdate, @ApptDate) as varchar(10)) + ' mos'
			else
				cast(floor(datediff(day, birthdate, @ApptDate) / 365.25) as varchar(10)) + ' yrs'
			end
from	
(
	select	[DATE] as birthdate
	from	F_TABLE_DATE('2004-01-01', @ApptDate)
        -- Get F_TABLE_DATE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519 id="green">
) d
order by birthdate desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources