select
"Catalog-Software"."Name"
, Vendor
, CASE
WHEN EndOfLifeDate LIKE '[1-9]\/[1-9]\/[12][9012][0-9][0-9]%' /* m/d/yyyy */
THEN CAST(CONVERT(DATE, EndOfLifeDate, 101) AS CHAR(10))
WHEN EndOfLifeDate LIKE '[1-9]\/[0-3][0-9]\/[12][9012][0-9][0-9]%' AND /* m/dd/yyyy */
CAST(SUBSTRING(EndOfLifeDate, 3, 2) AS INT) BETWEEN 1 AND 31
THEN CAST(CONVERT(DATE, EndOfLifeDate, 101) AS CHAR(10))
WHEN EndOfLifeDate LIKE '[01][0-9]\/[1-9]\/[12][9012][0-9][0-9]%' AND /* mm/d/yyyy */
CAST(SUBSTRING(EndOfLifeDate, 1, 2) AS INT) BETWEEN 1 AND 12
THEN CAST(CONVERT(DATE, EndOfLifeDate, 101) AS CHAR(10))
WHEN EndOfLifeDate LIKE '[01][0-9]\/[0-3][0-9]\/[12][9012][0-9][0-9]%' AND /* mm/dd/yyyy */
CAST(SUBSTRING(EndOfLifeDate, 1, 2) AS INT) BETWEEN 1 AND 12 AND
CAST(SUBSTRING(EndOfLifeDate, 4, 2) AS INT) BETWEEN 1 AND 31
THEN CAST(CONVERT(DATE, EndOfLifeDate, 101) AS CHAR(10))
WHEN EndOfLifeDate LIKE '[0-3][0-9]\/[01][0-9]\/[12][9012][0-9][0-9]%' AND /* dd/mm/yyyy */
CAST(SUBSTRING(EndOfLifeDate, 1, 2) AS INT) BETWEEN 1 AND 31 AND
CAST(SUBSTRING(EndOfLifeDate, 4, 2) AS INT) BETWEEN 1 AND 12
THEN CONVERT(DATE, EndOfLifeDate, 101)
WHEN EndOfLifeDate LIKE '[0-3][0-9]\/[0-9]\/[12][9012][0-9][0-9]%' AND /* dd/m/yyyy */
CAST(SUBSTRING(EndOfLifeDate, 1, 2) AS INT) BETWEEN 1 AND 31 AND
CAST(SUBSTRING(EndOfLifeDate, 4, 1) AS INT) BETWEEN 1 AND 12
THEN CONVERT(DATE, EndOfLifeDate, 101)
ELSE CONVERT(DATE, EndOfLifeDate, 103)
END AS EndOfLifeDate
, CASE
WHEN ReleaseDate LIKE '[1-9]\/[1-9]\/[12][9012][0-9][0-9]%' /* m/d/yyyy */
THEN CONVERT(DATE, ReleaseDate, 101)
WHEN ReleaseDate LIKE '[1-9]\/[0-3][0-9]\/[12][9012][0-9][0-9]%' AND /* m/dd/yyyy */
CAST(SUBSTRING(ReleaseDate, 3, 2) AS INT) BETWEEN 1 AND 31
THEN CONVERT(DATE, ReleaseDate, 101)
WHEN ReleaseDate LIKE '[01][0-9]\/[1-9]\/[12][9012][0-9][0-9]%' AND /* mm/d/yyyy */
CAST(SUBSTRING(ReleaseDate, 1, 2) AS INT) BETWEEN 1 AND 12
THEN CONVERT(DATE, ReleaseDate, 101)
WHEN ReleaseDate LIKE '[01][0-9]\/[0-3][0-9]\/[12][9012][0-9][0-9]%' AND /* mm/dd/yyyy */
CAST(SUBSTRING(ReleaseDate, 1, 2) AS INT) BETWEEN 1 AND 12 AND
CAST(SUBSTRING(ReleaseDate, 4, 2) AS INT) BETWEEN 1 AND 31
THEN CONVERT(DATE, ReleaseDate, 101)
WHEN ReleaseDate LIKE '[0-3][0-9]\/[01][0-9]\/[12][9012][0-9][0-9]%' AND /* dd/mm/yyyy */
CAST(SUBSTRING(ReleaseDate, 1, 2) AS INT) BETWEEN 1 AND 31 AND
CAST(SUBSTRING(ReleaseDate, 4, 2) AS INT) BETWEEN 1 AND 12
THEN CONVERT(DATE, ReleaseDate, 101)
WHEN ReleaseDate LIKE '[0-3][0-9]\/[0-9]\/[12][9012][0-9][0-9]%' AND /* dd/m/yyyy */
CAST(SUBSTRING(ReleaseDate, 1, 2) AS INT) BETWEEN 1 AND 31 AND
CAST(SUBSTRING(ReleaseDate, 4, 1) AS INT) BETWEEN 1 AND 12
THEN CONVERT(DATE, ReleaseDate, 101)
ELSE CONVERT(DATE, ReleaseDate, 103)
END AS ReleaseDate
,CONVERT(datetime,GETDATE(), 103) as "Today"
,"Catalog-Software"."Support"
,"Catalog-Software"."ExtendedSupportDate","Catalog-Software"."Language","Catalog-Software"."Version"
,count("Catalog-Computers"."import_id") as "Devices"

from "dbo"."Catalog-Computers" "Catalog-Computers"
inner join "dbo"."Catalog-ComputerSoftwareMatching" "Catalog-ComputerSoftwareMatching" on "Catalog-ComputerSoftwareMatching"."ComputerId" = "Catalog-Computers"."computer_guid"
inner join "dbo"."Catalog-Software" "Catalog-Software" on "Catalog-Software"."SoftwareId" = "Catalog-ComputerSoftwareMatching"."SoftwareId"
where Functionality='Operating System'
group by "Catalog-Software"."EndOfLifeDate","Catalog-Software"."ReleaseDate","Catalog-Software"."Support","Catalog-Software"."ExtendedSupportDate","Catalog-Software"."Language","Catalog-Software"."Version"
,"Catalog-Software"."Name", Vendor