I would really appreciate some help in getting the date part getting extracted out of a string and then use that date to only filter records for last month.

I have a column named ‘source.name’ which has values that look like this:

What I want is to filter out rows only based on Last months data.

I tried to extract the date and then filter it out in the query below. However, this returns no data.

WITH DeviceCounts AS (
    SELECT 
        Organization,
        Device_Name,
        COUNT(*) AS DeviceCount,
        [Source.Name]
    FROM 
        [Feed].[SL1_Usage_Totals]
    WHERE 
        [License_Type] = 'Ratio' 
        AND TRY_CONVERT(DATE, LEFT([Source.Name], CHARINDEX(' ', [Source.Name]) - 1) + ' 1, ' + RIGHT(LEFT([Source.Name], LEN([Source.Name]) - LEN('.xlsx')), 4)) IS NOT NULL -- Check if conversion to date is successful
        AND TRY_CONVERT(DATE, LEFT([Source.Name], CHARINDEX(' ', [Source.Name]) - 1) + ' 1, ' + RIGHT(LEFT([Source.Name], LEN([Source.Name]) - LEN('.xlsx')), 4)) = DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) -- Filter by the month of the previous month
        
    GROUP BY 
        Organization,
        [Source.Name],
        Device_Name
)

SELECT 
    CASE 
        WHEN CHARINDEX(' - ', t1.Organization) > 0 
        THEN LEFT(t1.Organization, CHARINDEX(' - ', t1.Organization) - 1)
        ELSE t1.Organization
    END AS OrganizationGroupName,
    CASE 
        WHEN CHARINDEX(' - ', t1.Organization) > 0 
        THEN LTRIM(RIGHT(t1.Organization, LEN(t1.Organization) - CHARINDEX(' - ', t1.Organization) - 2))
        ELSE t1.Organization
    END AS OrganizationName,
    t1.[Source.Name],
    SUM(TRY_CAST(t2.[License_Count] AS INT)) AS TotalStorage,

 SUM(
        CASE 
            WHEN t1.[License_Type] = 'Application' THEN 1
            ELSE 0
        END
    ) AS TotalApplication,
    SUM(
        CASE 
            WHEN t1.[License_Type] = 'Network' THEN 1
            ELSE 0
        END
    ) AS TotalNetwork,

    SUM(
        CASE 
            WHEN t1.[License_Type] = 'Ratio' THEN dc.DeviceCount / 10.0
            ELSE 0
        END
    ) AS RawTotalRatio,
    SUM(
        CASE 
            WHEN t1.[License_Type] = 'Infrastructure' THEN 1
            ELSE 0
        END
    ) AS TotalInfrastructure,
    CASE 
        WHEN SUM(
            CASE 
                WHEN t1.[License_Type] = 'Ratio' THEN dc.DeviceCount / 10.0
                ELSE 0
            END
        ) = 0 THEN 0
        ELSE CEILING(SUM(
            CASE 
                WHEN t1.[License_Type] = 'Ratio' THEN dc.DeviceCount / 10.0
                ELSE 0
            END
        ))
    END AS TotalRatio,
    'ScienceLogic' AS PartitionKey,
    'ScienceLogic license' AS CIType,
    'License' AS ConfigurationItemTypeName

FROM 
    [Feed].[SL1_Usage_Totals] AS t1
LEFT JOIN 
    [Feed].[SL1_Storage_totals] t2 ON t1.[Device_Name] = t2.Device_ID AND t1.[Source.Name] = t2.[Source.Name]
LEFT JOIN 
    DeviceCounts AS dc ON t1.Device_Name = dc.Device_Name AND dc.[Source.Name] = t1.[Source.Name]
WHERE 
    TRY_CONVERT(DATE, LEFT(t1.[Source.Name], CHARINDEX(' ', t1.[Source.Name]) - 1) + ' 1, ' + RIGHT(LEFT(t1.[Source.Name], LEN(t1.[Source.Name]) - LEN('.xlsx')), 4)) IS NOT NULL -- Check if conversion to date is successful
    AND TRY_CONVERT(DATE, LEFT(t1.[Source.Name], CHARINDEX(' ', t1.[Source.Name]) - 1) + ' 1, ' + RIGHT(LEFT(t1.[Source.Name], LEN(t1.[Source.Name]) - LEN('.xlsx')), 4)) = DATEADD(MONTH, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) -- Filter by the month of the previous month
    
GROUP BY 
    CASE 
        WHEN CHARINDEX(' - ', t1.Organization) > 0 
        THEN LEFT(t1.Organization, CHARINDEX(' - ', t1.Organization) - 1)
        ELSE t1.Organization
    END,
    CASE 
        WHEN CHARINDEX(' - ', t1.Organization) > 0 
        THEN LTRIM(RIGHT(t1.Organization, LEN(t1.Organization) - CHARINDEX(' - ', t1.Organization) - 2))
        ELSE t1.Organization
    END,
    t1.[Source.Name];