[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

SQL Query Guru Help Needed for Difficult Query

What I need assistance on if this query is the StatementDate field. Right now, I have a GetDate() placeholder in there until I can figure out how to get my value. Essentially, what I need is the first date a patient received a statement immeadiately after Medicare paid. I've added in the Medicare payment date and how I came about that right above the Join i tried to make and have commented out. I am confused on how I can get this one value to complete this query. Any help is appreciated.
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 
	(
	[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     
	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 (160)) medicareInsurance ON medicareInsurance.patientvisitid = pv.patientvisitid 

WHERE     
	t.Type = 'A'
	AND  --Filter on date range
	(
	(1 = 1 AND b.Entry >= ISNULL(NULL, '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))) OR
	(1 <> 1 AND pm.DepositDate >= ISNULL(NULL, '1/1/1900') AND pm.DepositDate < DATEADD(d,1,ISNULL(NULL,'1/1/3000')))
	)

ORDER BY 
	pp.[Last] + ', ' + pp.[First], 
	pvp.DateOfServiceFrom

SELECT 
	s.*,
	medicarepayment.amount AS MedicarePayment, 
	medicarepayment.CheckDate,
	GETDATE()AS StatementDate 

FROM 
	#Summary s 
	LEFT OUTER JOIN
	(
	SELECT  
		COALESCE(pm.CheckDate, pm.DateofEntry) AS Checkdate,
		pm.DateOfEntry AS [Date Of Entry],
		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 = 'Medicare'
	) medicarepayment ON s.ticketnumber = medicarepayment.ticketnumber --- AND s.[Procedure Code] = medicarepayment.code 
	
/*	
	LEFT OUTER JOIN
	(
	SELECT TOP 1
		 esf.filetransmitted, 
		 g.GuarantorId
	FROM    
		edistatement es
        INNER JOIN edistatementfile esf ON es.edistatementfileID = esf.edistatementfileID
        INNER JOIN guarantor g ON es.guarantorID = g.guarantorID		
    WHERE 
		-- < conditions here (first statement after medicares payment) >     
	ORDER BY 
		esf.FileTransmitted 
		) statementdate ON s.GuarantorId = statementdate.GuarantorId
*/		
		 
ORDER BY s.[Name], s.TicketNumber

DROP TABLE #Summary

Open in new window

0
Jeff S
Asked:
Jeff S
  • 2
  • 2
1 Solution
 
SharathData EngineerCommented:
Can you post the result of your current query and the expected result?
0
 
Jeff SAuthor Commented:
Sharath -

I am getting back a Statement Date finally thats not GetDate() any more with the revised code below. Only problem now is I need to pull my Statements via two methods and tried to code it as below. Unfortunately I am getting the following error:

Msg 156, Level 15, State 1, Line 284
Incorrect syntax near the keyword 'INTO'.
Msg 102, Level 15, State 1, Line 310
Incorrect syntax near ')'.

By the way the SQL is handled dynamically by the server therefore some items in my WHERE clause will look odd to you. Please try to disregard this.
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 
	(
	[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     
	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 (160)) 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
	(
	(1 = 2 AND t.ActionTypeMId IN (NULL)) OR
	(1 = 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(NULL, '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))) OR
	(1 <> 1 AND pm.DepositDate >= ISNULL(NULL, '1/1/1900') AND pm.DepositDate < DATEADD(d,1,ISNULL(NULL,'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     
	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 (160)) 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(NULL, '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL(NULL,'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.[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.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 = 'Medicare'
	) medicarepayment ON s.ticketnumber = medicarepayment.ticketnumber AND s.[Procedure Code] = medicarepayment.code 	
	LEFT OUTER JOIN
	(
	SELECT 
		 MAX(esf.filetransmitted) AS StatementDate, 
		 g.GuarantorId
	INTO #Tmp	 
	FROM    
		edistatement es
		INNER JOIN edistatementfile esf ON es.edistatementfileID = esf.edistatementfileID
		INNER JOIN guarantor g ON es.guarantorID = g.guarantorID
	GROUP BY g.GuarantorId    	       
    INSERT INTO #Tmp
		(
		StatementDate, 
		GuarantorID
		)
    SELECT 
		MAX(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
 	SELECT  
 		StatementDate,
		GuarantorId
	FROM    
		#Tmp      	    
		) statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate > medicarepayment.CheckDate
		
ORDER BY s.[Name], s.TicketNumber, statementdte.statementdate  DESC

DROP TABLE #Summary

Open in new window

0
 
SharathData EngineerCommented:
check this.
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 
	(
	[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     
	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 (160)) 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
	(
	(1 = 2 AND t.ActionTypeMId IN (NULL)) OR
	(1 = 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(NULL, '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))) OR
	(1 <> 1 AND pm.DepositDate >= ISNULL(NULL, '1/1/1900') AND pm.DepositDate < DATEADD(d,1,ISNULL(NULL,'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

SELECT 
		 MAX(esf.filetransmitted) AS StatementDate, 
		 g.GuarantorId
	INTO #Tmp	 
	FROM    
		edistatement es
		INNER JOIN edistatementfile esf ON es.edistatementfileID = esf.edistatementfileID
		INNER JOIN guarantor g ON es.guarantorID = g.guarantorID
	GROUP BY g.GuarantorId    	       
    
    INSERT INTO #Tmp
		(
		StatementDate, 
		GuarantorID
		)
    SELECT 
		MAX(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
 	
IF 2 = 1 AND NULL IS NOT NULL 

INSERT INTO #Summary

SELECT     
	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 (160)) 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(NULL, '1/1/1900') AND b.Entry < DATEADD(d,1,ISNULL(NULL,'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.[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.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 = 'Medicare'
	) medicarepayment ON s.ticketnumber = medicarepayment.ticketnumber AND s.[Procedure Code] = medicarepayment.code 	
	LEFT OUTER JOIN
	
		#Tmp statementdte ON s.GuarantorId = statementdte.GuarantorId AND statementdte.StatementDate > medicarepayment.CheckDate
		
ORDER BY s.[Name], s.TicketNumber, statementdte.statementdate  DESC

DROP TABLE #Summary

Open in new window

0
 
Jeff SAuthor Commented:
Thank you kindly for your assistance.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now