SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: This view calculates the status of Sysusers based on Safety System dates
*
*/

CREATE view [dbo].[SafetySystemEquipmentStatus]
(
    EquipmentFID,
    ColorCode
)
as

select
    EquipmentFID = Equip.PriKey,
    ColorCode =
        case
            when SafetySystemEquipment.AdministrativeHoldFlag = 1 then 'Black'
            when
            (
                SafetySystemEquipment.NextSanitizedDate < GetDate() or
                SafetySystemEquipment.NextInspectionDate < GetDate() or
                SafetySystemEquipment.NextServiceDate < GetDate() or
                SafetySystemEquipment.LicenseDueDate < GetDate() or
                SafetySystemEquipment.InterstateCommerceCommissionDueDate < GetDate() or
                SafetySystemEquipment.DotExpirationDate < GetDate()
            ) then 'Red'
            when
            (
                SafetySystemEquipment.NextSanitizedDate - 30 < GetDate() or
                SafetySystemEquipment.NextInspectionDate - 30 < GetDate() or
                SafetySystemEquipment.NextServiceDate - 30 < GetDate() or
                SafetySystemEquipment.LicenseDueDate - 30 < GetDate() or
                SafetySystemEquipment.InterstateCommerceCommissionDueDate - 30 < GetDate() or
                SafetySystemEquipment.DotExpirationDate - 30 < GetDate()
            ) then 'Yellow'
            else 'Green'
        end            
from Equip
inner join SafetySystemEquipment on Equip.PriKey = SafetySystemEquipment.EquipFID
GO
GRANT SELECT ON  [dbo].[SafetySystemEquipmentStatus] TO [MssExec]
GRANT INSERT ON  [dbo].[SafetySystemEquipmentStatus] TO [MssExec]
GRANT DELETE ON  [dbo].[SafetySystemEquipmentStatus] TO [MssExec]
GRANT UPDATE ON  [dbo].[SafetySystemEquipmentStatus] TO [MssExec]
GO