sandisql

USE [PFR]
GO

/****** Object:  View [dbo].[vwComplaintCognosDrillDownA]    Script Date: 8/6/2018 2:33:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[vwComplaintCognosDrillDownA]
AS
SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
--  when 'USGB' then
--      case(FAC) when 'ESCANABA' then
--          set DEPT = 'USES'
--      end
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
, LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Compliment'
,ComplaintType =(case(tblComplaints.CompStaff) when 1 then
    'Compliment Staff'
  end )
, Category =''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and CompStaff = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Compliment'
,ComplaintType =(
case(tblComplaints.CompProcess) when 1 then
    'Compliment Process'
  end ), Category=''
,SubCategory = ''

FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and CompProcess = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.RecWait) when 1 then
    'Wait Time - In Reception'
  end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and RecWait = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.ExamWait) when 1 then
    'Wait Time - In Exam'
  end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and ExamWait=1

Union

---------****************;
SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.PhoneWait) when 1 then
    'Wait Time - Phone On Hold'
  end as ComplaintType
,Category = isnull(tblddlnameadds.providerFullName,'Other')
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join dbo.tblStaffNames on (dbo.tblComplaints.ComplaintID = dbo.tblStaffNames.fkComplaintID and tblStaffNames.sectionID = 4)
Left Outer Join dbo.tblDDLnameAdds on (dbo.tblStaffNames.StaffName = ProviderFullName)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  PhoneWait=1 and tblStaffNames.sectionID = 4

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.Appt) when 1 then
    'Time to Next Available Appointment'
  end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  Appt=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.TestRequest) when 1 then
    'Time to Receipt of Test Results'
  end as ComplaintType
,Category = isnull(tblDDLnameAdds.providerFullName,'Other')
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join dbo.tblStaffNames on (dbo.tblComplaints.ComplaintID = dbo.tblStaffNames.fkComplaintID and tblStaffNames.sectionID = 7 )
Left Outer Join dbo.tblDDLnameAdds on (dbo.tblStaffNames.StaffName = ProviderFullName)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  TestRequest=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.Contact) when 1 then
    'Time to Contact for Requested Info'
  end as ComplaintType
,Category = isnull(tblDDLnameAdds.providerFullName,'Other')
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join dbo.tblStaffNames on (dbo.tblComplaints.ComplaintID = dbo.tblStaffNames.fkComplaintID and tblStaffNames.sectionID = 9 )
Left Outer Join dbo.tblDDLnameAdds on (dbo.tblStaffNames.StaffName = ProviderFullName)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and Contact=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,case(tblComplaints.Cost) when 1 then
    'Cost of Service'
end as ComplaintType
,Category = isnull(tblddlnameadds.providerFullName,'Other')
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join dbo.tblStaffNames on (dbo.tblComplaints.ComplaintID = dbo.tblStaffNames.fkComplaintID and tblStaffNames.sectionID = 6 )
Left Outer Join dbo.tblDDLnameAdds on (dbo.tblStaffNames.StaffName = ProviderFullName)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  Cost=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'DME'
,case(tblComplaints.CatDME) when 1 then
     'Quality of Optical/DME/Supplies'
end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  catDME=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Actual Billing Error'
,case(tblComplaints.BillCoding) when 1 then
     'Coding Error'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillCoding=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Actual Billing Error'
,case(tblComplaints.BillCharging) when 1 then
     'Charging Error'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillCharging=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Actual Billing Error'
,case(tblComplaints.BillPosting) when 1 then
     'Posting Error'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillPosting = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Statement'
,case(tblComplaints.BillStmtRecd) when 1 then
     'Received, Already Paid'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillStmtRecd = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Statement'
,case(tblComplaints.BillStmtDetail) when 1 then
     'Not Enough Detail'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillStmtDetail = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Statement'
,case(tblComplaints.BillStmtLayout) when 1 then
     'Layout/Process'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillStmtLayout=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Insurance Coverage'
,case(tblComplaints.BillPreCert) when 1 then
     'PreCertification'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillPreCert = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,Category =  'Insurance Coverage'
,case(tblComplaints.BillPTUnderstand) when 1 then
     'PT Lacks Understanding or Dissatisfied With Coverage'
end as SubCategory
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  billPTUnderstand = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType='Billing Issues'
,case(tblComplaints.BillErr) when 1 then
            'Error In Discount'
end as Catetory
,SubCategory = 'Contract'
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillErr=1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType = 'Billing Issues'
,Category=  'Other'
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where (validated = 1 and notify=1 and categoryValidated = 1 and billing=1)
and  Billcoding =0 and billcharging = 0 and billposting=0 and billsiteres=1 and billstmt = 0 and
billinscov = 0 and billprecert = 0 and billptunderstand=0 and billerr=0 and billothertype=0 and
other = 0

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Billing'
,ComplaintType = 'Billing Issues'
,case(tblComplaints.BillOtherType) when 1 then
            'Other'
end as Category
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  BillOtherType = 1

--Union
--
--SELECT
--dbo.tblComplaints.ComplaintID
--,DepartmentFullName = case
--  when (dbo.tblComplaints.Dept='USGB'
--      and (DOS >=   '2009-01-01')
--      and FAC='ESCANABA') then
--      'BayCare Clinic - Urological Surgeons (USES)'
--  else  (BCM.Department + ' (' + rtrim(Dept) + ')')
--end
--,DEPT = case
--   when (dbo.tblComplaints.Dept='USGB'
--      and (DOS >=   '2009-01-01')
--      and FAC='ESCANABA') then
--      'USES'
--   else dbo.tblComplaints.Dept
--end
--,isnull(dbo.tblComplaints.FAC,
--  case(Dept) when 'GBEC' then 'WMOB'
--  when 'OMGB' then 'OMGB'
--  else 'EPOB'
--end
--) as FAC
--,MgrGroup=case(tblComplaints.notify) when 1 then
--  dbo.tblResponsible.MgrGroup
--  else
--  MgrGroupLowComplexCount
--  end
--,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
--,LogDate
--,dbo.tblComplaints.LogDate as OrderDate
--,ComplaintStatus = case(tblComplaints.notify) when 1 then
--  dbo.tblPtInfo.ComplaintStatus
--  else
--  'Closed'
--end
--,DOS
--,dbo.tblComplaints.notify
--,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
--,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
--,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
--,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
--,Classification = 'Procedural/Process'
--,case(tblComplaints.Other) when 1 then
--   'Other'
--  end as ComplaintType
--,Category = ''
--,SubCategory = ''
--FROM         dbo.tblComplaints
--Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
--Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
--Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
-- Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
--and  tblComplaints.other = 1
--and  tblComplaints.catclinical = 0
--and  tblComplaints.catProf = 0
--and  tblComplaints.catprocedure = 0
--and  tblComplaints.catmissDir = 0


Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.CatProcEnv) when 1 then
     'Environmental'
  end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatProcEnv = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.CatProcWork) when 1 then
     'Current Work Process'
  end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatProcWork = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,case(tblComplaints.CatProcPerf) when 1 then
     'Performance'
  end as ComplaintType
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatProcPerf = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Procedural/Process'
,ComplaintType = 'Other'
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatProcOther > ''

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Misdirected Hospital'
,ComplaintType = ''
,Category=''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatMissdir = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Clinical'
,ComplaintType = case(tblComplaints.CatClinicalTreat) when 1 then
     'Treatment'
  end
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatClinicalTreat = 1

Union
SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Clinical'
,ComplaintType = case(tblComplaints.CatClinicalPain) when 1 then
     'Pain'
  end
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatClinicalPain = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Clinical'
,ComplaintType =case(tblComplaints.CatClinicalDiagnosis) when 1 then
     'Diagnosis'
  end
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatClinicalDiagnosis = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Clinical'
,ComplaintType = 'Other'
,Category = ''
,SubCategory = ''
FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and tblComplaints.CatClinicalOther > ''

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Communication Style/Professional Conduct'
,ComplaintType= 'Time Spent'
,Category= ''
,SubCategory = ''

FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatProfTime = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Communication Style/Professional Conduct'
,ComplaintType= 'Sensitivity/Empathy/Rude'
,Category= ''
,SubCategory = ''

FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  (tblComplaints.CatProfSensitive = 1 or  tblComplaints.CatProfRude = 1)

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Communication Style/Professional Conduct'
,ComplaintType= 'Too Difficult to Understand'
,Category= ''
,SubCategory = ''

FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  tblComplaints.CatProfComm = 1

Union

SELECT
dbo.tblComplaints.ComplaintID
,DepartmentFullName = case
    when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'BayCare Clinic - Urological Surgeons (USES)'
    else  (BCM.Department + ' (' + rtrim(Dept) + ')')
end
,DEPT = case
     when (dbo.tblComplaints.Dept='USGB'
        and (DOS >=   '2009-01-01')
        and FAC='ESCANABA') then
        'USES'
     else dbo.tblComplaints.Dept
end
,isnull(dbo.tblComplaints.FAC,
    case(Dept) when 'GBEC' then 'WMOB'
    when 'OMGB' then 'OMGB'
    else 'EPOB'
end
) as FAC
,MgrGroup=case(tblComplaints.notify) when 1 then
    dbo.tblResponsible.MgrGroup
    else
    MgrGroupLowComplexCount
  end
,isnull(dbo.tblResponsible.PrimaryContact,0) as Prime
,LogDate
,dbo.tblComplaints.LogDate as OrderDate
,ComplaintStatus = case(tblComplaints.notify) when 1 then
    dbo.tblPtInfo.ComplaintStatus
    else
    'Closed'
end
,DOS
,dbo.tblComplaints.notify
,isnull(dbo.tblPtInfo.PtSatisfied,0) as PtSatisfied
,isnull(dbo.tblPtInfo.PtAccount,'') as PtAccount
,isnull(dbo.tblPtInfo.PtLastName,'') as PtLastName
,isnull(dbo.tblPtInfo.PtFirstName,'') as PtFirstName
,Classification = 'Communication Style/Professional Conduct'
,ComplaintType= 'Other'
,Category= ''
,SubCategory = ''

FROM         dbo.tblComplaints
Left Outer Join dbo.tblPtInfo on dbo.tblComplaints.ComplaintID = dbo.tblPtinfo.fkComplaintID
Left Outer Join dbo.tblResponsible on (dbo.tblComplaints.ComplaintID = dbo.tblResponsible.fkComplaintID and dbo.tblResponsible.PrimaryContact=1)
Left Outer Join BayCareMaster..tblBCDepts BCM on dbo.tblComplaints.Dept = BCM.DepartmentNameShort
 Where ((validated = 1 and notify=0) or (notify=1 and categoryValidated = 1))
and  not(tblComplaints.CatProfOther) is null

--select* from vwComplaintCognosDrillDown where dept = 'BCOS'


GO