June 4, 2009 at 2:59 pm
Please note, the SQL is handled dynamically by the server, therefore some of
the items in my WHERE clauses will look odd to you. I know the NULL IS NOT
NULL ... looks odd and does not make sense, please ignore these.
My question has to deal with adding in a new field - which I believe will end
up being a sub-query. I need to add in the last three dates a patient
received a statement to this report. If you look at my query, I do a
statement count ...
=====================================================================
(
SELECT COUNT(*) FROM edistatement es
INNER JOIN edistatementfile esf2 ON es.edistatementfileID = esf2.
edistatementfileID
WHERE esf2.filetransmitted >= @startdate
AND esf2.filetransmitted < @enddate AND es.guarantorId = gr.
guarantorID)
+
(
SELECT COUNT(*) FROM activitylog al INNER JOIN dbo.PatientProfile pp2
ON al.patientprofileID = pp2.patientprofileid
WHERE pp2.GuarantorId =pp.GuarantorId AND al.created >= @startdate
AND al.created < @enddate AND functionname LIKE '%PrintStatements%')
AS StatementCount
=====================================================================
This is working great. I believe I must add in the dates via a sub-query now
on the last select statement. This seem right? If so, can anyone lend me a
hand?
[code]
SET NOCOUNT ON
declare @startdate datetime,
@enddate datetime
set @startdate = ISNULL(NULL,'1/1/1900')
set @enddate = DATEADD(DAY,1,ISNULL(NULL,'1/1/3000'))
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 ,
CollectionsStatus VARCHAR(255) NULL,
StatementCount int
)
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
(
(NULL IS NOT NULL AND gr.GuarantorId IN(NULL))
OR NULL IS NULL
)
GROUP BY
gr.GuarantorId
ORDER BY
gr.GuarantorId
INSERT #B
SELECT
gr.GuarantorId AS GuarantorId,
pp.PatientProfileId AS PatientProfileId,
ppa.InsBalance AS InsBalance,
ppa.PatBalance AS PatBalance,
ppa.Balance AS Balance,
pc.Description AS Description,
pv.PatientVisitId AS PatientVisitId,
pva.InsPayment AS VisitInsPayment,
pva.PatPayment AS VisitPatPayment,
pva.InsBalance AS VisitInsBalance,
pva.PatBalance AS VisitPatBalance,
cs.Description AS CollectionsStatus,
(
SELECT COUNT(*) FROM edistatement es
INNER JOIN edistatementfile esf2 ON es.edistatementfileID = esf2.edistatementfileID
WHERE esf2.filetransmitted >= @startdate
AND esf2.filetransmitted < @enddate AND es.guarantorId = gr.guarantorID)
+
(
SELECT COUNT(*) FROM activitylog al INNER JOIN dbo.PatientProfile pp2 ON al.patientprofileID = pp2.patientprofileid
WHERE pp2.GuarantorId =pp.GuarantorId AND al.created >= @startdate
AND al.created < @enddate AND functionname LIKE '%PrintStatements%') AS StatementCount
FROM
PatientProfile pp
INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
INNER JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId
INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId AND pc.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN MedLists cs ON pv.CollectionsStatusMId = cs.MedListsId
WHERE -- Filter on Guarantor
(
(NULL IS NOT NULL AND gr.GuarantorId IN(NULL))
OR NULL IS NULL
)
AND -- Filter on Visit Collection Status
(
(NULL IS NOT NULL AND pv.CollectionsStatusMId IN(NULL))
OR NULL IS NULL
)
AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(day,1,ISNULL(NULL,'1/1/3000'))
AND pv.CollectionsNextContactDate >= ISNULL(NULL,'1/1/1900') AND pv.CollectionsNextContactDate < dateadd(day,1,ISNULL(NULL,'1/1/3000'))
AND pv.BillStatus = 13
/*
GROUP BY
gr.GuarantorId,
pp.PatientProfileId,
ppa.InsBalance,
ppa.PatBalance,
ppa.Balance,
pc.Description,
pv.PatientVisitId,
pva.InsPayment,
pva.PatPayment,
pva.InsBalance,
pva.PatBalance,
pv.BillStatus,
cs.Description
HAVING
(pv.BillStatus = 13)
*/
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 --- Filter on Gua
(
(NULL IS NOT NULL AND gr.GuarantorId IN(NULL))
OR NULL 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
(
(NULL IS NOT NULL AND gr.GuarantorId IN(NULL))
OR NULL 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
-- Delete any guarantor where payment made in last 90 days.
IF 0 = 1
BEGIN
SELECT pp.GuarantorID
INTO #UniqueDelete
FROM VisitTransactions vt
JOIN PaymentMethod pm ON vt.PaymentMethodID = pm.PaymentMethodID
JOIN PatientVisit pv ON vt.PatientVisitID = pv.PatientVisitID
JOIN PatientProfile pp ON pv.PatientProfileID = pp.PatientProfileID
JOIN Transactions tc ON vt.VisitTransactionsId = tc.VisitTransactionsId
WHERE pm.Source = 1 AND tc.Type = 'P' AND DATEDIFF(dd,pm.DateofEntry,getdate()) < 90 AND GuarantorID --= @pGuarantorID
IN(
SELECT DISTINCT GuarantorID FROM #A
UNION
SELECT DISTINCT GuarantorID FROM #B
UNION
SELECT DISTINCT GuarantorID FROM #C
UNION
SELECT DISTINCT GuarantorID FROM #GPAll)
AND pp.GuarantorID is not null
DELETE FROM #A WHERE GuarantorID IN(SELECT * FROM #UniqueDelete)
DELETE FROM #B WHERE GuarantorID IN(SELECT * FROM #UniqueDelete)
DELETE FROM #C WHERE GuarantorID IN(SELECT * FROM #UniqueDelete)
DELETE FROM #GPAll WHERE GuarantorID IN(SELECT * FROM #UniqueDelete)
DROP TABLE #UniqueDelete
END
-- End of Last Payment Deletion
SELECT DISTINCT
dbo.FormatName(gr.Prefix , gr.First , gr.Middle , gr.Last , gr.Suffix) AS GuarantorName ,
gr.GuarantorId ,
gr.First AS GuarantorFirst ,
gr.Last AS GuarantorLast ,
ISNULL(Convert(VarChar(20), gr.birthdate, 101),'')as GuarantorDOB,
LEFT(gr.SSN , 3) + '-' + SUBSTRING(gr.SSN , 4 , 2) + '-' + RIGHT(gr.SSN , 4) AS GuarantorSSN ,
'(' + LEFT(gr.Phone1 , 3) + ') ' + SUBSTRING(gr.Phone1 , 4 , 3) + '-' + SUBSTRING(gr.Phone1 , 7 , 4) AS Phone ,
'(' + LEFT(gr.Phone2 , 3) + ') ' + SUBSTRING(gr.Phone2 , 4 , 3) + '-' + SUBSTRING(gr.Phone2 , 7 , 4) AS Phone2 ,
RefA.TotalInsBalance AS GuarantorInsBalance ,
RefA.TotalPatBalance AS GuarantorPatBalance ,
RefA.TotalBalance AS GuarantorBalance ,
pp.PatientId ,
dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName ,
pp.First AS PatientFirst ,
pp.Last AS PatientLast ,
ISNULL(Convert(VarChar(20), pp.birthdate, 101),'')as PatientDOB,
LEFT(pp.SSN , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + RIGHT(pp.SSN , 4) AS PatientSSN ,
ISNULL(dbo.formatphone(pp.phone1 , 1) , '') AS PatientPhone1 ,
ISNULL(pp.Phone1Type , ' ') AS PatientPhone1Type ,
ISNULL(dbo.formatphone(pp.phone2 , 1) , '') AS PatientPhone2 ,
ISNULL(pp.Phone2Type , ' ') AS PatientPhone2Type ,
ISNULL(pp.Address1,'') AS PatientAddress1 ,
ISNULL(pp.Address2, '') AS PatientAddress2 ,
ISNULL(pp.City,'') AS PatientCity ,
ISNULL(pp.State,'') AS PatientState ,
ISNULL(pp.Zip,'') AS PatientZip ,
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.CollectionsStatus ,
ISNULL(ic.ListName, '') AS [Insurance Carrier] ,
ISNULL(pi.InsuredId , '') AS InsuredId ,
RefB.StatementCount
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
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID
AND (
pi.Inactive IS NULL
OR pi.Inactive = 0
)
AND pi.OrderForClaims = 1
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 ABS(RefB.InsBalance) END > NULL)
OR (CASE WHEN 1 = 2 THEN ABS(RefB.PatBalance) 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
(
(NULL IS NOT NULL AND pv.VisitOwnerMID IN(NULL))
OR NULL IS NULL
)
AND -- Filter on Number of Statements Received
((RefB.StatementCount >= 2) OR (2 IS NULL))
/*
GROUP BY
dbo.FormatName(gr.Prefix, gr.First, gr.Middle, gr.Last, gr.Suffix),
LEFT(gr.SSN, 3) + '-' + SUBSTRING(gr.SSN, 4, 2) + '-' + RIGHT(gr.SSN, 4),
'(' + LEFT(gr.Phone1, 3) + ') ' + SUBSTRING(gr.Phone1, 4, 3) + '-' + SUBSTRING(gr.Phone1, 7, 4),
'(' + LEFT(gr.Phone2, 3) + ') ' + SUBSTRING(gr.Phone2, 4, 3) + '-' + SUBSTRING(gr.Phone2, 7, 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.CollectionsStatus
*/
ORDER BY
GuarantorName,
PatientName
DROP TABLE #A
DROP TABLE #B
DROP TABLE #C
DROP TABLE #GPAll
[/code]
June 5, 2009 at 8:41 am
I have no time now to go into this, but you might wish to take a look at the function ROW_NUMBER(). Here is an excellent article on the subject: http://www.sqlservercentral.com/articles/T-SQL/66512/
Greetz,
Hans Brouwer
June 10, 2009 at 3:38 am
Jeff,
I tried to run your query. You join with a table PatientProfile, but there is no such table. Could you provide this table with some data? Are there other missing objects?
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply