How to select 2 records in an SQL Table in a given sequence?

Question

I have a table with 100,000 records and the fields:

 id  |  dia  |  chuva_manha  |  chuva_tarde  |  chuva_noite

I would like to create an instruction that consults the table and counts how many times a given sequence of events happens. For example I want to take how many times it rained one day in the morning and the other day it rained in the afternoon.

But for that I have to create 2 instructions select in C# and create a loop for to validate the result (the.EXE_READER manager is a CRUD that I have implemented that makes all the connection and transaction with sql server, just sending the SQL statement)

int valor = 0;
for(int id = 0; id <= 100000; id+)
{
    string query1 = "SELECT * FROM dbchuva WHERE [id] = id AND chuva_manha != 0";
    string query2 = "SELECT * FROM dbchuva WHERE [id] = (id + 1)  AND chuva_tarde != 0";
    DataTable dados1 = gestor.EXE_READER(query1);
    int Qtde1 = dados1.Rows.Count;
    DataTable dados2 = gestor.EXE_READER(query2;
    int Qtde2 = dados2.Rows.Count;
    if(Qtde1 !=0 && Qtde2 !=0)
    {
        int valor = 1;
    }
}

Or

int valor = 0;
for(int id = 0; id <= 100000; id+)
{
    int id2 = id + 1;
    string query = "SELECT * FROM dbchuva";
    DataTable dados = gestor.EXE_READER(query)
    int val1= Convert.ToInt16(dados.Rows[id]["chuva_manha"]);
    int val2 = Convert.ToInt16(dados.Rows[id2]["chuva_tarde"]);

    if valor(val1 != 0 && val2 != 0)
    {
        valor = 1;
    }
}

Both forms require a lot of time to process due to the large amount of table values that ends up locking the whole system.

How could I implement in a simpler way?
How to verify that line X occurred event A and line X+1 event B occurred?
Or better how to count how many times when happening event A, in the subsequent record occurred event B?

Answer 1

From what I could understand, when it rains, the columns related to rain receive the value 1, when it doesn’t rain, they get the value 0. Depending on your version of SQL Server, I believe you can solve everything with a single query:

SELECT COUNT(1) 
FROM   (SELECT c.chuva_manha, 
               Lead(c.chuva_tarde, 1, 0) 
                 OVER (ORDER BY c.dia) chuva_tarde_proximo_dia 
        FROM   dbchuva c) resumo 
WHERE  resumo.chuva_manha <> 0 AND resumo.chuva_tarde_proximo_dia <> 0;

The analytical function LEAD returns a value from a column according to a offset (offset), if it does not exist, returns a default value (these are the function parameters respectively). In the above case, it will return the column value chuva_tarde of the following day (1 of offset), obeying the order of the days (OVER (ORDER BY c.dia)).

After having the function result for all days, it is checked whether the two conditions are met, returning the number of times the two events occurred. This check has to be done using sub query because it is not possible to use the function in the clause WHERE.

You can adjust the query for any other event combinations.

Answer 2

A solution ( sql only)

SELECT * 
FROM dbchuva c1
WHERE [id] = id 
AND chuva_manha != 0"
AND EXISTS (SELECT 1
            FROM dbchuva C2
            WHERE C2.id = (C1.id + 1)  
            AND C2.chuva_tarde != 0)

The EXISTS seeks the occurrence of a record in the passsda condition, in case rain the afternoon the next day.