→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jakehc
Engaged Sweeper
I'm trying to create a report that just shows Total numbers for all our Desktops for a specific Manufacturer or Model. If I can figure one out the other should be a piece of cake.

Select Top 1000000 tblAssetCustom.Manufacturer,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename Like '%Windows%' And tblAssetCustom.Model <>
'%Latitude%'
Group By tblAssetCustom.Manufacturer
Order By Total Desc


I have this but it is not filtering out the Latitude laptops. They will still show in our Total count. This also happens with the Lenovos as well if I try to wildcard with %ThinkPad%. I'm sure it's just a simple order of operations or value. SQL is not my forte and I'm trying to learn on the fly.

Any suggestions / help is appreciated.
2 REPLIES 2
RCorbeil
Honored Sweeper II
If you want to use wildcards, you need to test for "like"-ness, not equality.

Change <> '%Latitude%' to NOT LIKE '%Latitude%' (i.e. the text string doesn't contain "Latitude").

Model = 'Latitude' (Model is "Latitude")
Model <> 'Latitude' (Model is not "Latitude")

Model LIKE 'Latitude%' (Model starts with "Latitude")
Model LIKE '%Latitude' (Model ends with "Latitude")
Model LIKE '%Latitude%' (Model contains "Latitude")
Model LIKE 'Lat%tude' (Model starts with "Lat" and ends with "tude")

Model NOT LIKE 'Latitude%' (Model doesn't start with "Latitude")
Model NOT LIKE '%Latitude' (Model doesn't end with "Latitude")
Model NOT LIKE '%Latitude%' (Model doesn't contain "Latitude")
Model NOT LIKE 'Lat%tude' (Model doesn't start with "Lat" and end with "tude")
RC62N wrote:
If you want to use wildcards, you need to test for "like"-ness, not equality.

Change <> '%Latitude%' to NOT LIKE '%Latitude%' (i.e. the text string doesn't contain "Latitude").

Model = 'Latitude' (Model is "Latitude")
Model <> 'Latitude' (Model is not "Latitude")

Model LIKE 'Latitude%' (Model starts with "Latitude")
Model LIKE '%Latitude' (Model ends with "Latitude")
Model LIKE '%Latitude%' (Model contains "Latitude")
Model LIKE 'Lat%tude' (Model starts with "Lat" and ends with "tude")

Model NOT LIKE 'Latitude%' (Model doesn't start with "Latitude")
Model NOT LIKE '%Latitude' (Model doesn't end with "Latitude")
Model NOT LIKE '%Latitude%' (Model doesn't contain "Latitude")
Model NOT LIKE 'Lat%tude' (Model doesn't start with "Lat" and end with "tude")


Thank you very much! I knew it was something I was just not thinking through. I was using LIKE statement in another query for LIKE %ipad% so I don't know why it didn't register for me.

Works like a charm now!