LearnItFirst User Forum

SET NOCOUNT ON

DECLARE @DOBFrom datetime, @DOBTo datetime

IF 1 = 1
   BEGIN
    SET @DOBFrom = getdate() - (100 * 365.25)
    IF 1 = 1
          BEGIN    
        SET @DOBTo = getdate() - ((1-1) * 365.25)
          END
    ELSE
          BEGIN
        SET @DOBTo = getdate() - (1 * 365.25)
          END
   END
ELSE
   BEGIN
                IF NULL = NULL
                        BEGIN
        SET @DOBFrom = getdate() - (365.25*110)
        SET @DOBTo = getdate()
            END
    ELSE
            BEGIN
        SET @DOBFrom = NULL
        SET @DOBTo = NULL
            END
   END

DECLARE @maxamt money,
          @minamt money

if 1 = '1'
begin
        set @minamt = -99999999.00
        set @maxamt = 999999999.00
end
if 1 = '2'
begin
        set @minamt = -9999999.00
        set @maxamt = NULL
end
if 1 = '3'
begin
        set @minamt = NULL
        set @maxamt = 99999999.00
end
if 1 = '4'
begin
        set @minamt = NULL
        set @maxamt = NULL
end

DECLARE @insmaxamt money,
          @insminamt money

if 1 = '1'
begin
        set @insminamt = -99999999.00
        set @insmaxamt = 999999999.00
end
if 1 = '2'
begin
        set @insminamt = -9999999.00
        set @insmaxamt = NULL
end
if 1 = '3'
begin
        set @insminamt = NULL
        set @insmaxamt = 99999999.00
end
if 1 = '4'
begin
        set @insminamt = NULL
        set @insmaxamt = NULL
end

CREATE TABLE #Bill
    (
    patientvisitid int
    )
INSERT #Bill

SELECT  distinct PatientVisit.patientvisitid
FROM    PatientVisit
    INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId
    LEFT JOIN InsuranceCarriers ON PatientVisit.PrimaryInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId
    LEFT JOIN InsuranceGroup on InsuranceCarriers.InsuranceGroupID = InsuranceGroup.InsuranceGroupID
    LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId
    INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
    INNER JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
    INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId
    LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId
    LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId
    INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId
    INNER JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId
    LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId
    INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId
    LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId
    LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId
    LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId
    LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId
    INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder
    INNER JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
    LEFT OUTER JOIN Medlists vo ON PatientVisit.VisitOwnerMid = vo.MedlistsId
    
WHERE     (MedLists_1.TableName = 'BillStatus')
    AND  --Filter on CPT Code
    (
    (NULL IS NOT NULL AND PatientVisitProcs.ProceduresId IN (NULL)) OR
    (NULL IS NULL)
    )
    AND  --Filter on doctor
    (
    (NULL IS NOT NULL AND PatientVisit.DoctorID IN (NULL)) OR
    (NULL IS NULL)
    )
    AND  --Filter on Diagnosis
    (
    (NULL IS NOT NULL AND PatientVisitDiags_0.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_1.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_2.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_3.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_4.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_5.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_6.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_7.DiagnosisId IN (NULL)) OR
    (NULL IS NOT NULL AND PatientVisitDiags_8.DiagnosisId IN (NULL)) OR
    (NULL IS NULL)
    )
    AND  --Filter on facility
    (
    (NULL IS NOT NULL AND PatientVisit.FacilityID IN (NULL)) OR
    (NULL IS NULL)
    )
    AND --Filter on pat sex
    (
    (Null IS NOT NULL AND PatientProfile.sex in (Null)) OR
    (Null IS NULL)
    )
    AND  --Filter on Date
    (
    (1 = 1 AND (PatientVisit.Entered >= ISNULL(NULL, '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))                 OR
    (1 = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom <                                                                    DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))
    )
    AND  --Filter on DOBDate
    (
    PatientProfile.Birthdate >= @DOBFrom  AND PatientProfile.Birthdate <= @DOBTo
    )
    AND  --Filter on Insurance Carrier
    (
    (NULL IS NOT NULL AND PatientVisit.PrimaryInsuranceCarriersId IN (NULL)) OR
    (NULL IS NULL)
    )
    AND --Filter on Ins Group
    (
    (NULL IS NOT NULL AND InsuranceCarriers.InsuranceGroupID IN (NULL)) OR
    (NULL IS NULL)
    )
    AND  --Filter on patient balance
    (
    (1 = 1) OR
    (1 = 2 AND PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.PatBalance <= @maxamt) OR
    (1 = 3 AND PatientVisitAgg.PatBalance >= @minamt) OR
    (1 = 4 AND PatientVisitAgg.PatBalance >= @minamt AND PatientVisitAgg.PatBalance <= @maxamt)
    )
    AND  --Filter on Insurance balance
    (
    (1 = 1) OR
    (1 = 2 AND PatientVisitAgg.InsBalance <> 0 AND PatientVisitAgg.InsBalance <= @insmaxamt) OR
    (1 = 3 AND PatientVisitAgg.InsBalance >= @insminamt) OR
    (1 = 4 AND PatientVisitAgg.InsBalance >= @insminamt AND PatientVisitAgg.InsBalance <= @insmaxamt)
    )
    AND --Filter on patient
    (
    (NULL IS NOT NULL AND  PatientProfile.PatientProfileID IN (NULL)) OR
    (NULL IS NULL)
    )
    AND -- Filter on Visit Owner
    (
    (NULL IS NOT NULL AND PatientVisit.VisitOwnerMID IN (NULL)) OR
    (NULL IS NULL)
    )

SELECT
    PatientVisit.TicketNumber AS [Ticket Number],
    PatientVisit.LastFiledDate,
            PatientVisit.FirstFiledDate,
    CASE WHEN PatientVisit.LastFiledDate IS NULL THEN 0
                ELSE DATEDIFF(d , PatientVisit.LastFiledDate , GETDATE())
            END as DaysSinceFiled ,
    ISNULL(vo.Description,'No Visit Owner Assigned') AS [Visit Owner] ,
           PatientVisitProcs.DateOfServiceFrom AS [Date Of Service],
    DoctorFacility.ListName AS Doctor,
    ISNULL(InsuranceCarriers.ListName, '') AS [Insurance Carrier],
    ISNULL (InsuranceGroup.name,'No Ins Group') as InsGroupName,
    ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')AS [Ins Address],
    --MedLists.Description AS [Financial Class],
    --ISNULL(InsuranceGroup.Name, '') AS [Insurance Group],
    dbo.FormatName(PatientProfile.Prefix, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last, PatientProfile.Suffix) AS [Patient Name],
    patientprofile.PatientId,
    patientprofile.PatientProfileID,
    ISNULL(patientprofile.medicalrecordnumber,'No MRN#')as [Medical Record Number],
    ISNULL(patientprofile.address1,'') + '' + ISNULL(patientprofile.address2, '') + ' ' + ISNULL(patientprofile.city,'') + ' ' + ISNULL(patientprofile.state,'') + ' ' + ISNULL(patientprofile.zip,'') AS [Patient Address],
    ISNULL(patientprofile.Phone1,'No Phone')as PatPhone,
    ISNULL(Convert(VarChar(20), patientprofile.birthdate, 101),'No DOB')as PatientDOB,
    datediff(YYYY, isnull(patientprofile.birthdate, getdate()), getdate())AS [Patient Age],
    'Age @ TOS' = CASE
         WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom) <= 0
            THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Month(s)'
            ELSE CAST(DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Yr(s)'
           END,
    ISNULL(patientprofile.sex,'')as PatSex,
    PatientVisit.Entered AS [Date Of Entry],
    DoctorFacility_1.ListName AS Facility,
         MedLists_1.Description AS [Visit Status],
    PatientVisitProcs.TotalFee AS Fee,
    PatientVisitProcs.CPTCode AS [CPT Code],
    PatientVisitProcs.Units AS Units,
    MedLists_2.Code AS PlaceOfService,
    MedLists_3.Code AS TypeOfService,
    ISNULL(CONVERT(varchar(4), MedLists_4.Code), ' ')
    + ' ' + ISNULL(CONVERT(varchar(4), MedLists_5.Code), ' ')
    + ' ' + ISNULL(CONVERT(varchar(4), MedLists_6.Code), ' ')
    + ' ' + ISNULL(CONVERT(varchar(4), MedLists_7.Code), ' ') AS Modifier,
    PatientVisitProcsAgg.InsPayment,
    PatientVisitProcsAgg.PatPayment,
    PatientVisitProcsAgg.InsAdjustment,
    PatientVisitProcsAgg.PatAdjustment,
    PatientVisitProcsAgg.InsBalance,
    PatientVisitProcsAgg.PatBalance,
    PatientVisitProcsAgg.InsBalance + PatientVisitProcsAgg.PatBalance AS TotalBalance,
    MedLists_8.Description AS Department,
    DoctorFacility_2.ListName AS Company,
    ISNULL(PatientVisitDiags_0.Code,'') AS Diag1,
    ISNULL(PatientVisitDiags_1.Code,'') AS Diag2,
    ISNULL(PatientVisitDiags_2.Code,'') AS Diag3,
    ISNULL(PatientVisitDiags_3.Code,'') AS Diag4,
    ISNULL(PatientVisitDiags_4.Code,'') AS Diag5,
    ISNULL(PatientVisitDiags_5.Code,'') AS Diag6,
    ISNULL(PatientVisitDiags_6.Code,'') AS Diag7,
    ISNULL(PatientVisitDiags_7.Code,'') AS Diag8,
    CASE
    WHEN 0 = 1 THEN DoctorFacility.ListName
    WHEN 0 = 2 THEN DoctorFacility_1.ListName
    WHEN 0 = 3 THEN DoctorFacility_2.ListName
    WHEN 0 = 4 THEN ISNULL(InsuranceCarriers.ListName,'No Carrier') + ' ' + ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')
    ELSE NULL
    END AS Grouping

FROM    PatientVisit
    INNER JOIN #Bill b ON PatientVisit.PatientVisitId = b.PatientVisitId
    INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId
    LEFT JOIN InsuranceCarriers ON PatientVisit.PrimaryInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId
    LEFT JOIN InsuranceGroup on InsuranceCarriers.InsuranceGroupID = InsuranceGroup.InsuranceGroupID
    LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId
    INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
    INNER JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
    INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId
    LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId
    LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId
    INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId
    INNER JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId
    LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId
    INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId
    LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId
    LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId
    LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId
    LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId
    INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder
    LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder
    INNER JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
    LEFT OUTER JOIN Medlists vo ON PatientVisit.VisitOwnerMid = vo.MedlistsId
WHERE     (MedLists_1.TableName = 'BillStatus')
ORDER BY [Patient Name]ASC
DROP TABLE #Bill

PatientCorrespondence.Created AS [Date Created],
PatientCorrespondence.CreatedBy AS [Created By],
CASE PatientCorrespondence.Description WHEN '**long**'
THEN cast(PatientCorrespondence.DescriptionLong as varchar(8000))
ELSE PatientCorrespondence.Description END AS Notes

SELECT DISTINCT
PatientCorrespondence.Created AS [Date Created] ,
PatientCorrespondence.CreatedBy AS [Created By] ,
CASE PatientCorrespondence.Description WHEN '**long**' THEN CAST(PatientCorrespondence.DescriptionLong AS VARCHAR(8000)) ELSE PatientCorrespondence.Description END AS Notes
FROM PatientVisit
LEFT JOIN PatientCorrespondence ON PatientVisit.PatientVisitId = PatientCorrespondence.PatientVisitId
WHERE PatientVisit.PatientVisitId = '846'
ORDER BY [Date Created] DESC