Help with a SQL Query - 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 2005 Forums

Transact-SQL (2005)

Help with a SQL Query

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2011-05-10 : 21:59:01

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 ')'.

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.

[CODE]
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
[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-10 : 22:07:11

copy this and paste into the query window and execute. You will see the error message in the message window. Double click on it and it will bring you to the error line.

Which is . . .


	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

You can't have a INSERT INTO and SELECT INTO inside a derived table. The INSERT INTO should not be there ?

KH
[spoiler]Time is always against us[/spoiler]

JeffS23
Posting Yak Master

212 Posts

Posted - 2011-05-10 : 22:11:43

If its not possible to INSERT INTO a derived table, I wonder how I can work around that and still get what I need.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-10 : 22:20:51

you can split into 2 query. First query will INSERT INTO #tmp, second query will then select from #tmp

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-10 : 22:23:00

alternatively, don't use #tmp table
try replacing the LEFT OUTER JOIN derived query with



	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    	       

	UNION ALL	

    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

KH
[spoiler]Time is always against us[/spoiler]

JeffS23
Posting Yak Master

212 Posts

Posted - 2011-05-10 : 22:35:31

Thanks, that did it! You are amazing!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources