Using a substring of a data value in my SQL query

I have data in my database that looks like this:

Date (DateTime)        Type (varchar)  Data (varchar)
2010/12/22 6:00        Geofence        Area 1,1
2010/12/22 7:00        Geofence        Area 1,0
2010/12/22 7:30        Geofence        Area 2,1
2010/12/22 7:45        Geofence        Area 2,0
2010/12/22 8:00        Geofence        Area 3,1

As you can see, for the Geofence data type, the Data is split using a comma, with the first half containing the name of the geofence and the second half indicating whether the point has entered or exited the geofence.

What I'm trying to do is get a list of all latest Geofence type entries for each area. My ideal output would look like this:

Date                   Type       Data
2010/12/22 7:00        Geofence   Area 1,0
2010/12/22 7:45        Geofence   Area 2,0
2010/12/22 8:00        Geofence   Area 3,1

My hypothetical SQL statement would look like this

SELECT MAX(Date) AS LatestDate, Data
FROM MyTable
WHERE Type = 'Geofence'
GROUP BY FirstHalfOf(Data)

Is it possible to do this keeping this same table structure?

I'm using MS SQL Server 2008

Thanks

P.S. As requested, here is some SQL code to create a table and fill it with the sample data:

CREATE TABLE TestData (
    id int PRIMARY KEY IDENTITY,
    Date datetime NOT NULL,
    Type varchar(50) NOT NULL,
    Data varchar(max) NULL)

INSERT INTO TestData (Date, Type, Data)
VALUES('2010/12/22 6:00', 'Geofence', 'Area 1,1')
INSERT INTO TestData (Date, Type, Data)
VALUES('2010/12/22 7:00', 'Geofence', 'Area 1,开发者_Python百科0')
INSERT INTO TestData (Date, Type, Data)
VALUES('2010/12/22 7:30', 'Geofence', 'Area 2,1')
INSERT INTO TestData (Date, Type, Data)
VALUES('2010/12/22 7:45', 'Geofence', 'Area 2,0')
INSERT INTO TestData (Date, Type, Data)
VALUES('2010/12/22 8:00', 'Geofence', 'Area 3,1')

---

Here is the solution for MS SQL Server

;WITH cte
          AS (
              SELECT    id,
                        Date,
                        Type,
                        Data,
                        SUBSTRING(Data, 1, CASE WHEN CHARINDEX(',', Data) = 0
                                           THEN LEN(Data)
                                           ELSE (CHARINDEX(',', Data) - 1)
                                           END) AS k
              FROM      TestData
             ),
        counts
          AS (
              SELECT    *,
                        duplicateCount = ROW_NUMBER() 
                        OVER (PARTITION BY k ORDER BY k, date desc)
              FROM      cte
             )
    SELECT  *
    FROM    counts
    WHERE   duplicateCount = 1

---

This is for MySQL, the substring function will vary by platform:

select m.Date, m.Type, m.Data
from MyTable m
inner join (
    SELECT SUBSTRING_INDEX(Data, ',', 1) as LeftData, 
        MAX(Date) AS LatestDate
    FROM MyTable 
    WHERE Type = 'Geofence' 
    GROUP BY SUBSTRING_INDEX(Data, ',', 1)
) mm on m.Date = mm.LatestDate
    and SUBSTRING_INDEX(m.Data, ',', 1) = mm.LeftData