September 4, 2003 at 1:59 pm
I’m looking to find out how many computers have “Out of Date” antivirus DATs and how many have “In Date” DATs. I can easily query one or the other, but I don’t know how I can place both in one query. As an example, here’s a query to show me “Out of Date”.
Is there any way I can place a conditional state in the SELECT clause that would allow me to COUNT when DATVer < '4.0.4290' in one column (OutOfDate), but also COUNT when DATVer >= '4.0.4290' in another column (InDate)?
Does it make sense what I’m trying to say? Thanks for your help!
SELECT
BranchNode.NodeName as Site,
COUNT(LeafNode.NodeName) as OutOfDate
FROM ComputerProperties, LeafNode, BranchNode, ProductProperties, AVIView_Software
WHERE
LastUpdate > '20030825000000'
AND DATVer < '4.0.4290'
AND ComputerProperties.ParentID = LeafNode.AutoID
AND LeafNode.ParentID = BranchNode.AutoID
AND LeafNode.AutoID = ProductProperties.ParentID
AND ProductProperties.ProductCode = AVIView_Software.ProductCode
AND AVIView_Software.ProductFamily IN ('TVD', 'TVD_NC')
GROUP BY BranchNode.NodeName
ORDER BY BranchNode.NodeName
September 4, 2003 at 4:16 pm
If I understand correctly I think you are after this.
SELECT
BranchNode.NodeName as Site,
SUM(CASE WHEN DATVer < '4.0.4290' THEN 1 ELSE 0 END) as OutOfDate,
SUM(CASE WHEN DATVer >= '4.0.4290' THEN 1 ELSE 0 END) as InDate,
FROM ComputerProperties, LeafNode, BranchNode, ProductProperties, AVIView_Software
WHERE
LastUpdate > '20030825000000'
AND ComputerProperties.ParentID = LeafNode.AutoID
AND LeafNode.ParentID = BranchNode.AutoID
AND LeafNode.AutoID = ProductProperties.ParentID
AND ProductProperties.ProductCode = AVIView_Software.ProductCode
AND AVIView_Software.ProductFamily IN ('TVD', 'TVD_NC')
GROUP BY BranchNode.NodeName
ORDER BY BranchNode.NodeName
September 4, 2003 at 7:09 pm
Thank you much. I'll give it a shot!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply