Temp Table help 1

jeffsturgeon2002 · Feb 16, 2007

First off, I am still relatively new to SQL and trying out a Temp table for the first time. I am still in a massive learning curve!!

I am currently getting the following Error in Query Analyzer and can not figure out the error in my coding (please again .... new to SQL)

Code:

Server: Msg 170, Level 15, State 1, Line 122
Line 122: Incorrect syntax near 'Name'.
Server: Msg 170, Level 15, State 1, Line 248
Line 248: Incorrect syntax near 'Name'.

The coding I want to attempt is seperating out the Current carrier and the secondary carriers ... they share most of the same information, but I need a way to seperate them, then merge them together ... Im positive im missing a few steps and if anyone can be so helpful and point out my errors, I would deeply appreciate it.

MAIN QUERY (as it stands so far)

Code:

SET NOCOUNT ON

SELECT 
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name], 
	  pp.patientid AS [Patient ID],
	  pv.TicketNumber AS [Ticket Number], 
	  pv.Visit AS [Date Of Service], 
	  df.ListName AS [Doctor Name], 
	  df1.ListName AS [Facility Name], 
	  ml.Description AS [Financial Class],
	  df2.ListName AS [Company Name], 
	  pva.insbalance AS [Visit Ins Balance],
	  pva.patbalance AS [Visit Pat Balance],
	  pva.insbalance + pva.patbalance AS [Total Visit Balance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS [Phone Number], 
	  ISNULL(pp.Phone1Type, ' ') AS [Phone Type], 
	  ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS [Patient SSN],
	  CONVERT(varchar,pp.Birthdate,101) AS [Birth Date],
	  CASE WHEN pp.Sex = 'M' THEN 'Male'WHEN pp.Sex = 'F' THEN 'Female' ELSE IsNull(pp.Sex,'')END AS [Patient Sex],
          g.[Last] + ', ' + g.[First] AS [Guarantor Name], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [Guarantor Addr],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [Guarantor CSZ], 

         CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.First
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.First
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.First
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.First
		ELSE IsNull(pins1.[First],'')
	 	END AS [Current Ins FirstName],

	 CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Last
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.Last
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.Last
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Last
		ELSE IsNull(pins1.[Last],'')
		END AS [CurrentIns LastName],

	 CASE 	WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Address1
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.address1
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.address1
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Address1
		ELSE IsNull(pins1.address1,'')
		END AS [CurrentIns Addr 1],

	 CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Address2
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.address2
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.address2
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Address2
		ELSE IsNull(pins1.address2,'')
		END AS [CurrentIns Addr 2],

	 CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.City
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.City
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.City
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.City
		ELSE IsNull(pins1.city,'')
		END AS [CurrentIns City],

	CASE    WHEN pins1.InsuredSameAsPatient = 1 THEN pp.State
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.State
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.State
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.State
		ELSE IsNull(pins1.State,'')
		END AS [CurrentIns State],

	CASE    WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Zip
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.zip
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.Zip
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.zip
		ELSE IsNull(pins1.zip,'')
		END AS [CurrentIns Zip],

       	ic1.ListName AS [CurrentIns Carrier], 
	ISNULL(pins1.InsuredId, '') AS [CurrentIns Insured ID], 
	ISNULL(pins1.groupid,'')AS [CurrentIns Group ID],
	ISNULL(CONVERT(VARCHAR,pins1.InsCardEffectiveDate,101),'No Effective Date') AS [CurrentIns Eff Date], 
        ISNULL(CONVERT(VARCHAR,pins1.InsCardTerminationDate,101),'No Termination Date') AS [CurrentIns Term Date]

INTO #CURRENT

FROM    PatientVisit pv
	LEFT JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN insurancecarriers ic1 ON pv.currentinsuranceCarriersid = ic1.insurancecarriersid
	LEFT JOIN patientinsurance pins1 on pv.patientprofileID = pins1.patientprofileID and pins1.orderforclaims=1
-- 	LEFT JOIN insurancecarriers ic1 on pins1.insurancecarriersID = ic1.insurancecarriersID
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	LEFT JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId  
	LEFT JOIN DoctorFacility df2 ON pv.CompanyId = df2.DoctorFacilityId 
	LEFT JOIN MedLists ml ON pv.FinancialClassMId = ml.MedListsId

WHERE  --- 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 Primary Carrier
	(
	(NULL IS NOT NULL AND pins1.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND -- Filter on Primary Insurance Termination Date
	(
	(pins1.InsCardTerminationDate IS NULL OR (pins1.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins1.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Primary Insurance Effective Date
	(
	(pins1.InsCardEffectiveDate IS NULL OR (pins1.InsCardEffectiveDate >= ISNULL(NULL, '01/01/1900') AND pins1.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)

INSERT INTO #CURRENT (Patient Name,Patient ID, Ticket Number, Date Of Service, Doctor Name, Facility Name, Financial Class, Company Name, 
	'0','0','0',Patient Address,Patient CSZ,Phone Number,Phone Type,Patient SSN,Birth Date,Patient Sex,Guarantor Name,Guarantor Addr,
	Guarantor CSZ,CurrentIns FirstName,CurrentIns LastName,CurrentIns Addr 1,CurrentIns Addr 2,CurrentIns City,CurrentIns State,
	CurrentIns Zip,CurrentIns Carrier, CurrentIns Insured ID, CurrentIns Group ID, CurrentIns Eff Date, CurrentIns Term Date)

SELECT	  
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name], 
	  pp.patientid AS [Patient ID],
	  pv.TicketNumber AS [Ticket Number], 
	  pv.Visit AS [Date Of Service], 
	  df.ListName AS [Doctor Name], 
	  df1.ListName AS [Facility Name], 
	  ml.Description AS [Financial Class],
	  df2.ListName AS [Company Name], 
	  pva.insbalance AS [Visit Ins Balance],
	  pva.patbalance AS [Visit Pat Balance],
	  pva.insbalance + pva.patbalance AS [Total Visit Balance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS [Phone Number], 
	  ISNULL(pp.Phone1Type, ' ') AS [Phone Type], 
	  ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS [Patient SSN],
	  CONVERT(varchar,pp.Birthdate,101) AS [Birth Date],
	  CASE WHEN pp.Sex = 'M' THEN 'Male'WHEN pp.Sex = 'F' THEN 'Female' ELSE IsNull(pp.Sex,'')END AS [Patient Sex],
          g.[Last] + ', ' + g.[First] AS [Guarantor Name], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [Guarantor Addr],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [Guarantor CSZ], 
		
	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.First
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.First
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.First
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.First
		ELSE IsNull(pins2.[First],'')
		END AS [Secondary Ins First Name],

	  CASE 	WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Last
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.Last
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.Last
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.Last
		ELSE IsNull(pins2.[Last],'')
		END AS [Secondary Ins Last Name],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Address1
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.address1
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.address1
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.Address1
		ELSE IsNull(pins2.address1,'')
		END AS [Secondary Ins Address 1],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Address2
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.address2
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.address2
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.Address2
		ELSE IsNull(pins2.address2,'')
		END AS [Secondary Ins Address 2],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.City
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.City
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.City
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.City
		ELSE IsNull(pins2.city,'')
		END AS [Secondary Ins City],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.State
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.State
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.State
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.State
		ELSE IsNull(pins2.State,'')
		END AS [Secondary Ins State],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Zip
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.zip
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.Zip
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.zip
		ELSE IsNull(pins2.zip,'')
		END AS [Secondary Ins Zip],

        ISNULL(ic2.ListName,'No Secondary Ins') AS [Secondary Insurance Carrier], 
	ISNULL(pins2.InsuredId, 'N/A') AS [Secondary Insured ID], 
	ISNULL(pins2.groupid,'N/A')AS [Secondary Group ID],    
	ISNULL(CONVERT(VARCHAR,pins2.InsCardEffectiveDate,101),'No Effective Date') AS [SecIns EffDate], 
        ISNULL(CONVERT(VARCHAR,pins2.InsCardTerminationDate,101),'No Termination Date') AS [SecIns TermDate]

INTO #SECONDARY

FROM    PatientVisit pv
	LEFT JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN patientinsurance pins2 on pv.patientprofileID = pins2.patientprofileID and pins2.orderforclaims=2
	LEFT JOIN insurancecarriers ic2 on pins2.insurancecarriersID = ic2.insurancecarriersID
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	LEFT JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId  
	LEFT JOIN DoctorFacility df2 ON pv.CompanyId = df2.DoctorFacilityId 
	LEFT JOIN MedLists ml ON pv.FinancialClassMId = ml.MedListsId

WHERE  --- 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 secondary Carrier
	(
	(NULL IS NOT NULL AND pins2.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND -- Filter on Secondary Insurance Termination Date
	(
	(pins2.InsCardTerminationDate IS NULL OR (pins2.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins2.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Secondary Insurance Effective Date
	(
	(pins2.InsCardEffectiveDate IS NULL OR (pins2.InsCardEffectiveDate >= ISNULL(NULL, '01/01/1900') AND pins2.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)
	 AND  --Filter on Patient 
        ( 
        (NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR 
        (NULL IS NULL) 
        ) 

INSERT INTO #SECONDARY (Patient Name,Patient ID, Ticket Number, Date Of Service, Doctor Name, Facility Name, Financial Class, Company Name, 
			'0', '0', '0', Patient Address, Patient CSZ, Phone Number, Phone Type, Patient SSN, Birth Date, Patient Sex,
          		Guarantor Name, Guarantor Addr, Guarantor CSZ, Secondary Ins First Name, Secondary Ins Last Name, Secondary Ins Address 1,
			Secondary Ins Address 2, Secondary Ins City, Secondary Ins State, Secondary Ins Zip, Secondary Insurance Carrier, 
			Secondary Insured ID, Secondary Group ID, SecIns EffDate, SecIns TermDate)

DROP TABLE #CURRENT
DROP TABLE #SECONDARY

Jeff

SELECT * FROM users WHERE clue > 0

gmmastros · Feb 16, 2007

You apparently have fields with spaces in their name

INSERT INTO #CURRENT ([!]Patient Name[/!],Patient ID,

You can either remove the spaces (which is what I would do), or you can surround the field names with square brackets, like this...

INSERT INTO #CURRENT ([!][[/!]Patient Name[!]][/!],[!][[/!]Patient ID[!]][/!], etc....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

jeffsturgeon2002 · Feb 16, 2007

I had spaces ... ill bracket them in ... your wonderful George ... I can always count on you!

Jeff

SELECT * FROM users WHERE clue > 0

monksnake · Feb 16, 2007

You need to put [] around each column name in your temporary tables. (Not ever column name, just the ones that have spaces in them and/or the word name)

The spaces in the column names is a bad practice, it's best to used mixed case or _.

[small]"There's an old saying in Tennessee — I know it's in Texas, probably in Tennessee — that says, fool me once, shame on — shame on you. Fool me — you can't get fooled again." - George W. Bush[/small]
<.

monksnake · Feb 16, 2007

Damn George, toooooooo fast.

[small]"There's an old saying in Tennessee — I know it's in Texas, probably in Tennessee — that says, fool me once, shame on — shame on you. Fool me — you can't get fooled again." - George W. Bush[/small]
<.

jeffsturgeon2002 · Feb 16, 2007

George or Monksnake,

I tried the []'s and now Im getting this

Code:

Server: Msg 156, Level 15, State 1, Line 205
Incorrect syntax near the keyword 'INTO'.
Server: Msg 156, Level 15, State 1, Line 254
Incorrect syntax near the keyword 'DROP'.

REVISED MAIN QUERY

Code:

/* Patient Insurance List Revised to Patient Insurance from Visit*/

SET NOCOUNT ON

SELECT 
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name], 
	  pp.patientid AS [Patient ID],
	  pv.TicketNumber AS [Ticket Number], 
	  pv.Visit AS [Date Of Service], 
	  df.ListName AS [Doctor Name], 
	  df1.ListName AS [Facility Name], 
	  ml.Description AS [Financial Class],
	  df2.ListName AS [Company Name], 
	  pva.insbalance AS [Visit Ins Balance],
	  pva.patbalance AS [Visit Pat Balance],
	  pva.insbalance + pva.patbalance AS [Total Visit Balance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS [Phone Number], 
	  ISNULL(pp.Phone1Type, ' ') AS [Phone Type], 
	  ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS [Patient SSN],
	  CONVERT(varchar,pp.Birthdate,101) AS [Birth Date],
	  CASE WHEN pp.Sex = 'M' THEN 'Male'WHEN pp.Sex = 'F' THEN 'Female' ELSE IsNull(pp.Sex,'')END AS [Patient Sex],
          g.[Last] + ', ' + g.[First] AS [Guarantor Name], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [Guarantor Addr],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [Guarantor CSZ], 

         CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.First
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.First
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.First
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.First
		ELSE IsNull(pins1.[First],'')
	 	END AS [Current Ins FirstName],

	 CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Last
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.Last
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.Last
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Last
		ELSE IsNull(pins1.[Last],'')
		END AS [CurrentIns LastName],

	 CASE 	WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Address1
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.address1
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.address1
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Address1
		ELSE IsNull(pins1.address1,'')
		END AS [CurrentIns Addr 1],

	 CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Address2
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.address2
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.address2
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Address2
		ELSE IsNull(pins1.address2,'')
		END AS [CurrentIns Addr 2],

	 CASE   WHEN pins1.InsuredSameAsPatient = 1 THEN pp.City
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.City
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.City
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.City
		ELSE IsNull(pins1.city,'')
		END AS [CurrentIns City],

	CASE    WHEN pins1.InsuredSameAsPatient = 1 THEN pp.State
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.State
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.State
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.State
		ELSE IsNull(pins1.State,'')
		END AS [CurrentIns State],

	CASE    WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Zip
		WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.zip
		WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.Zip
		WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.zip
		ELSE IsNull(pins1.zip,'')
		END AS [CurrentIns Zip],

       	ic1.ListName AS [CurrentIns Carrier], 
	ISNULL(pins1.InsuredId, '') AS [CurrentIns Insured ID], 
	ISNULL(pins1.groupid,'')AS [CurrentIns Group ID],
	ISNULL(CONVERT(VARCHAR,pins1.InsCardEffectiveDate,101),'No Effective Date') AS [CurrentIns Eff Date], 
        ISNULL(CONVERT(VARCHAR,pins1.InsCardTerminationDate,101),'No Termination Date') AS [CurrentIns Term Date]

INTO #CURRENT

FROM    PatientVisit pv
	LEFT JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN insurancecarriers ic1 ON pv.currentinsuranceCarriersid = ic1.insurancecarriersid
	LEFT JOIN patientinsurance pins1 on pv.patientprofileID = pins1.patientprofileID and pins1.orderforclaims=1
-- 	LEFT JOIN insurancecarriers ic1 on pins1.insurancecarriersID = ic1.insurancecarriersID
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	LEFT JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId  
	LEFT JOIN DoctorFacility df2 ON pv.CompanyId = df2.DoctorFacilityId 
	LEFT JOIN MedLists ml ON pv.FinancialClassMId = ml.MedListsId

WHERE  --- 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 Primary Carrier
	(
	(NULL IS NOT NULL AND pins1.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND -- Filter on Primary Insurance Termination Date
	(
	(pins1.InsCardTerminationDate IS NULL OR (pins1.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins1.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Primary Insurance Effective Date
	(
	(pins1.InsCardEffectiveDate IS NULL OR (pins1.InsCardEffectiveDate >= ISNULL(NULL, '01/01/1900') AND pins1.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)

INSERT INTO #CURRENT([Patient Name],[Patient ID],[Ticket Number],[Date Of Service],[Doctor Name],[Facility Name],[Financial Class],[Company Name],
	['0'],['0'],['0'],[Patient Address],[Patient CSZ],[Phone Number],[Phone Type],[Patient SSN],[Birth Date],[Patient Sex],[Guarantor Name],[Guarantor Addr],
	[Guarantor CSZ],[CurrentIns FirstName],[CurrentIns LastName],[CurrentIns Addr 1],[CurrentIns Addr 2],[CurrentIns City],[CurrentIns State],
	[CurrentIns Zip],[CurrentIns Carrier],[CurrentIns Insured ID],[CurrentIns Group ID],[CurrentIns Eff Date],[CurrentIns Term Date])

SELECT	  
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name], 
	  pp.patientid AS [Patient ID],
	  pv.TicketNumber AS [Ticket Number], 
	  pv.Visit AS [Date Of Service], 
	  df.ListName AS [Doctor Name], 
	  df1.ListName AS [Facility Name], 
	  ml.Description AS [Financial Class],
	  df2.ListName AS [Company Name], 
	  pva.insbalance AS [Visit Ins Balance],
	  pva.patbalance AS [Visit Pat Balance],
	  pva.insbalance + pva.patbalance AS [Total Visit Balance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS [Phone Number], 
	  ISNULL(pp.Phone1Type, ' ') AS [Phone Type], 
	  ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS [Patient SSN],
	  CONVERT(varchar,pp.Birthdate,101) AS [Birth Date],
	  CASE WHEN pp.Sex = 'M' THEN 'Male'WHEN pp.Sex = 'F' THEN 'Female' ELSE IsNull(pp.Sex,'')END AS [Patient Sex],
          g.[Last] + ', ' + g.[First] AS [Guarantor Name], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [Guarantor Addr],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [Guarantor CSZ], 
		
	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.First
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.First
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.First
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.First
		ELSE IsNull(pins2.[First],'')
		END AS [Secondary Ins First Name],

	  CASE 	WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Last
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.Last
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.Last
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.Last
		ELSE IsNull(pins2.[Last],'')
		END AS [Secondary Ins Last Name],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Address1
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.address1
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.address1
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.Address1
		ELSE IsNull(pins2.address1,'')
		END AS [Secondary Ins Address 1],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Address2
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.address2
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.address2
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.Address2
		ELSE IsNull(pins2.address2,'')
		END AS [Secondary Ins Address 2],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.City
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.City
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.City
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.City
		ELSE IsNull(pins2.city,'')
		END AS [Secondary Ins City],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.State
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.State
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.State
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.State
		ELSE IsNull(pins2.State,'')
		END AS [Secondary Ins State],

	  CASE  WHEN pins2.InsuredSameAsPatient = 1 THEN pp.Zip
		WHEN pins2.InsuredSameAsGuarantor = 1 THEN g.zip
		WHEN pins2.InsuredSameAsGuarantor IS NULL THEN g.Zip
		WHEN pins2.InsuredSameAsPatient IS NULL THEN pp.zip
		ELSE IsNull(pins2.zip,'')
		END AS [Secondary Ins Zip],

        ISNULL(ic2.ListName,'No Secondary Ins') AS [Secondary Insurance Carrier], 
	ISNULL(pins2.InsuredId, 'N/A') AS [Secondary Insured ID], 
	ISNULL(pins2.groupid,'N/A')AS [Secondary Group ID],    
	ISNULL(CONVERT(VARCHAR,pins2.InsCardEffectiveDate,101),'No Effective Date') AS [SecIns EffDate], 
        ISNULL(CONVERT(VARCHAR,pins2.InsCardTerminationDate,101),'No Termination Date') AS [SecIns TermDate]

INTO #SECONDARY

FROM    PatientVisit pv
	LEFT JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN patientinsurance pins2 on pv.patientprofileID = pins2.patientprofileID and pins2.orderforclaims=2
	LEFT JOIN insurancecarriers ic2 on pins2.insurancecarriersID = ic2.insurancecarriersID
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	LEFT JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId  
	LEFT JOIN DoctorFacility df2 ON pv.CompanyId = df2.DoctorFacilityId 
	LEFT JOIN MedLists ml ON pv.FinancialClassMId = ml.MedListsId

WHERE  --- 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 secondary Carrier
	(
	(NULL IS NOT NULL AND pins2.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND -- Filter on Secondary Insurance Termination Date
	(
	(pins2.InsCardTerminationDate IS NULL OR (pins2.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins2.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Secondary Insurance Effective Date
	(
	(pins2.InsCardEffectiveDate IS NULL OR (pins2.InsCardEffectiveDate >= ISNULL(NULL, '01/01/1900') AND pins2.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)
	 AND  --Filter on Patient 
        ( 
        (NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR 
        (NULL IS NULL) 
        ) 

INSERT INTO #SECONDARY ([Patient Name],[Patient ID],[Ticket Number],[Date Of Service],[Doctor Name],[Facility Name],[Financial Class],[Company Name],
			['0'],['0'],['0'],[Patient Address],[Patient CSZ],[Phone Number],[Phone Type],[Patient SSN],[Birth Date],[Patient Sex],
          		[Guarantor Name], [Guarantor Addr], [Guarantor CSZ],[Secondary Ins First Name],[Secondary Ins Last Name],[Secondary Ins Address 1],
			[Secondary Ins Address 2],[Secondary Ins City],[Secondary Ins State],[Secondary Ins Zip],[Secondary Insurance Carrier], 
			[Secondary Insured ID],[Secondary Group ID],[SecIns EffDate],[SecIns TermDate])

DROP TABLE #CURRENT
DROP TABLE #SECONDARY

Jeff

SELECT * FROM users WHERE clue > 0

gmmastros · Feb 16, 2007

If you remove all the tables and fields, your query reduces to....

[tt][blue]
SELECT Fields....
INTO #CURRENT
FROM tables....
WHERE --- Filter on Doctor

INSERT INTO #CURRENT(fields...)
SELECT fields....
INTO #SECONDARY
FROM tables....
WHERE --- Filter on Doctor
INSERT INTO #SECONDARY (fields....)

DROP TABLE #CURRENT
DROP TABLE #SECONDARY
[/blue][/tt]

This format is just plain wrong. There are 2 ways to put data in to a temp table.

1:
[tt][blue]
Select fields
Into #Temp
From tables
Where whatever...
[/blue][/tt]

2:

[tt][blue]
Create Table #Temp(Fields....)
Insert Into #Temp(Fields...)
Select Fields...
From Tables
Where .....
[/blue][/tt]

For various reasons, method #2 is better, but does require more work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

chrissie1 · Feb 16, 2007

hey, you know how much easier your life would be if you had ten different smaller queries, rather than just one that does everything

Christiaan Baes
Belgium

"My new site" - Me

SQLDenis · Feb 16, 2007

Okay this code is giving me a serious headache, i have to say something ;-)

Why do you have 5 checks here?
instead of

Code:

CASE     WHEN pins1.InsuredSameAsPatient = 1 THEN pp.Address1
        WHEN pins1.InsuredSameAsGuarantor = 1 THEN g.address1
        WHEN pins1.InsuredSameAsGuarantor IS NULL THEN g.address1
        WHEN pins1.InsuredSameAsPatient IS NULL THEN pp.Address1
        ELSE IsNull(pins1.address1,'')
        END AS [CurrentIns Addr 1],

do this

Code:

CASE   WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.Address1
WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.address1
ELSE IsNull(pins1.address1,'') END AS [CurrentIns Addr 1],

and what is with all this nonsense, NULL IS NOT NULL??,NULL IS NULL???

Code:

AND --- Filter on Facility
    (
    (NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
    (NULL IS NULL)

what are you trying to say here? this perhaps?

Code:

AND pv.FacilityId IS NULL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

jeffsturgeon2002 · Feb 16, 2007

Denis,

This is what "AND --- Filter on Facility" looks like in the application

Code:

AND --- Filter on Facility
	(
	(?FACILITY.ITEMDATA? IS NOT NULL AND pp.FacilityId IN (?FACILITY.ITEMDATA.U?)) OR
	(?FACILITY.ITEMDATA? IS NULL)
	)

The end user populates a name in a listbox and voila it gives you whatcha need.

It looks crazy I know and I have more SQL pros telling me whats up with the NULL is NULL not NULL than I can count. Its how GE developed there crazy application ... Its something that works though.

Jeff

SELECT * FROM users WHERE clue > 0

SQLDenis · Feb 16, 2007

so what happens if someone types in a value like this

;delete PatientVisit or 1=1; DROP TABLE PatientVisit

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

onpnt · Feb 16, 2007

Its how GE developed there crazy application ... Its something that works though.

I think my sig says it all

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

jeffsturgeon2002 · Feb 16, 2007

Most of the endusers arent SQL writers ... putting that in would not hopefully never occur. I didnt write the product .. just have to work with it.

Jeff

SELECT * FROM users WHERE clue > 0

AlexCuse · Feb 16, 2007

Jeff -

If I were you, I would look to see if your application can use Stored Procedures. Then your app would run something like this:

EXEC dbo.MySP ?PARAM1, ?PARAM2, ?PARAM3

and inside your stored proc you can remove very dangerous things like semicolons and 'DROP TABLE' commands from supplied parameters. Granted, you should not need to worry about this happening, but what if someone gets fired and they want to give the company one last F(orget)-U before they go home? All they'd need to do is write

1=1; DROP DATABASE jeffsDB

in one of the report parameters, and you are in for a world of hurt.

If you google 'SQL Injection Attacks' you will find some good information on this.

There are also several other advantages to using stored procedures, but I can't get into them now.

If this is an option for you, I would strongly suggest you pursue it.

Good LUck,

Alex

Ignorance of certain subjects is a great part of wisdom

jeffsturgeon2002 · Feb 16, 2007

If Im creating my Temp Table can I use:

Code:

CREATE TABLE #CURRENT
	(
	PatientName VARCHAR(100),
	PatientID VARCHAR(15),
	Ticket Number VARCHAR(20),
	DateOfService DATETIME,
	DoctorName VARCHAR(100),
	FacilityName VARCHAR(100),
	Financial Class VARCHAR(100),
	CompanyName VARCHAR(100),
	Visit Ins Balance MONEY,
	Visit Pat Balance MONEY,
	Total Visit Balance MONEY,
	Patient Address VARCHAR(50),
	Patient CSZ VARCHAR(40),
        ..... etc, etc ..............

with

Code:

SELECT 
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [PatientName], 
	  pp.patientid AS [PatientID],
	  pv.TicketNumber AS [Ticket Number], 
	  pv.Visit AS [DateOfService], 
	  df.ListName AS [DoctorName], 
	  df1.ListName AS [FacilityName], 
	  ml.Description AS [Financial Class],
	  df2.ListName AS [CompanyName], 
	  pva.insbalance AS [Visit Ins Balance],
	  pva.patbalance AS [Visit Pat Balance],
	  pva.insbalance + pva.patbalance AS [Total Visit Balance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ], 
................etc, etc, etc ......

Can I create the Patient CSZ as a Temp field and add up the Varchars in each with the formula used in my select - or should I break them out indivually in my temp table?

Jeff

SELECT * FROM users WHERE clue > 0

AlexCuse · Feb 16, 2007

Yes, you will be able to use that in your insert statement. Just be sure result will fit in varchar(40) (If you are using 2 letter state abbreviations I am sure it will - max length for City is usually around 30).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Temp Table help 1

jeffsturgeon2002

Programmer

gmmastros

Programmer

jeffsturgeon2002

Programmer

monksnake

Programmer

monksnake

Programmer

jeffsturgeon2002

Programmer

gmmastros

Programmer

chrissie1

Programmer

SQLDenis

Programmer

jeffsturgeon2002

Programmer

SQLDenis

Programmer

onpnt

Programmer

jeffsturgeon2002

Programmer

AlexCuse

Programmer

jeffsturgeon2002

Programmer

AlexCuse

Programmer

Similar threads

Part and Inventory Search

Sponsor