Duplicates (3-4) in report please helppppp!

[AlexMZetec]

Hello!

I tried to merge 2 reports that works well but now I get 3-4 duplicates for each entries. The report return 1000 lines instead of 400.
Someone can see my problem???
HELP!!!!!!!!



Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
Case
When tblDiskPartition.Type = 'Installable File System' Then 'Legacy'
Else 'UEFI'
End As BiosType,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Username
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskPartition On tblAssets.AssetID = tblDiskPartition.AssetID
Where tblEncryptableVolume.DriveLetter = 'C:'
Order By tblAssets.AssetName

[KrisNelson · Accepted solution]

This entry is your problem.

Case
When tblDiskPartition.Type = 'Installable File System' Then 'Legacy'
Else 'UEFI'
End As BiosType

It's checking each of the partitions for each asset. And since there are multiple partitions per drive your returning multiple entries.

I would try this and see if it's to your liking; and admittedly there's probably a better way to code this. But, it's only going to check the bootable partitions for the UEFI/Legacy type.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID As AssetID1,
  tblAssets.AssetName,
  Case
    When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type =
      'Installable File System' Then 'Legacy'
    When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type <>
      'Installable File System' Then 'UEFI'
  End As BiosType,
  Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
    Else 'UNKNOWN'
  End As ProtectionStatus,
  tblEncryptableVolume.DriveLetter,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Username
From tblEncryptableVolume
  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblDiskPartition On tblAssets.AssetID = tblDiskPartition.AssetID
Where Case
    When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type =
      'Installable File System' Then 'Legacy'
    When tblDiskPartition.Bootable = 'True' And tblDiskPartition.Type <>
      'Installable File System' Then 'UEFI'
  End Is Not Null And tblEncryptableVolume.DriveLetter = 'C:'
Order By tblAssets.AssetName

-Kris

[AlexMZetec]

Hello Kris,

A strong thank you to you. You saved my week. It works!

Yes my goal was to get only the bootable partition.

Many thanks!
Alex