SubQuery Assistance 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)

SubQuery Assistance Needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-09-04 : 17:51:54

My client came back and wanted me to add in a filter for Transaction Date from my Query (see full query below):

WHERE pm.Source = 1 AND t.Amount <> 0
AND --Filter on date range
(
(pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900')
AND pm.DateOfEntry < DATEADD(d,1,ISNULL('09/04/2007','1/1/3000')))
)
The issue is how the following subquerys are calculating their values. I think I need to add in something now on if the pm.DateOfEntry is between Date1 and Date2. Would this be right and if so, can someone assist me on the syntax or at least give me a start on it ...?? It appears as if the subqueries are not respecting any date logic, only the id being passed into them.

Section of Code I need assistance with:

PatBalance = (SELECT SUM(pva.PatBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
InsBalance = (SELECT SUM(pva.InsBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId)

Main Query:

SET NOCOUNT ON

CREATE TABLE #Visit
(
PatientVisitId int,
PatientProfileId int
)

CREATE TABLE #Ledger
(
PatientProfileId int,
Type smallint,
PatientId varchar(15) NULL,
Birthdate datetime NULL,
PatientName varchar(110) NULL,
PatientAddress1 varchar(50) NULL,
PatientAddress2 varchar(50) NULL,
PatientCity varchar(30) NULL,
PatientState varchar(3) NULL,
PatientZip varchar(10) NULL,
PatientPhone1 varchar(15) NULL,
PatientPhone1Type varchar(25) NULL,
PatientPhone2 varchar(15) NULL,
PatientPhone2Type varchar(25) NULL,
PatientVisitId int NULL,
VisitDateOfService datetime NULL,
VisitDateOfEntry datetime NULL,
DoctorId int NULL,
DoctorName varchar(110) NULL,
FacilityId int NULL,
FacilityName varchar(60) NULL,
CompanyId int NULL,
CompanyName varchar(60) NULL,
TicketNumber varchar(20) NULL,
PatientVisitProcsId int NULL,
TransactionDateOfServiceFrom datetime NULL,
TransactionDateOfServiceTo datetime NULL,
TransactionDateOfEntry datetime NULL,
InternalCode varchar(10) NULL,
ExternalCode varchar(10) NULL,
Description varchar(255) NULL,
Fee money NULL,
Units float NULL,
PatAmount money NULL,
InsAmount money NULL,
Action varchar(1) NULL,
Payer varchar(255) NULL,
Notes text NULL,
PatBalance money NULL,
InsBalance money NULL,
Charges money NULL,
Payments money NULL,
Adjustments money NULL
)

/* Get the subset of visits for this report */
INSERT #Visit
SELECT pv.PatientVisitId, pv.PatientProfileId

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitID = pva.PatientVisitID
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId

WHERE pp.PatientProfileId = 462 AND pva.PatPayment <> 0
AND --Filter on date type and range
(
('1' = '1' AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))) OR
('1' = '2' AND pv.Entered >= ISNULL(NULL,'1/1/1900') AND pv.Entered < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)

/* Get demographics for the patient */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientId,
Birthdate,
PatientName,
PatientAddress1,
PatientAddress2,
PatientCity,
PatientState,
PatientZip,
PatientPhone1,
PatientPhone1Type,
PatientPhone2,
PatientPhone2Type,
PatBalance,
InsBalance,
Charges,
Payments,
Adjustments
)

SELECT DISTINCT
pp.PatientProfileId, 1, pp.PatientId, pp.Birthdate,
RTRIM(RTRIM(RTRIM(ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) + ' ' + pp.Last) + ' ' + ISNULL(pp.Suffix, '')) AS PatientName,
pp.Address1, pp.Address2, pp.City, pp.State, pp.Zip,
pp.Phone1, pp.Phone1Type, pp.Phone2, pp.Phone2Type,

PatBalance = (SELECT SUM(pva.PatBalance)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),

InsBalance = (SELECT SUM(pva.InsBalance)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),

Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),

Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId),

Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment)
FROM #Visit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
WHERE pv.PatientProfileId = pp.PatientProfileId)

FROM PatientProfile pp
INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId

/* Get visit information for the patient */

INSERT #Ledger
(PatientProfileId,
Type,
PatientVisitId,
VisitDateOfService,
VisitDateOfEntry,
DoctorId,
DoctorName,
FacilityId,
FacilityName,
CompanyId,
CompanyName,
TicketNumber
)

SELECT pv.PatientProfileId, 2, pv.PatientVisitId, pv.Visit, pv.Entered,
pv.DoctorId, d.ListName AS DoctorName,
pv.FacilityId, f.ListName AS FacilityName,
pv.CompanyId, c.ListName AS CompanyName,
pv.TicketNumber

FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId

/* Get diagnosis information for the patient's visits */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
InternalCode,
ExternalCode,
Description
)

SELECT pv.PatientProfileId, 3, pv.PatientVisitId,
pvd.Code, pvd.ICD9Code,
pvd.Description

FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId

/* Get transaction information for the patient's visits */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
PatientVisitProcsId,
TransactionDateOfServiceFrom,
TransactionDateOfEntry,
Description,
Payer,
PatAmount,
InsAmount,
Action,
Notes
)

SELECT pv.PatientProfileId, 5, pv.PatientVisitId, NULL,
ISNULL(pm.CheckDate, b.Entry), b.Entry,
at.Description + CASE WHEN pm.CheckCardNumber IS NULL THEN ''
ELSE ' - Check # ' + pm.CheckCardNumber + ' ' + CASE WHEN pm.CheckDate IS NULL THEN '' ELSE CONVERT(varchar(12), pm.CheckDate, 101) END END,
pm.PayerName,
CASE WHEN pm.Source = 1 THEN t.Amount END,
CASE WHEN pm.Source = 2 THEN t.Amount END,
t.Action,
CASE WHEN ISNULL(t.ShowOnStatement, 0) <> 0 THEN t.Note ELSE NULL END

FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action = 'P'
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0
INNER JOIN Batch b ON pm.BatchId = b.BatchId
LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId

WHERE pm.Source = 1 AND t.Amount <> 0
AND --Filter on date range
(
(pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900')
AND pm.DateOfEntry < DATEADD(d,1,ISNULL('06/30/2005','1/1/3000')))
)

SELECT tl.*
FROM #Ledger tl
ORDER BY tl.PatientProfileId, tl.PatientVisitId, tl.PatientVisitProcsId, tl.Type

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 18:18:02

Add an extra WHERE clause to the subquery?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 18:23:34

You also should make all the subqueries as one derived table...

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

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-09-04 : 18:28:54

Peso -

You also should make all the subqueries as one derived table... (Can you elaborate?)

I am still fairly new to SQL and Im not to sure what you mean.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 03:20:22

[code]SET NOCOUNT ON

CREATE TABLE #Visit
(
PatientVisitId int,
PatientProfileId int
)

CREATE TABLE #Ledger
(
PatientProfileId int,
Type smallint,
PatientId varchar(15) NULL,
Birthdate datetime NULL,
PatientName varchar(110) NULL,
PatientAddress1 varchar(50) NULL,
PatientAddress2 varchar(50) NULL,
PatientCity varchar(30) NULL,
PatientState varchar(3) NULL,
PatientZip varchar(10) NULL,
PatientPhone1 varchar(15) NULL,
PatientPhone1Type varchar(25) NULL,
PatientPhone2 varchar(15) NULL,
PatientPhone2Type varchar(25) NULL,
PatientVisitId int NULL,
VisitDateOfService datetime NULL,
VisitDateOfEntry datetime NULL,
DoctorId int NULL,
DoctorName varchar(110) NULL,
FacilityId int NULL,
FacilityName varchar(60) NULL,
CompanyId int NULL,
CompanyName varchar(60) NULL,
TicketNumber varchar(20) NULL,
PatientVisitProcsId int NULL,
TransactionDateOfServiceFrom datetime NULL,
TransactionDateOfServiceTo datetime NULL,
TransactionDateOfEntry datetime NULL,
InternalCode varchar(10) NULL,
ExternalCode varchar(10) NULL,
Description varchar(255) NULL,
Fee money NULL,
Units float NULL,
PatAmount money NULL,
InsAmount money NULL,
Action varchar(1) NULL,
Payer varchar(255) NULL,
Notes text NULL,
PatBalance money NULL,
InsBalance money NULL,
Charges money NULL,
Payments money NULL,
Adjustments money NULL
)

INSERT #Visit
SELECT pv.PatientVisitId,
pv.PatientProfileId
FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitID = pva.PatientVisitID
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE pp.PatientProfileId = 462
AND pva.PatPayment <> 0
AND --Filter on date type and range
(
('1' = '1' AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))) OR
('1' = '2' AND pv.Entered >= ISNULL(NULL,'1/1/1900') AND pv.Entered < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)

/* Get demographics for the patient */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientId,
Birthdate,
PatientName,
PatientAddress1,
PatientAddress2,
PatientCity,
PatientState,
PatientZip,
PatientPhone1,
PatientPhone1Type,
PatientPhone2,
PatientPhone2Type,
PatBalance,
InsBalance,
Charges,
Payments,
Adjustments
)
SELECT DISTINCT pp.PatientProfileId,
1,
pp.PatientId,
pp.Birthdate,
RTRIM(RTRIM(RTRIM(ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) + ' ' + pp.Last) + ' ' + ISNULL(pp.Suffix, '')) AS PatientName,
pp.Address1,
pp.Address2,
pp.City,
pp.State,
pp.Zip,
pp.Phone1,
pp.Phone1Type,
pp.Phone2,
pp.Phone2Type,
Yak.PatBalance,
Yak.InsBalance,
Yak.Charges,
Yak.Payments,
Yak.Adjustments
FROM PatientProfile pp
INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId
LEFT JOIN (
SELECT pv.PatientProfileId,
SUM(pva.PatBalance) AS PatBalance,
SUM(pva.InsBalance) AS InsBalance,
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) AS Adjustments
FROM #Visit AS pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
GROUP BY pv.PatientProfileId
) AS Yak ON Yak.PatientProfileId = pp.PatientProfileId

/* Get visit information for the patient */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
VisitDateOfService,
VisitDateOfEntry,
DoctorId,
DoctorName,
FacilityId,
FacilityName,
CompanyId,
CompanyName,
TicketNumber
)
SELECT pv.PatientProfileId,
2,
pv.PatientVisitId,
pv.Visit,
pv.Entered,
pv.DoctorId,
d.ListName AS DoctorName,
pv.FacilityId,
f.ListName AS FacilityName,
pv.CompanyId,
c.ListName AS CompanyName,
pv.TicketNumber
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId

/* Get diagnosis information for the patient's visits */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
InternalCode,
ExternalCode,
Description
)

SELECT pv.PatientProfileId,
3,
pv.PatientVisitId,
pvd.Code,
pvd.ICD9Code,
pvd.Description
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId

/* Get transaction information for the patient's visits */

INSERT #Ledger
(
PatientProfileId,
Type,
PatientVisitId,
PatientVisitProcsId,
TransactionDateOfServiceFrom,
TransactionDateOfEntry,
Description,
Payer,
PatAmount,
InsAmount,
Action,
Notes
)
SELECT pv.PatientProfileId,
5,
pv.PatientVisitId,
NULL,
ISNULL(pm.CheckDate, b.Entry),
b.Entry,
at.Description + ISNULL(' - Check # ' + pm.CheckCardNumber + ' ', '') + ISNULL(CONVERT(varchar(12), pm.CheckDate, 101), '')
pm.PayerName,
CASE
WHEN pm.Source = 1 THEN t.Amount
END,
CASE
WHEN pm.Source = 2 THEN t.Amount
END,
t.Action,
CASE
WHEN t.ShowOnStatement <> 0 THEN t.Note
END
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action = 'P'
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0
INNER JOIN Batch b ON pm.BatchId = b.BatchId
LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
WHERE pm.Source = 1
AND t.Amount <> 0
AND --Filter on date range
(
(pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900')
AND pm.DateOfEntry < DATEADD(d,1,ISNULL('06/30/2005','1/1/3000')))
)

SELECT tl.*
FROM #Ledger tl
ORDER BY tl.PatientProfileId,
tl.PatientVisitId,
tl.PatientVisitProcsId,
tl.Type[/code]

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

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-09-05 : 09:43:29

Peso,

Thanks for the coding assistance breaking out the subqueries into one derived table. I am still needing assistance getting it to take the date parameters. I tried adding on to the coding you broke into one derived table - by adding in a where clause and it seems not to matter. Any thoughts?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 09:45:44

To get data for September 2007

LEFT JOIN	(
			SELECT		pv.PatientProfileId,
					SUM(pva.PatBalance) AS PatBalance,
					SUM(pva.InsBalance) AS InsBalance,
					SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
					SUM(pva.InsPayment + pva.PatPayment) AS Payments,
					SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) AS Adjustments
			FROM		#Visit AS pv
			INNER JOIN	PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 
			WHERE		Col1 >= '20070901'
					AND Col1 < '20071001'
			GROUP BY	pv.PatientProfileId
		) AS Yak ON Yak.PatientProfileId = pp.PatientProfileId

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

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-09-05 : 10:02:39

I tried this and my results are far off.

FROM PatientProfile pp
INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId
LEFT JOIN
(
SELECT pv.PatientProfileId,
SUM(pva.PatBalance) AS PatBalance,
SUM(pva.InsBalance) AS InsBalance,
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
SUM(pva.InsAdjustment + pva.PatAdjustment) AS Adjustments

FROM #Visit AS pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0
WHERE
--Filter on date range
(
(pm.DateOfEntry >= ISNULL('06/01/2005', '1/1/1900')
AND pm.DateOfEntry < DATEADD(d,1,ISNULL('06/30/2005','1/1/3000')))
)

GROUP BY pv.PatientProfileId
)
AS Yak ON Yak.PatientProfileId = pp.PatientProfileId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 10:30:39

BUT YOU STILL HAVEN'T PUT THE DATE FILTER CRITERIA IN THE DERIVED TABLE!

See post 09/05/2007 : 09:45:44

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

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-09-06 : 12:38:40

Did I not in my post on 09/05/2007 : 10:02:39 ? What am I missing here? I need it to pull the date parameters from the PaymentMethod Date of Entry field.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 07:43:59

You need to put the WHERE date-filter IN BOTH places!
Both main query and the derived table.

See the last post where I also highlighted the datefilter in red.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources