November 7, 2006 at 10:49 am
Sorry for such a trivial question! I'm a hardware tech who has been appointed DBA. We're running SQL Server 2000. Our software vendor has e-mailed me a stored procedure that a staff member requested. I am to "install" it in the proper database. How is that done? The vendor is no help, and my pleas for training are falling on deaf ears. Thanks!!!
November 7, 2006 at 12:06 pm
Can you post the exact code he sent you?
November 7, 2006 at 1:46 pm
Here it is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AAA_CP_CustomDataExport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AAA_CP_CustomDataExport]
GO
--exec AAA_CP_CustomDataExport NULL, '01/01/06', '01/31/06'
CREATE PROCEDURE AAA_CP_CustomDataExport
@PlanID varchar(5) = NULL,
@BeginIncurredDate datetime = NULL,
@EndIncurredDate datetime = NULL
/************************************************************************/
/* Stored Procedure: AAA_CP_CustomDataExport */
/* */
/* Purpose: This procedure returns a custom data extract that can be */
/* copied from query analyzer into Excel for further analyis. */
/* */
/* Input Parameters: */
/* @PlanID */
/* @BeginIncurredDate */
/* @EndIncurredDate */
/* */
/* Output Parameters: None */
/* */
/* Return Value: None */
/* */
/* Usage: */
/* exec dbo.AAA_CP_CustomDataExport */
/* @PlanID = 'ARKP', */
/* @BeginIncurredDate = '01/01/2005', */
/* @EndIncurredDate = '01/31/2005' */
/* */
/* Called By: application */
/* */
/* Calls: None */
/* */
/* Data Modifications: None */
/* */
/* Updates: */
/* Date Author Purpose */
/* 07/06/2006 Chris P. Smith Initial Coding */
/* */
/************************************************************************/
AS
DECLARE
@IncurredDateRecordID integer,
@ProcessCompleteKey integer,
@ServiceLineTypeRecordID integer,
@PlanFinancialPartyRecordID integer,
@PrimaryEnrolleeTypeRecordID integer,
@DefaultStartDate datetime,
@DefaultEndDate datetime;
DECLARE @Plans TABLE (
PLPlanKey integer PRIMARY KEY CLUSTERED
);
DECLARE @ClaimsToView TABLE (
CPHeaderKey integer,
VersionNumber integer,
PBEntityKey integer,
IncurredDate datetime,
PRIMARY KEY CLUSTERED (CPHeaderKey)
);
DECLARE @Claims TABLE (
CPHeaderKey integer,
VersionNumber integer,
BenefitsAssignedFlag bit,
CPLineItemKey integer,
FamilyID varchar(16),
SequenceNumber integer,
Primary_FirstName varchar(25),
Primary_MiddleName varchar(25),
Primary_LastName varchar(50),
Patient_FirstName varchar(25),
Patient_MiddleName varchar(25),
Patient_LastName varchar(50),
Patient_Gender varchar(25),
Patient_Age integer,
Patient_DOB datetime,
Primary_MemberClass varchar(50),
Primary_City varchar(30),
Primary_State varchar(2),
Primary_Zip varchar(9),
Provider_TIN varchar(9),
Provider_Name varchar(50),
Provider_City varchar(30),
Provider_State varchar(2),
Provider_LastChangedBy varchar(50),
Profider_LastChanged datetime,
BilledAmount money,
CoverageType varchar(30),
PlanSelected varchar(20),
NumberOfUnits integer,
UnitsDescription varchar(50),
IncurredDate datetime,
StartOfService datetime,
EndOfService datetime,
PRIMARY KEY CLUSTERED (CPHeaderKey, CPLineItemKey)
);
DECLARE @ClaimDXCodes TABLE (
CPHeaderKey integer,
SequenceNumber integer,
DXCode varchar(48),
DXDescription varchar(100),
PRIMARY KEY CLUSTERED (CPHeaderKey, SequenceNumber)
);
DECLARE @ClaimServiceCodes TABLE (
CPHeaderKey integer,
CPLineItemKey integer,
ServiceCode varchar(48),
ServiceCodeDescription varchar(100),
BenefitCode varchar(5),
BenefitCodeDescription varchar(20),
PRIMARY KEY CLUSTERED (CPHeaderKey, CPLineItemKey)
);
DECLARE @ClaimFinancials TABLE (
CPHeaderKey integer,
CPLineItemKey integer,
ChargeAmt money,
CoveredAmt money,
NotAllowedMemberAmt money,
NotAllowedProviderAmt money,
PayAmt money,
DeductibleAmount money,
PRIMARY KEY CLUSTERED (CPHeaderKey, CPLineItemKey)
);
DECLARE @IncurredDates TABLE (
CPHeaderKey integer,
VersionNumber integer,
EventDate datetime,
PRIMARY KEY CLUSTERED (CPHeaderKey, VersionNumber)
);
SET NOCOUNT ON;
/*
** Variable initialization.
*/
SET @DefaultStartDate = '01/01/1753';
SET @DefaultEndDate = '01/01/3000';
SET @IncurredDateRecordID = dbo.GEN_RecordID(53, 2);
SET @ProcessCompleteKey = dbo.GEN_RecordID(51, 32768);
SET @ServiceLineTypeRecordID = dbo.GEN_RecordID(58, 1);
SET @PlanFinancialPartyRecordID = dbo.GEN_RecordID(54, 1);
SET @PrimaryEnrolleeTypeRecordID = dbo.GEN_RecordID(9, 1);
SET @BeginIncurredDate = CASE WHEN (@BeginIncurredDate IS NULL) OR (@BeginIncurredDate = '') THEN @DefaultStartDate ELSE @BeginIncurredDate END;
SET @EndIncurredDate = CASE WHEN (@EndIncurredDate IS NULL) OR (@EndIncurredDate = '') THEN @DefaultEndDate ELSE @EndIncurredDate END;
/*
** If we were passed a plan id then we'll limit our results by it, otherwise we'll specify that all
** the plans should be included.
*/
IF RTRIM(ISNULL(@PlanID, '')) = ''
INSERT INTO @Plans (
PLPlanKey
)
SELECT
PLPlanKey
FROM
dbo.PLPlanHeader;
ELSE
INSERT INTO @Plans (
PLPlanKey
)
SELECT
PLPlanKey
FROM
dbo.PLPlanHeader
WHERE
PlanID = @PlanID;
INSERT INTO @IncurredDates (CPHeaderKey, VersionNumber, EventDate)
SELECT
hd.CPHeaderKey, hd.VersionNumber, hd.EventDate
FROM
dbo.CPHeaderDate hd
WHERE
hd.EventRecordID_G51_53 = @IncurredDateRecordID
ANDhd.EventDate >= @BeginIncurredDate
ANDhd.EventDate <= @EndIncurredDate;
/*
** Get the listing of claims to be exported.
*/
INSERT INTO @ClaimsToView (
CPHeaderKey, PBEntityKey, VersionNumber, IncurredDate
)
SELECT
h.CPHeaderKey, h.PBEntityKey, h.VersionNumber, hd.EventDate
FROM
dbo.CPHeader h
INNER JOIN @IncurredDates hd ON
h.CPHeaderKey = hd.CPHeaderKey
ANDh.VersionNumber = hd.VersionNumber
WHERE
h.ComputedStatusRecordId_G51 = @ProcessCompleteKey
/*
** Get the base claim information.
*/
INSERT INTO @Claims(
CPHeaderKey, VersionNumber, BenefitsAssignedFlag, CPLineItemKey, FamilyID, SequenceNumber, Primary_FirstName, Primary_MiddleName,
Primary_LastName, Patient_FirstName, Patient_MiddleName, Patient_LastName, Patient_Gender, Patient_Age, Patient_DOB,
Primary_MemberClass, Primary_City, Primary_State, Primary_Zip, Provider_TIN, Provider_Name, Provider_City,
Provider_State, Provider_LastChangedBy, Profider_LastChanged, BilledAmount, CoverageType, PlanSelected,
NumberOfUnits, UnitsDescription, IncurredDate, StartOfService, EndOfService
)
SELECT
ctv.CPHeaderKey, ctv.VersionNumber, c.BenefitsAssignedFlag, li.CPLineItemKey, pam.FamilyID, pam.SequenceNumber, prie.FirstName, prie.MiddleName, prie.LastName + ISNULL(prig4.ShortDesc, '') AS LastName,
pate.FirstName, pate.MiddleName, pate.LastName + ISNULL(patg4.ShortDesc, '') AS LastName, path2.Description,
dbo.Age(pam.BirthDate, ctv.IncurredDate), pam.BirthDate, priecd.MbrClass, priea.City, priea.State, LEFT(priea.zip, 5) AS Zip,
prv.TaxPayerIdentificationNumber, prve.LastName AS Provider_Name,
prvea.City AS Provider_City, prvea.State AS Provider_State, prve.WhoLastChanged, prve.WhenLastChanged,
c.TotalAmountOfClaim, cth.CovType + ' - ' + cth.ShortDesc, ph.ShortDesc, li.Quantity AS Number_Of_Units, h41.Description AS Unit_Description,
ctv.IncurredDate, li.StartOfService, li.EndOfService
FROM
@ClaimsToView ctv
INNER JOIN dbo.CPClaim c ON
ctv.CPHeaderKey = c.CPHeaderKey
INNER JOIN dbo.PBEntityClaimsData ecd ON
c.PatientMBMemberKey = ecd.MBMemberKey
INNER JOIN dbo.PBMember pam ON
ecd.PBEntityKey = pam.PBEntityKey
INNER JOIN dbo.PBMember prim ON
pam.FamilyID = prim.FamilyID
ANDpam.CompanyPBEntityKey = prim.CompanyPBEntityKey
ANDprim.EnrolleeTypeKey = @PrimaryEnrolleeTypeRecordID
INNER JOIN dbo.PBEntity prie ON
prim.PBEntityKey = prie.PBEntityKey
LEFT OUTER JOIN dbo.GEN_LookupTables prig4 ON
prie.SuffixID = prig4.RecordID
INNER JOIN dbo.PBEntity pate ON
ecd.PBEntityKey = pate.PBEntityKey
LEFT OUTER JOIN dbo.GEN_LookupTables patg4 ON
pate.SuffixID = patg4.RecordID
LEFT OUTER JOIN dbo.GEN_HIPAA_LookupTables path2 ON
pam.SexKey = path2.RecordID
LEFT OUTER JOIN dbo.PBEntityClaimsData priecd ON
prie.PBEntityKey = priecd.PBEntityKey
ANDpriecd.PLPlanKey = ecd.PLPlanKey
LEFT OUTER JOIN dbo.PBEntityAddress priea ON
priea.PBEntityAddressKey = dbo.PBGetEntityAddressKey(prie.PBEntityKey, 1)
INNER JOIN dbo.CPProvider prv ON
c.ServiceProviderPBEntityKey = prv.PBEntityKey
INNER JOIN dbo.PBEntity prve ON
prv.PBEntityKey = prve.PBEntityKey
LEFT OUTER JOIN dbo.PBEntityAddress prvea ON
prvea.PBEntityAddressKey = dbo.PBGetEntityAddressKey(prv.PBEntityKey, 1)
INNER JOIN dbo.PLCovTypeHeader cth ON
c.PLCovTypeHdrKey = cth.PLCovTypeHdrKey
INNER JOIN dbo.PLPlanHeader ph ON
cth.PLPlanKey = ph.PLPlanKey
INNER JOIN @Plans ptv ON
ph.PLPlanKey = ptv.PLPlanKey
INNER JOIN dbo.CPLineItem li ON
ctv.CPHeaderKey = li.CPHeaderKey
ANDli.LineItemTypeRecordID_G58 = @ServiceLineTypeRecordID
INNER JOIN dbo.GEN_HIPAA_LookupTables h41 ON
li.QuantityUOM_RecordID_H41 = h41.RecordID;
/*
** Get summary financial information for the claims.
*/
INSERT INTO @ClaimFinancials (
CPHeaderKey, CPLineItemKey, PayAmt, CoveredAmt, ChargeAmt, NotAllowedProviderAmt, NotAllowedMemberAmt, DeductibleAmount
)
SELECT
a.CPHeaderKey, b.CPLineItemKey,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID & 0x0F00) < 0x0531) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS PayAmt,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND (d.ValueID < 0x0400) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS CoveredAmt,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID <= 0x0102)) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS ChargeAmt,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID) = 0x0202) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS NotAllowedProvider,
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID) = 0x0201) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
), -- AS NotAllowedMember
SUM(
CASE
WHEN (e.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID) AND ((d.ValueID) = 0x0405) THEN (c.Amount * e.Multiplier)
ELSE $0.00
END
) -- AS DeductibleAmount
FROM
@ClaimsToView a
INNER JOIN CPLineItem b ON
b.CPHeaderKey = a.CPHeaderKey
ANDb.LineItemTypeRecordID_G58 = @ServiceLineTypeRecordID
INNER JOIN CPLineItemAmount c ON
c.CPLineItemKey = b.CPLineItemKey
INNER JOIN GEN_LookupTables d ON
d.RecordID = c.FinancialActivityRecordID_G55
INNER JOIN CPFinancialActivityParticipant e ON
e.FinancialActivityRecordID_G55 = d.RecordID
ANDe.FinancialPartyRecordID_G54 = @PlanFinancialPartyRecordID
GROUP BY
a.CPHeaderKey, b.CPLineItemKey;
/*
** Get the diagnosis codes associated with the claims.
*/
INSERT INTO @ClaimDXCodes(
CPHeaderKey, SequenceNumber, DXCode, DXDescription
)
SELECT
cdc.CPHeaderKey, cdc.SequenceNumber, dcl.IndustryStandardCode, dcl.Description
FROM
@ClaimsToView c
INNER JOIN CPClaimDiagnosisCode cdc ON
c.CPHeaderKey = cdc.CPHeaderKey
INNER JOIN GEN_CP_LookupTables dcl ON
dcl.RecordID = cdc.DiagnosisCodeRecordID_C6
/*
** Get the service and benefit codes for the line items.
*/
INSERT INTO @ClaimServiceCodes (
CPHeaderKey, CPLineItemKey, ServiceCode, ServiceCodeDescription, BenefitCode, BenefitCodeDescription
)
SELECT
c.CPHeaderKey, c.CPLineItemKey, csc.IndustryStandardCode, csc.Description,
bch.BenCode, bch.ShortDesc
FROM
@Claims c
INNER JOIN CPLineItemClaimAdjudicationData cad ON
cad.CPLineItemKey = c.CPLineItemKey
INNER JOIN GEN_CP_LookupTables csc ON
csc.RecordID = cad.CurrentServiceCodeRecordID_C4
LEFT OUTER JOIN PLBenCodeHeader bch ON
bch.PLBenCodeHdrKey = cad.PLBenCodeHdrKey;
/*
** Turn NOCOUNT off so that the user will be able to clearly see how many rows came back.
*/
SET NOCOUNT OFF;
/*
** Return the final recordset.
*/
SELECT
RIGHT('00000000' + CONVERT(varchar, c.CPHeaderKey), 8) + '-' + RIGHT('00' + CONVERT(varchar, c.VersionNumber), 2) AS [ClaimNumber],
c.IncurredDate, c.FamilyID + '-' + RIGHT('00' + CONVERT(varchar, c.SequenceNumber), 2) AS [Patient_ID], c.Patient_DOB, c.Patient_Gender,
c.PlanSelected, c.Provider_TIN, c.Provider_Name, c.Provider_City, c.Provider_State,
ISNULL(DXCode_1, '') AS DXCode_1, ISNULL(DXDescription_1, '') AS DXDescription_1,
ISNULL(DXCode_2, '') AS DXCode_2, ISNULL(DXDescription_2, '') AS DXDescription_2,
ISNULL(DXCode_3, '') AS DXCode_3, ISNULL(DXDescription_3, '') AS DXDescription_3,
ISNULL(DXCode_4, '') AS DXCode_4, ISNULL(DXDescription_4, '') AS DXDescription_4,
ISNULL(DXCode_5, '') AS DXCode_5, ISNULL(DXDescription_5, '') AS DXDescription_5,
ISNULL(DXCode_6, '') AS DXCode_6, ISNULL(DXDescription_6, '') AS DXDescription_6,
ISNULL(DXCode_7, '') AS DXCode_7, ISNULL(DXDescription_7, '') AS DXDescription_7,
ISNULL(DXCode_8, '') AS DXCode_8, ISNULL(DXDescription_8, '') AS DXDescription_8,
c.StartOfService, c.EndOfService,
ISNULL(csc.ServiceCode, '') AS ServiceCode, ISNULL(csc.ServiceCodeDescription, '') AS ServiceCodeDescription,
ISNULL(csc.BenefitCode, '') AS BeneiftCode, ISNULL(csc.BenefitCodeDescription, '') AS BenefitCodeDescription,
c.NumberOfUnits, c.UnitsDescription,
ISNULL(cf.ChargeAmt, 0) AS ChargedAmount, ISNULL(cf.CoveredAmt, 0) AS CoveredAmount, ISNULL(cf.PayAmt, 0) AS PayAmount,
ISNULL(cf.NotAllowedProviderAmt, 0) AS NotAllowedProviderAmount,
ISNULL(cf.NotAllowedMemberAmt, 0) AS NotAllowedMemberAmount,
ISNULL(cf.DeductibleAmount, 0) AS DeductibleAmount,
CASE
WHEN c.BenefitsAssignedFlag = 1 THEN 'Yes'
ELSE 'No'
END AS [BenefitsAssignedIndicator]
FROM
@Claims c
LEFT OUTER JOIN @ClaimServiceCodes csc ON
c.CPHeaderKey = csc.CPHeaderKey
ANDc.CPLineItemKey = csc.CPLineItemKey
LEFT OUTER JOIN @ClaimFinancials cf ON
c.CPHeaderKey = cf.CPHeaderKey
ANDc.CPLineItemKey = cf.CPLineItemKey
LEFT OUTER JOIN (
SELECT
CPHeaderKey,
MAX(
CASE
WHEN SequenceNumber = 1 THEN DXCode
ELSE ''
END
) AS DXCode_1,
MAX(
CASE
WHEN SequenceNumber = 1 THEN DXDescription
ELSE ''
END
) AS DXDescription_1,
MAX(
CASE
WHEN SequenceNumber = 2 THEN DXCode
ELSE ''
END
) AS DXCode_2,
MAX(
CASE
WHEN SequenceNumber = 2 THEN DXDescription
ELSE ''
END
) AS DXDescription_2,
MAX(
CASE
WHEN SequenceNumber = 3 THEN DXCode
ELSE ''
END
) AS DXCode_3,
MAX(
CASE
WHEN SequenceNumber = 3 THEN DXDescription
ELSE ''
END
) AS DXDescription_3,
MAX(
CASE
WHEN SequenceNumber = 4 THEN DXCode
ELSE ''
END
) AS DXCode_4,
MAX(
CASE
WHEN SequenceNumber = 4 THEN DXDescription
ELSE ''
END
) AS DXDescription_4,
MAX(
CASE
WHEN SequenceNumber = 5 THEN DXCode
ELSE ''
END
) AS DXCode_5,
MAX(
CASE
WHEN SequenceNumber = 5 THEN DXDescription
ELSE ''
END
) AS DXDescription_5,
MAX(
CASE
WHEN SequenceNumber = 6 THEN DXCode
ELSE ''
END
) AS DXCode_6,
MAX(
CASE
WHEN SequenceNumber = 6 THEN DXDescription
ELSE ''
END
) AS DXDescription_6,
MAX(
CASE
WHEN SequenceNumber = 7 THEN DXCode
ELSE ''
END
) AS DXCode_7,
MAX(
CASE
WHEN SequenceNumber = 7 THEN DXDescription
ELSE ''
END
) AS DXDescription_7,
MAX(
CASE
WHEN SequenceNumber = 8 THEN DXCode
ELSE ''
END
) AS DXCode_8,
MAX(
CASE
WHEN SequenceNumber = 8 THEN DXDescription
ELSE ''
END
) AS DXDescription_8
FROM
@ClaimDXCodes
GROUP BY
CPHeaderKey
) dxc ON
c.CPHeaderKey = dxc.CPHeaderKey
ORDER BY
c.FamilyID, c.SequenceNumber, c.IncurredDate;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
November 7, 2006 at 1:47 pm
If it is regular stored procedure (a readable file):
1. Open your Query Analyzer;
2. Point to your desired databse;
3. Copy the code on the code window;
4. Run it.
If it is an extended stored procedure (DLL file):
1. Copy the file to ...\MS SQL Server\...\Bin folder;
2. Open your Query Analyzer;
3. Point to your desired database;
4. Run sp_addextendedproc to register (reference online help for its use).
November 7, 2006 at 1:48 pm
Your stored procedure is a regular one!
November 7, 2006 at 1:59 pm
Duh. I should have thought of that! Another question though. This is supposed to dump data I can use to create a report. Where is that data dump? Sorry to be such a pain. Like I say, I'm a hardware guy.
November 7, 2006 at 2:04 pm
I need to have more information to answer your last question.
November 7, 2006 at 2:06 pm
No problem. Thanks for the help! I'm making the vendor deal with it.
November 7, 2006 at 2:06 pm
Ya, that's a question that would be best answered by the vendor.
November 7, 2006 at 3:21 pm
Currently the output is intended to go to Query Analyzer.
/* Purpose: This procedure returns a custom data extract that can be */
/* copied from query analyzer into Excel for further analyis. */
It appears the vendor intends you to run this from QA and then copy the results into Excel.
It even shows how to run the script:
Usage: */
/* exec dbo.AAA_CP_CustomDataExport */
/* @PlanID = 'ARKP', */
/* @BeginIncurredDate = '01/01/2005', */
/* @EndIncurredDate = '01/31/2005' */
It does appear to be intended to have a final use in an application: * Called By: application */
That app should accept the parameters from the user and then return the desired results.
-SQLBill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply