SQL counting based on date comparison

Question

I've got the following SQL query which gives me all of my assets which are older than two years, broken down by organization. Then I have to run it again and just remove the date comparison part in the WHERE clause to find out my total number of assets, so I can give a count of old, a count of total, and then a % old.

Is there a way to do this all as a single query? I'm thinking some type of case statement in the query maybe?

SELECT o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, COUNT(DISTINCT a.LabAssetSerialNbr) TotalAssets
FROM vw_DimLabAsset a
INNER JOIN vw_DimWorker w ON w.WorkerKey = a.LabAssetAssignedToWorkerKey
INNER JOIN vw_DimOrganizationHierarchy o ON o.OrganizationHierarchyUnitCd = w.WorkerOrganizationUnitCd
    AND o.OrganizationHierarchyUnitLevelFourNm IS NOT NULL
WHERE a.SystemCreatedOnDtm < DATEADD(day, DATEDIFF(day, 0, DATEADD(yy, -2, GETDATE())), 0)
    AND a.LabAssetTypeNm IN ('u_cmdb_ci_prototype_system', 'u_cmdb_ci_silicon')
    AND a.LabAssetHardwareStatus <> 'retired'
    AND (a.LabAssetHardwareSubStatus IS NULL OR a.LabAssetHardwareSubStatus <> 'archive')
GROUP BY o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm
ORDER BY 1, 2

I tried adding this to the select: `

SUM(CASE WHEN a.SystemCreatedOnDtm < DATEADD(day, DATEDIFF(day, 0, DATEADD(yy, -2, GETDATE())), 0) THEN 1 ELSE 0 END)

but that doesn't return the same count as the TotalAssets value.

Update

Here's the final query I ended up with:

DECLARE @date DateTime
SELECT @date = DATEADD(day, DATEDIFF(day, 0, DATEADD(yy, -2, GETDATE())), 0);

WITH pphw AS 
(
    SELECT DISTINCT o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr, MIN(a.SystemCreatedOnDtm) MinCreated
    FROM vw_DimLabAsset a
    INNER JOIN vw_DimWorker w ON a.LabAssetAssignedToWorkerKey = w.WorkerKey
    INNER JOIN vw_DimOrganizationHierarchy o ON w.WorkerOrganizationUnitCd = o.OrganizationHierarchyUnitCd
        AND o.OrganizationHierarchyUnitLevelThreeNm IS NOT NULL
        AND o.OrganizationHierarchyUnitLevelFourNm IS NOT NULL
    WHERE LabAssetHardwareStatus <> 'Retired'
        AND (LabAssetHardwareSubStatus IS NULL OR LabAssetHardwareSubStatus <> 'Archive')
        AND a.LabAssetTypeNm IN ('u_cmdb_ci_prototype_system', 'u_cmdb_ci_silicon')
    GROUP BY o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr
)
SELECT OrganizationHierarchyUnitLevelThreeNm, OrganizationHierarchyUnitLevelFourNm, 
    SUM(CASE WHEN MinCreated < @date THEN 1 ELSE 0 END) AssetsOverTwoYears,
    COUNT(DISTINCT LabAssetSerialNbr) TotalAssets
FROM pphw
GROUP BY OrganizationHierarchyUnitLevelThreeNm, OrganizationHierarchyUnitLevelFourNm
HAVING SUM(CASE WHEN MinCreated < @date THEN 1 ELSE 0 END) > 0
ORDER BY 1, 2

Answer 1

My answer is similar to tysonwright, but I think the GROUP BY clause should be outside of the sub-select. But, I would want sample data to validate this. The bottom line is that you should first gather all of the records that you want calculate metrics on via a sub-select. From there, you can perform your SUMs and COUNTs.

SELECT TMP1.OrganizationHierarchyUnitLevelThreeNm
    ,TMP1.OrganizationHierarchyUnitLevelFourNm
    ,TotalAssets = COUNT(TMP1.LabAssetSerialNbr)
    ,AssetsOver2YearsOld = SUM(TMP1.AssetOver2YearsOldInd)
    ,PercAssetsOver2YearsOld = SUM(TMP1.AssetOver2YearsOldInd) / COUNT(TMP1.LabAssetSerialNbr)
FROM (SELECT DISTINCT o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr
            ,AssetOver2YearsOldInd = CASE WHEN a.SystemCreatedOnDtm < DATEADD(d, DATEDIFF(d, 0, DATEADD(yy, -2, GETDATE())), 0) THEN 1 ELSE 0 END
        FROM vw_DimLabAsset a
        INNER JOIN vw_DimWorker w 
            ON w.WorkerKey = a.LabAssetAssignedToWorkerKey
        INNER JOIN vw_DimOrganizationHierarchy o 
            ON o.OrganizationHierarchyUnitCd = w.WorkerOrganizationUnitCd
            AND o.OrganizationHierarchyUnitLevelFourNm IS NOT NULL
        WHERE a.LabAssetTypeNm IN ('u_cmdb_ci_prototype_system', 'u_cmdb_ci_silicon')
            AND a.LabAssetHardwareStatus <> 'retired'
            AND (a.LabAssetHardwareSubStatus IS NULL 
                    OR a.LabAssetHardwareSubStatus <> 'archive')
        ) TMP1
GROUP BY TMP1.OrganizationHierarchyUnitLevelThreeNm, TMP1.OrganizationHierarchyUnitLevelFourNm
ORDER BY 1, 2

Update:

To remove the duplicates by date time, you can use either the MIN or MAX function, depending one what your requirement is:

SELECT TMP1.OrganizationHierarchyUnitLevelThreeNm
    ,TMP1.OrganizationHierarchyUnitLevelFourNm
    ,TotalAssets = COUNT(TMP1.LabAssetSerialNbr)
    ,AssetsOver2YearsOld = SUM(CASE WHEN TMP1.MaxSystemCreatedOnDtm < DATEADD(d, DATEDIFF(d, 0, DATEADD(yy, -2, GETDATE())), 0) THEN 1 ELSE 0 END)
    ,PercAssetsOver2YearsOld = SUM(CASE WHEN TMP1.MaxSystemCreatedOnDtm < DATEADD(d, DATEDIFF(d, 0, DATEADD(yy, -2, GETDATE())), 0) THEN 1 ELSE 0 END) / COUNT(TMP1.LabAssetSerialNbr)
FROM (SELECT DISTINCT o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr, MaxSystemCreatedOnDtm = MAX(a.SystemCreatedOnDtm)
        FROM vw_DimLabAsset a
        INNER JOIN vw_DimWorker w 
            ON w.WorkerKey = a.LabAssetAssignedToWorkerKey
        INNER JOIN vw_DimOrganizationHierarchy o 
            ON o.OrganizationHierarchyUnitCd = w.WorkerOrganizationUnitCd
            AND o.OrganizationHierarchyUnitLevelFourNm IS NOT NULL
        WHERE a.LabAssetTypeNm IN ('u_cmdb_ci_prototype_system', 'u_cmdb_ci_silicon')
            AND a.LabAssetHardwareStatus <> 'retired'
            AND (a.LabAssetHardwareSubStatus IS NULL 
                    OR a.LabAssetHardwareSubStatus <> 'archive')
        GROUP BY o.OrganizationHierarchyUnitLevelThreeNm, o.OrganizationHierarchyUnitLevelFourNm, a.LabAssetSerialNbr
        ) TMP1
GROUP BY TMP1.OrganizationHierarchyUnitLevelThreeNm, TMP1.OrganizationHierarchyUnitLevelFourNm
ORDER BY 1, 2

Answer 2

I think this will get you what you need:

SELECT 
    s.OrganizationHierarchyUnitLevelThreeNm, 
    s.OrganizationHierarchyUnitLevelFourNm, 
    COUNT(*) TotalAssets,
    SUM(CASE    WHEN s.SystemCreatedOnDtm < DATEADD(day, DATEDIFF(day, 0, DATEADD(yy, -2, GETDATE())), 0)
                THEN 1 ELSE 0 END) AssetsOver2YearsOld,
    SUM(CASE    WHEN s.SystemCreatedOnDtm < DATEADD(day, DATEDIFF(day, 0, DATEADD(yy, -2, GETDATE())), 0)
                THEN 1 ELSE 0 END) / COUNT(*) PercAssetsOver2YearsOld
FROM 
    (   
    SELECT
        o.OrganizationHierarchyUnitLevelThreeNm, 
        o.OrganizationHierarchyUnitLevelFourNm
        a.LabAssetSerialNbr,
        a.SystemCreatedOnDtm
    FROM    
        vw_DimLabAsset a
    INNER JOIN 
        vw_DimWorker w 
    ON 
        w.WorkerKey = a.LabAssetAssignedToWorkerKey
    INNER JOIN 
        vw_DimOrganizationHierarchy o 
    ON 
        o.OrganizationHierarchyUnitCd = w.WorkerOrganizationUnitCd
    AND 
        o.OrganizationHierarchyUnitLevelFourNm IS NOT NULL
    WHERE 
        a.LabAssetTypeNm IN ('u_cmdb_ci_prototype_system', 'u_cmdb_ci_silicon')
    AND 
        a.LabAssetHardwareStatus <> 'retired'
    AND 
        (a.LabAssetHardwareSubStatus IS NULL OR a.LabAssetHardwareSubStatus <> 'archive')
    GROUP BY 
        o.OrganizationHierarchyUnitLevelThreeNm, 
        o.OrganizationHierarchyUnitLevelFourNm
        a.LabAssetSerialNbr,
        a.SystemCreatedOnDtm
    ) AS s