LS Central - SQL View - Get Latest 12-13 Digit Barcode or other barcode if no 12-13 digit barcode can be found

 SELECT ItemNo, [1213BC], [1213BCDate], OtherBC, OtherBCDate,Case when ([1213BC] > '') then [1213BC] else OtherBC end as FinalBarcode,Case when ([1213BC] > '') then [1213BCDate] else OtherBCDate end as FinalBarcodeDate

FROM     (SELECT ItemNo, MAX([1213BC]) AS [1213BC], MAX([1213BCDate]) AS [1213BCDate], MAX(OtherBC) AS OtherBC, MAX(OtherBCDate) AS OtherBCDate

                  FROM      (SELECT CASE WHEN BCLen = '1213' THEN LastDateModified ELSE '' END AS [1213BCDate], CASE WHEN BCLen = 'Other' THEN LastDateModified ELSE '' END AS OtherBCDate, ItemNo, 

                                                       CASE WHEN BCLen = '1213' THEN MAX([Barcode No_]) ELSE '' END AS [1213BC], CASE WHEN BCLen = 'Other' THEN MAX([Barcode No_]) ELSE '' END AS OtherBC

                                     FROM      (SELECT LB.LastDateModified, LB.ItemNo, LB.BCLen, LB.BCCount, BC.[Barcode No_]

                                                        FROM      (SELECT MAX([Last Date Modified]) AS LastDateModified, [Item No_] AS ItemNo, CASE WHEN LEN([Barcode No_]) BETWEEN 12 AND 13 THEN '1213' ELSE 'Other' END AS BCLen, COUNT([Barcode No_]) 

                                                                                             AS BCCount

                                                                           FROM      [Master Config$Barcodes]

                                                                           GROUP BY LEN([Barcode No_]), [Item No_]) AS LB INNER JOIN

                                                                              (SELECT [Item No_], [Barcode No_], [Last Date Modified]

                                                                               FROM      [Master Config$Barcodes] AS [Master Config$Barcodes_1]) AS BC ON LB.LastDateModified = BC.[Last Date Modified] AND LB.ItemNo = BC.[Item No_]) AS LBC

                                     GROUP BY LastDateModified, ItemNo, BCLen, BCCount) AS IBC

                  GROUP BY ItemNo) AS FLBC