select vd.Number + ' - ' + vd.Name Vlan,
dv.Decal,
dv.BuildingNumber + ':' + dv.RoomNumber Location,
ds.Name DeviceStatus,
os.Name OperatingSystem,
dt.Name DeviceType,
dv.Manufacturer,
dv.ModelNumber,
dv.SerialNumber,
case when dv.IsNetworked = 1 then 'Yes' else 'No' END Networked,
case when dv.HasWaiver = 1 then 'Yes' else 'No' END HasWaiver,
case when dv.HasSensitiveData = 1 then 'Yes' else 'No' END HasSensitiveData,
case when dv.IsEncrypted = 1 then 'Yes' else 'No' END Encrypted,
dv.SwitchIdentifier,
dv.SwitchPort,
case when dv.IsIscApproved = 1 then 'Yes' else 'No' END IsIscApproved,
dv.Comments,
PrimaryUser.FirstName + ' ' + PrimaryUser.LastName PrimaryUser,
PropertyCustodian.FirstName + ' ' + PropertyCustodian.LastName PropertyCustodian,
PropertyOfficer.FirstName + ' ' + PropertyOfficer.LastName PropertyOfficer,
CustomSystemOwner.FirstName + ' ' + CustomSystemOwner.LastName CustomSystemOwner,
SystemOwner.FirstName + ' ' + SystemOwner.LastName SystemOwner
from DeviceVersion dv
inner join VlanDetailVersion vd on vd.VlanId = dv.vlanid
inner join DeviceStatus ds on ds.id = dv.DeviceStatusId
inner join Device d on d.CurrentDeviceVersionId = dv.Id
inner join DeviceOperatingSystem os on os.Id = dv.OperatingSystemId
inner join DeviceType dt on dt.Id = dv.DeviceTypeId
left join users PrimaryUser on PrimaryUser.Id = dv.PrimaryUserId
left join users PropertyCustodian on PropertyCustodian.Id = dv.PropertyCustodianUserId
left join users PropertyOfficer on PropertyOfficer.Id = dv.PropertyPropertyOfficerUserId
left join users CustomSystemOwner on CustomSystemOwner.Id = dv.CustomSystemOwnerUserId
left join users SystemOwner on SystemOwner.Id = dv.SystemOwnerUserId