SQL Query Help needed - SQL Server Forums

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)

SQL Query Help needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-10-17 : 16:17:52

In this question, I have posted two sets of coding .... one from a Stored Procedure that pulls what I want in the Report Query I have that currently works. My client wants to add in the Patient Contact name in the report example I have listed (see My Existing Query:). When I try adding in other ways, I get a row for each contact .... I just want the first one listed (as the SP lists). Any help is appreciated.

Part from another Stored Procedure:

DECLARE @pPatientProfileId int

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,
Address1 VARCHAR (100)NULL,
Address2 VARCHAR (100)NULL,
City VARCHAR (60) NULL,
State VARCHAR (20) NULL,
Zip VARCHAR (20) NULL,
Phone1 varchar (15) NULL,
Phone1Type varchar (10) NULL,
Phone2 varchar (15) NULL,
Phone2Type varchar (10) NULL,
ContactRelToPatient varchar (20) NULL

)

INSERT #Contacts_tmp
SELECT PatientProfileId,
First,
Middle,
Last,
Address1,
Address2,
City,
State,
Zip,
Phone1,
Phone1Type,
Phone2,
Phone2Type,
ContactRelToPatient
FROM PatientContacts pc
WHERE pc.PatientProfileId = @pPatientProfileId
ORDER BY Created

SELECT 'Contact1First' = IsNull(First,''),
'Contact1Middle' = IsNull(Middle,''),
'Contact1Last' = IsNull(Last,''),
'Contact1Address1' = ISNULL(address1,''),
'Contact1Address2' = ISNULL(address2,''),
'Contact1City' = ISNULL(city,''),
'Contact1State' = ISNULL(state,''),
'Contact1Zip' = ISNULL(zip,''),
'Contact1Phone' = IsNull(Phone1,''),
'Contact1Phone1Type' = IsNull(Phone1Type, ''),
'Contact1Phone2' = IsNull(Phone2, ''),
'Contact1Phone2Type' = IsNull(Phone2Type, ''),
'Contact1RelToPatient' = IsNull(ContactRelToPatient,'')
FROM #Contacts_tmp
WHERE id_col = 1

My Existing Query:

/* Guarantor Balance */
SET NOCOUNT ON

IF ( SELECT OBJECT_ID('tempdb..#A','U') ) IS NOT NULL
DROP TABLE #A
IF ( SELECT OBJECT_ID('tempdb..#B','U') ) IS NOT NULL
DROP TABLE #B
IF ( SELECT OBJECT_ID('tempdb..#C','U') ) IS NOT NULL
DROP TABLE #C
IF ( SELECT OBJECT_ID('tempdb..#GPAll','U') ) IS NOT NULL
DROP TABLE #GPAll

CREATE TABLE #A
(GuarantorId int NOT NULL,
TotalInsBalance money NULL,
TotalPatBalance money NULL,
TotalBalance money NULL
)
CREATE TABLE #B
(GuarantorId int NOT NULL,
PatientProfileId int NOT NULL,
InsBalance money NULL,
PatBalance money NULL,
Balance money NULL,
Description varchar(255) NULL,
PatientVisitId int NULL,
VisitInsPayment money NULL,
VisitPatPayment money NULL,
VisitInsBalance money NULL,
VisitPatBalance money NULL,
VisitStatus varchar(255) NULL
)
CREATE TABLE #GPAll
( GuarantorId int NOT NULL,
PaymentDate datetime NOT NULL,
Amount money NULL
)
CREATE TABLE #C
(GuarantorId int NULL,
LastPaymentDate datetime NULL,
PaymentAge int NULL,
Amount money NULL,
)

INSERT #A
SELECT gr.GuarantorId,
SUM(ppa.InsBalance) AS TotalInsBalance,
SUM(ppa.PatBalance) AS TotalPatBalance,
SUM(ppa.Balance) AS TotalBalance
FROM PatientProfile pp
INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
WHERE
(
('43' IS NOT NULL AND gr.GuarantorId IN(43))
OR '43' IS NULL
)
GROUP BY gr.GuarantorId
ORDER BY gr.GuarantorId

INSERT #B
SELECT gr.GuarantorId,
pp.PatientProfileId,
ppa.InsBalance AS InsBalance,
ppa.PatBalance AS PatBalance,
ppa.Balance AS Balance,
pc.Description,
pv.PatientVisitId,
pva.InsPayment AS VisitInsPayment,
pva.PatPayment AS VisitPatPayment,
pva.InsBalance AS VisitInsBalance,
pva.PatBalance AS VisitPatBalance,
bs.Description AS VisitStatus
FROM Guarantor gr
INNER JOIN PatientProfile pp ON gr.GuarantorId = pp.GuarantorId
INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
LEFT JOIN Medlists bs on bs.TableName = 'BillStatus' AND pv.BillStatus = bs.JoinId
LEFT JOIN PatientCorrespondence pc ON pv.PatientVisitId = pc.PatientVisitId
WHERE
((
('43' IS NOT NULL AND gr.GuarantorId IN(43))
OR '43' IS NULL
)
AND
---pva.PatBalance > 0
pv.Billstatus NOT IN (12)
)

INSERT #GPAll
SELECT px.GuarantorId,
px.PaymentDate AS PaymentDate,
SUM(px.Amount) AS Amount
FROM
(
SELECT gr.GuarantorId,
b.Entry AS PaymentDate,
pm.Amount
FROM PaymentMethod pm
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
WHERE pm.PayerType = 'Patient' AND pm.Amount <> 0
AND
(
('43' IS NOT NULL AND gr.GuarantorId IN(43))
OR '43' IS NULL
)
UNION ALL
SELECT gr.GuarantorId,
b.Entry AS PaymentDate,
pm.Amount
FROM PaymentMethod pm
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN Guarantor gr ON pm.PayerId = gr.GuarantorId
WHERE pm.PayerType = 'Guarantor' AND pm.Amount <> 0
AND
(
('43' IS NOT NULL AND gr.GuarantorId IN(43))
OR '43' IS NULL
)
) AS px
GROUP BY GuarantorId, PaymentDate
ORDER BY GuarantorId

INSERT #C
SELECT gp.GuarantorId,
gp.PaymentDate AS LastPaymentDate,
DATEDIFF(day, gp.PaymentDate, getdate()) AS PaymentAge,
gp.Amount
FROM #GPAll gp
WHERE gp.PaymentDate =
( SELECT MAX(PaymentDate)
FROM #GPAll gpm
WHERE gp.GuarantorId = gpm.GuarantorId
)
ORDER BY GuarantorId

SELECT
dbo.FormatName(gr.Prefix, gr.First, gr.Middle, gr.Last, gr.Suffix) AS GuarantorName,
ISNULL(gr.Address1,'')+ ' ' + ISNULL(gr.Address2,'') AS [Guarantor Address],
ISNULL(gr.City,'')+ ' ' + ISNULL(gr.State,'')+ ' ' + ISNULL(gr.Zip,'') AS [Guarantor CSZ],
LEFT(gr.SSN, 3) + '-' + SUBSTRING(gr.SSN, 4, 2) + '-' + RIGHT(gr.SSN, 4) AS SSN,
'(' + LEFT(gr.Phone1, 3) + ') ' + SUBSTRING(gr.Phone1, 4, 3) + '-' + RIGHT(gr.Phone1, 4) AS Phone,
'(' + LEFT(gr.Phone2, 3) + ') ' + SUBSTRING(gr.Phone2, 4, 3) + '-' + RIGHT(gr.Phone2, 4) AS Phone2,
RefA.TotalInsBalance AS GuarantorInsBalance,
RefA.TotalPatBalance AS GuarantorPatBalance,
RefA.TotalBalance AS GuarantorBalance,
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
RefB.InsBalance,
RefB.PatBalance,
RefC.LastPaymentDate,
RefC.Amount,
RefB.Balance,
pv.Visit,
RefB.Description,
pv.TicketNumber,
RefB.VisitInsPayment,
RefB.VisitPatPayment,
RefB.VisitInsBalance,
RefB.VisitPatBalance,
RefB.VisitStatus
FROM #A as RefA
INNER JOIN #B as RefB ON RefB.GuarantorId = RefA.GuarantorId
LEFT OUTER JOIN #C as RefC ON RefA.GuarantorId = RefC.GuarantorId
INNER JOIN Guarantor gr ON gr.GuarantorId = RefA.GuarantorId
INNER JOIN PatientProfile pp ON pp.PatientProfileId = RefB.PatientProfileId
INNER JOIN PatientVisit pv ON pv.PatientVisitId = RefB.PatientVisitId
WHERE
( pv.Visit >= ISNULL(NULL,'1/1/1900')
AND pv.Visit < DATEADD(d, 1, ISNULL(NULL,'1/1/3000'))
)
AND
(
1 < 2 OR 1 > 3
OR CASE WHEN 1 = 2 THEN RefA.TotalBalance END > NULL
OR CASE WHEN 1 = 3 THEN RefA.TotalBalance END < NULL
OR (CASE WHEN 1 = 3 THEN ABS(RefB.InsBalance) END < NULL)
OR (CASE WHEN 1 = 3 THEN ABS(RefB.PatBalance) END < NULL)
)
AND
(
((30 IS NULL)
OR
(RefC.PaymentAge IS NULL))
OR
(RefC.PaymentAge > 30)
)
AND --Filter on insurance balance
(
(1 = 1) OR
(1 = 2 AND RefA.TotalInsBalance > NULL) OR
(1 = 3 AND RefA.TotalInsBalance < NULL)
)
AND --Filter on patient balance
(
(1 = 1) OR
(1 = 2 AND RefA.TotalPatBalance > NULL) OR
(1 = 3 AND RefA.TotalPatBalance < NULL)
)

GROUP BY
dbo.FormatName(gr.Prefix, gr.First, gr.Middle, gr.Last, gr.Suffix),
ISNULL(gr.Address1,'')+ ' ' + ISNULL(gr.Address2,''),
ISNULL(gr.City,'')+ ' ' + ISNULL(gr.State,'')+ ' ' + ISNULL(gr.Zip,''),
LEFT(gr.SSN, 3) + '-' + SUBSTRING(gr.SSN, 4, 2) + '-' + RIGHT(gr.SSN, 4),
'(' + LEFT(gr.Phone1, 3) + ') ' + SUBSTRING(gr.Phone1, 4, 3) + '-' + RIGHT(gr.Phone1, 4),
'(' + LEFT(gr.Phone2, 3) + ') ' + SUBSTRING(gr.Phone2, 4, 3) + '-' + RIGHT(gr.Phone2, 4),
RefA.TotalInsBalance,
RefA.TotalPatBalance,
RefA.TotalBalance,
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
RefB.InsBalance,
RefB.PatBalance,
RefC.LastPaymentDate,
RefC.Amount,
RefB.Balance,
pv.Visit,
RefB.Description,
pv.TicketNumber,
RefB.VisitInsPayment,
RefB.VisitPatPayment,
RefB.VisitInsBalance,
RefB.VisitPatBalance,
RefB.VisitStatus
ORDER BY GuarantorName, PatientName
DROP TABLE #A
DROP TABLE #B
DROP TABLE #C
DROP TABLE #GPAll

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 01:57:45

Is this a query resulting from your famous dynamic sql query generator?

E 12°55'05.25"
N 56°04'39.16"

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-10-18 : 04:52:13

How about making way used of a derived table instead of a temporary table? I think it's performance wise.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources