SET NOCOUNT ON

CREATE TABLE #PatientTemp
        (
          [PatientProfileId] [int] NOT NULL,
          [Prefix] [varchar](10) NULL,
          [First] [varchar](30) NULL,
          [Middle] [varchar](30) NULL,
          [Last] [varchar](30) NULL,
          [Suffix] [varchar](20) NULL,
          [Created] [datetime] NOT NULL,
          [SSN] [varchar](9) NULL,
          [Birthdate] [datetime] NULL,
          [Address1] [varchar](50) NULL,
          [Address2] [varchar](50) NULL,
          [City] [varchar](30) NULL,
          [State] [varchar](3) NULL,
          [Zip] [varchar](10) NULL,
          [Phone1] [varchar](15) NULL,
          [Sex] [VARCHAR](10) NULL
        ) 

INSERT
        #PatientTemp
        SELECT
                pp.PatientProfileId,
                pp.Prefix,
                pp.First,
                pp.Middle,
                pp.Last,
                pp.Suffix,
                CASE WHEN 2 = 1 THEN PP.Created
                     WHEN 2 = 2 THEN MIN(PV.Visit)
                     ELSE NULL
                END AS Created,
                pp.ssn,
                pp.birthdate,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                pp.Phone1,
                pp.Sex
        FROM
                PatientVisit pv
                INNER JOIN PatientProfile pp ON pp.PatientProfileId = pv.PatientProfileId
        GROUP BY
                pp.PatientProfileId,
                pp.Prefix,
                pp.First,
                pp.Middle,
                pp.Last,
                pp.Suffix,
                pp.Created,
                pp.ssn,
                pp.birthdate,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                pp.Phone1,
                pp.Sex
        HAVING
                ( (( 2 = 1 )
                  AND ( PP.Created >= ISNULL('01/01/2008' , '1/1/1900')
                        AND PP.Created < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000')) )
	) )
                OR ( ( 2 = 2 )
                     AND ( (MIN(PV.Visit) >= ISNULL('01/01/2008' , '1/1/1900')
                           AND ( MIN(PV.Visit) ) < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000'))) ) )
        ORDER BY
                pp.First,
                pp.Last

SELECT
        #PatientTemp.PatientProfileId,
        #PatientTemp.Last + ', ' + #PatientTemp.First AS [Member Name],
        #PatientTemp.Created AS Created,
        #PatientTemp.SSN,
        ISNULL(CONVERT(VARCHAR(20) , #PatientTemp.birthdate , 101) , '') AS [Birthdate],
        #PatientTemp.Address1,
        #PatientTemp.Address2,
        #PatientTemp.City,
        #PatientTemp.State,
        #PatientTemp.Zip,
        #PatientTemp.Phone1,
        #PatientTemp.Middle,
        CASE WHEN #PatientTemp.Sex = 'M' THEN '1'
             WHEN #PatientTemp.Sex = 'F' THEN '2'
             ELSE ISNULL(#PatientTemp.Sex , '')
        END AS [Sex],
        MAX(ic.Listname) AS Listname,
        MAX(ISNULL(pi.InsuredId , '')) AS [InsuredID]

--	    CASE 
--			WHEN pi.InsCardEffectiveDate IS NULL THEN ''
--          ELSE CONVERT(VARCHAR , pi.InsCardEffectiveDate , 101)
--       END AS [PrimaryInsEffDate],
--		DATEADD(year , 1 , pi.InsCardEffectiveDate) AS [PrimaryInsTermDate]

FROM
        PatientVisit pv
        INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
        INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
        LEFT JOIN dbo.DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId
        LEFT JOIN dbo.DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
        LEFT JOIN PatientVisitResource pvr ON pvr.PatientVisitId = pv.PatientVisitId
        LEFT JOIN PatientInsurance pi ON pv.PrimaryPICarrierId = pi.PatientInsuranceId
        LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
WHERE
        ( ( NULL IS NOT NULL
            AND pv.DoctorId IN ( NULL ) )
          OR ( NULL IS NULL ) )
        AND ( ( '6' IS NOT NULL
                AND pv.FacilityId IN ( 6 ) )
              OR ( '6' IS NULL ) )
        AND  --Filter on insurance carrier
        ( ( NULL IS NOT NULL
            AND ic.InsuranceCarriersId IN ( NULL ) )
          OR ( NULL IS NULL ) )
        AND ( ( NULL IS NOT NULL
                AND pvr.ResourceId IN ( NULL ) )
              OR ( NULL IS NULL ) )
GROUP BY
        #PatientTemp.Created,
        dbo.FormatName(#PatientTemp.Prefix , #PatientTemp.First , #PatientTemp.Middle , #PatientTemp.Last , #PatientTemp.Suffix),
        #PatientTemp.PatientProfileId,
        #PatientTemp.Last,
        #PatientTemp.First,
        #PatientTemp.SSN,
        #PatientTemp.Birthdate,
        #PatientTemp.Address1,
        #PatientTemp.Address2,
        #PatientTemp.City,
        #PatientTemp.State,
        #PatientTemp.Zip,
        #PatientTemp.Phone1,
        #PatientTemp.Middle,
        #PatientTemp.Sex
        ---ic.ListName,
		---pi.InsuredId,
		---pi.InsCardEffectiveDate
HAVING
        ( (( 2 = 1 )
          AND ( #PatientTemp.Created >= ISNULL('01/01/2008' , '1/1/1900')
                AND #PatientTemp.Created < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000')) )
	) )
        OR ( ( 2 = 2 )
             AND ( (MIN(pv.Visit) >= ISNULL('01/01/2008' , '1/1/1900')
                   AND ( MIN(pv.Visit) ) < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000'))) ) )
ORDER BY
        #PatientTemp.Last + ', ' + #PatientTemp.First,
        #PatientTemp.PatientProfileId,
        #PatientTemp.Created,
        #PatientTemp.SSN,
        #PatientTemp.Birthdate,
        #PatientTemp.Address1,
        #PatientTemp.Address2,
        #PatientTemp.City,
        #PatientTemp.State,
        #PatientTemp.Zip,
        #PatientTemp.Phone1,
        #PatientTemp.Middle,
        #PatientTemp.Sex
        ---ic.ListName,
        ---pi.InsuredId,
        ---pi.InsCardEffectiveDate,
	---[PrimaryInsTermDate]

DROP TABLE #PatientTemp

quote:

---

Originally posted by JeffS23

This is a DATETIME field. I would say yes.

---