Jeff S
asked on
Help with a SQL Query
First and foremost, the SQL is handled dynamically by the SQL Server. Coding in my WHERE clauses may look odd to you, please disregard this. What I need help with is very close to the end of this query. In it I have this JOIN condition:
... statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate > medicarepayment.CheckDate
Well here is the sample date for the full query:
His CheckDate = '2010-07-23 00:00:00.000'
When I look at his Statement Dates I get these:
2010-03-15 11:27:08.000
2010-03-31 11:24:58.000
2010-04-08 12:03:38.000
2010-04-29 07:33:31.000
2010-05-20 19:04:17.000
2010-07-30 12:44:06.000
2010-08-19 10:02:04.000
2010-11-11 09:06:56.000
2010-12-02 09:19:56.000
2010-12-26 17:07:54.000
2011-01-20 12:24:36.000
2011-02-10 16:48:02.000
2011-03-04 08:45:52.000
2011-03-31 09:26:36.000
2011-05-12 08:54:16.000
What I need it the first Date immeadiately after the Checkdate and on this one I get a NULL and can not figure out why.
... statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate
Well here is the sample date for the full query:
His CheckDate = '2010-07-23 00:00:00.000'
When I look at his Statement Dates I get these:
2010-03-15 11:27:08.000
2010-03-31 11:24:58.000
2010-04-08 12:03:38.000
2010-04-29 07:33:31.000
2010-05-20 19:04:17.000
2010-07-30 12:44:06.000
2010-08-19 10:02:04.000
2010-11-11 09:06:56.000
2010-12-02 09:19:56.000
2010-12-26 17:07:54.000
2011-01-20 12:24:36.000
2011-02-10 16:48:02.000
2011-03-04 08:45:52.000
2011-03-31 09:26:36.000
2011-05-12 08:54:16.000
What I need it the first Date immeadiately after the Checkdate and on this one I get a NULL and can not figure out why.
SET NOCOUNT ON
DECLARE
@groupby1 varchar(200),
@groupby2 varchar(200)
SET @groupby1 = convert(varchar(200),'None')
SET @groupby2 = convert(varchar(200),'None')
CREATE TABLE #Summary
(
PatientVisitID INT,
[Date of Entry] DATETIME,
Name VARCHAR(100),
Guarantor VARCHAR(100),
GuarantorId INT,
[Adjustment Type] VARCHAR(75),
TicketNumber VARCHAR(25),
[Procedure Code] VARCHAR(10),
[Adjustment Amount] money,
DateofServiceFrom DATETIME,
Notes VARCHAR(255),
[Financial Class] VARCHAR(200),
[Insurance Carrier] VARCHAR(60),
InsuredID VARCHAR(25),
[Policy Type] VARCHAR(200),
Facility VARCHAR(60),
Group1 VARCHAR(200),
Group2 VARCHAR(200),
MedicaidID VARCHAR(25),
NoRecord VARCHAR(1)
)
INSERT INTO #Summary
SELECT
pv.PatientVisitID,
b.Entry AS [Date Of Entry],
pp.[Last] + ', ' + pp.[First] AS Name,
dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS Guarantor,
ISNULL(g.GuarantorID , 0) AS GuarantorId,
MedLists.Description AS [Adjustment Type],
pv.TicketNumber,
pvp.Code AS [Procedure Code],
td.Amount AS [Adjustment Amount],
pvp.DateOfServiceFrom,
ISNULL(CONVERT(VARCHAR(255), t.Note), ' ') AS Notes,
ISNULL(fc.Description, 'No Financial Class') AS [Financial Class],
ISNULL(ic.ListName,'No Insurance') AS [Insurance Carrier],
ISNULL(pi.InsuredId , '') AS InsuredID,
ISNULL(ml.Description,'No Insurance') AS [Policy Type],
dff.ListName as Facility,
CASE @groupby1
WHEN 'Facility' THEN dff.ListName
WHEN 'Financial Class' THEN ISNULL(fc.Description, 'No Financial Class')
WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName,'No Insurance')
WHEN 'Policy Type' THEN ISNULL(ml.Description,'No Insurance')
ELSE 'None'
END as Group1,
CASE @groupby2
WHEN 'Facility' THEN dff.ListName
WHEN 'Financial Class' THEN ISNULL(fc.Description, 'No Financial Class')
WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName,'No Insurance')
WHEN 'Policy Type' THEN ISNULL(ml.Description,'No Insurance')
ELSE 'None'
END as Group2,
medicareInsurance.InsuredId AS MedicaidId,
ISNULL(medicareInsurance.NoRecord,'') AS NoRecord
FROM
PaymentMethod pm
INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
INNER JOIN TransactionDistributions td ON t.TransactionsId = td.TransactionsId
INNER JOIN Batch b ON pm.BatchId = b.BatchId
INNER JOIN PatientVisit pv ON vt.PatientVisitid = pv.PatientVisitId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT OUTER JOIN MedLists ON t.ActionTypeMId = MedLists.MedListsId
LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
LEFT JOIN PatientInsurance pi ON pv.PrimaryPICarrierId = pi.PatientInsuranceId
LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID
LEFT OUTER JOIN MedLists ml ON ic.PolicyTypeMID = ml.MedListsID
INNER JOIN DoctorFacility dff ON pv.FacilityId = dff.DoctorFacilityId
LEFT OUTER JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID
LEFT OUTER JOIN
(SELECT medicarepvi.patientvisitid,medicarepi.InsuredID, NoRecord='X' FROM PatientVisitInsurance medicarepvi INNER JOIN patientinsurance medicarepi ON medicarepvi.PatientInsuranceID = medicarepi.PatientInsuranceID
WHERE medicarepi.InsuranceCarriersId IN (4,3,7,2)) medicareInsurance ON medicareInsurance.patientvisitid = pv.patientvisitid
WHERE
t.Type = 'A'
AND --Filter on Source of Adjustment
(
('3' = pm.Source) OR
('3' = '3')
)
AND --Filter on Carrier
(
(NULL IS NOT NULL AND pv.PrimaryInsuranceCarriersID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on adjustment type
(
(2 = 2 AND t.ActionTypeMId IN (3444)) OR
(2 = 1)
)
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)
)
AND --Filter on date range
(
(1 = 1 AND b.Entry >= ISNULL('01/01/2010', '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000'))) OR
(1 <> 1 AND pm.DepositDate >= ISNULL('01/01/2010', '1/1/1900') AND pm.DepositDate < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000')))
)
AND --Filter on date range
(
(pvp.DateofServiceFrom >= ISNULL(NULL, '1/1/1900') AND pvp.DateofServiceFrom < DATEADD(d,1,ISNULL(NULL,'1/1/3000')))
)
AND --Filter on procedures
(
(NULL IS NOT NULL AND pvp.ProceduresId IN (NULL)) OR
(NULL IS NULL)
)
ORDER BY
pp.[Last] + ', ' + pp.[First],
pvp.DateOfServiceFrom
IF 2 = 1 AND NULL IS NOT NULL
INSERT INTO #Summary
SELECT
pv.PatientVisitID,
b.Entry AS [Date Of Entry],
pp.[Last] + ', ' + pp.[First] AS Name,
dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS Guarantor,
ISNULL(g.GuarantorID , 0) AS GuarantorId,
'FQHC Reimbursement' AS [Adjustment Type],
pv.TicketNumber,
pvp.Code AS [Procedure Code],
-1 * pvp.TotalFee AS [Adjustment Amount],
pvp.DateOfServiceFrom,
' ' AS Notes,
ISNULL(fc.Description, 'No Financial Class') AS [Financial Class],
ISNULL(ic.ListName,'No Insurance') AS [Insurance Carrier],
ISNULL(pi.InsuredId , '') AS InsuredID,
ISNULL(ml.Description,'No Insurance') AS [Policy Type],
dff.ListName as Facility,
CASE @groupby1
WHEN 'Facility' THEN dff.ListName
WHEN 'Financial Class' THEN ISNULL(fc.Description, 'No Financial Class')
WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName,'No Insurance')
WHEN 'Policy Type' THEN ISNULL(ml.Description,'No Insurance')
ELSE 'None'
END as Group1,
CASE @groupby2
WHEN 'Facility' THEN dff.ListName
WHEN 'Financial Class' THEN ISNULL(fc.Description, 'No Financial Class')
WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName,'No Insurance')
WHEN 'Policy Type' THEN ISNULL(ml.Description,'No Insurance')
ELSE 'None'
END as Group2,
medicareInsurance.InsuredId AS MedicaidId,
ISNULL(medicareInsurance.NoRecord,'') AS NoRecord
FROM
PatientVisitProcs pvp
INNER JOIN PatientVisit pv ON pvp.PatientVisitID = pv.PatientVisitID
INNER JOIN Batch b ON pvp.BatchId = b.BatchId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
LEFT JOIN PatientInsurance pi ON pv.PrimaryPICarrierId = pi.PatientInsuranceId
LEFT OUTER JOIN MedLists ml ON ic.PolicyTypeMID = ml.MedListsID
LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID
INNER JOIN DoctorFacility dff ON pv.FacilityId = dff.DoctorFacilityId
LEFT OUTER JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID
LEFT OUTER JOIN
(SELECT medicarepvi.patientvisitid,medicarepi.InsuredID, NoRecord='X' FROM PatientVisitInsurance medicarepvi INNER JOIN patientinsurance medicarepi ON medicarepvi.PatientInsuranceID = medicarepi.PatientInsuranceID
WHERE medicarepi.InsuranceCarriersId IN (4,3,7,2)) medicareInsurance ON medicareInsurance.patientvisitid = pv.patientvisitid
WHERE
--Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Carrier
(
(NULL IS NOT NULL AND pv.PrimaryInsuranceCarriersID 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)
)
AND --Filter on date range
(
(b.Entry >= ISNULL('01/01/2010', '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000')))
)
AND --Filter on FQHC procedures
(
(NULL IS NOT NULL AND pvp.ProceduresId IN (NULL)) OR
(NULL IS NULL)
)
ORDER BY
pp.[Last] + ', ' + pp.[First],
pvp.DateOfServiceFrom
SELECT
s.PatientVisitID,
s.[Date of Entry],
s.[Name],
s.Guarantor,
s.GuarantorId,
s.[Adjustment Type],
s.TicketNumber,
s.[Procedure Code],
s.[Adjustment Amount],
s.DateofServiceFrom,
s.Notes,
s.[Financial Class],
s.[Insurance Carrier],
s.InsuredID,
s.[Policy Type],
s.Facility,
s.Group1,
s.Group2,
s.MedicaidID,
s.NoRecord,
medicarepayment.amount AS MedicarePayment,
medicarepayment.CheckDate,
statementdte.statementdate AS StatementDate
FROM
#Summary s
INNER JOIN dbo.Guarantor g ON s.GuarantorId = g.GuarantorId
LEFT OUTER JOIN
(
SELECT
COALESCE(pm.CheckDate, pm.DateofEntry) AS Checkdate,
pm.DateOfEntry AS [Date Of Entry],
pv.PatientVisitID,
pv.TicketNumber,
pvp.Code,
CASE WHEN pm.Source = 2 THEN td.Amount
ELSE 0.0000
END AS Amount,
pvp.DateOfServiceFrom
FROM
PaymentMethod pm
INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
INNER JOIN Transactions tc ON vt.VisitTransactionsId = tc.VisitTransactionsId
INNER JOIN TransactionDistributions td ON tc.TransactionsId = td.TransactionsId
INNER JOIN PatientVisit pv ON vt.PatientVisitid = pv.PatientVisitId
INNER JOIN MedLists ml ON tc.ActionTypeMId = ml.MedListsId
LEFT JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
INNER JOIN InsuranceCarriers ic ON vt.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT OUTER JOIN MedLists ml2 ON ic.PolicyTypeMID = ml2.MedListsID
WHERE
tc.Type = 'P'
AND td.Amount <> 0.00
AND ml2.Description LIKE 'Medicare%'
) medicarepayment ON s.ticketnumber = medicarepayment.ticketnumber AND s.PatientVisitID = medicarepayment.PatientVisitID /*AND s.[Procedure Code] = medicarepayment.code*/
LEFT OUTER JOIN
(
SELECT StatementDate, GuarantorId
FROM
(
SELECT
MIN(esf.filetransmitted) AS StatementDate, g.GuarantorId
FROM
edistatement es
INNER JOIN edistatementfile esf ON es.edistatementfileID = esf.edistatementfileID
LEFT JOIN guarantor g ON es.guarantorID = g.guarantorID
GROUP BY g.GuarantorID
/*
UNION ALL
SELECT
MIN(al.Created) AS StatementDate,
g.GuarantorId
FROM
ActivityLog al
INNER JOIN patientprofile pp ON al.patientprofileID = pp.patientprofileID
INNER JOIN guarantor g ON pp.guarantorID = g.guarantorID
WHERE functionname LIKE '%PrintStatements%'
GROUP BY g.GuarantorID
*/
) A
) statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate > medicarepayment.CheckDate
WHERE s.TicketNumber = '049602'
ORDER BY s.[Name], s.TicketNumber, statementdte.statementdate DESC
DROP TABLE #Summary
ASKER
Executing the first subquery give me the earliest date on the entire table - '2010-03-15 11:27:08.000'. The second one gives me the same. Because I am telling it ...
.... statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate > medicarepayment.CheckDate
Because the StatmentDate is not > the the CheckDate its giving me a NULL. I can see the problem, just need to work around it somehow.
.... statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate
Because the StatmentDate is not > the the CheckDate its giving me a NULL. I can see the problem, just need to work around it somehow.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks kindly.
As for the query in question, I can suggest an approach for debugging it (and, without the data in tables, that's about all I can do).
Start with the inner most query and execute just that subquery to see if you get back data (and, more specifically, the data you expect), e.g.:
Open in new window
Then work your way out:
Open in new window
Eventually, you will hit a spot where the data you expect doesn't appear. That points to where the problem is.