En SQL Server, estoy tratando de calcular el recuento de días desde que se observó el mismo clima que hoy (supongamos que hoy es el 6 de agosto de 2018) primero en los últimos 5 días. Por pueblo.

Aquí están los datos:

+---------+---------+--------+--------+--------+
| Date    | Toronto | Cairo  | Zagreb | Ankara |
+---------+---------+--------+--------+--------+
| 1.08.18 | Rain    | Sun    | Clouds | Sun    |
| 2.08.18 | Sun     | Sun    | Clouds | Sun    |
| 3.08.18 | Rain    | Sun    | Clouds | Rain   |
| 4.08.18 | Clouds  | Sun    | Clouds | Clouds |
| 5.08.18 | Rain    | Clouds | Rain   | Rain   |
| 6.08.18 | Rain    | Sun    | Sun    | Sun    |
+---------+---------+--------+--------+--------+

Esto tiene que funcionar bien, pero todo lo que se me ocurrió hasta ahora es consultas individuales para cada ciudad (y habrá docenas de ciudades, no solo las cuatro). Esto funciona pero no va a escalar.

Aquí está el de Toronto ...

SELECT 
    DATEDIFF(DAY, MIN([Date]), GETDATE()) + 1 
FROM
    (SELECT TOP 5 * 
     FROM Weather 
     WHERE [Date] <= GETDATE()
     ORDER BY [Date] DESC) a
WHERE 
    Toronto = (SELECT TOP 1 Toronto 
               FROM Weather
               WHERE DataDate = GETDATE())

... que devuelve correctamente 4 ya que hoy llueve y la primera ocurrencia de lluvia en los últimos 5 días fue el 3 de agosto.

Pero lo que quiero devolver es una tabla como esta:

+---------+-------+--------+--------+
| Toronto | Cairo | Zagreb | Ankara |
+---------+-------+--------+--------+
| 4       | 5     | 1      | 5      |
+---------+-------+--------+--------+

¿Cómo es esto posible